Formula help - using MIN, multiply and divide but keep getting Invalid operation

Jeana · 02/03/22

I've tried applying parenthesis to force certain calculations before others and I've tried it with out any.

The Production-Scope, Pre-Production Scope and Post-Production scope are formula results themselves. All columns are set up as Text/Number formats.

Screen Shot 2022-02-03 at 10.15.55 AM.png

Screen Shot 2022-02-03 at 10.23.44 AM.png

Gotta be something simple I'm missing or the format of the MIN function but I don't see it.

Thanks,

Jeana

Jeff Reisman · 02/03/22

@Jeana

What exactly are you trying to do with the MIN function? You have to give the function either a range or a list of values to evaluate. So what are you trying to compare to find the lowest value?

Your first formula essentially says =MIN(1.9) which only gives it one value to evaluate. If you're trying to compare which is lower between the calculated values of:

[Pre-Production Scope]@row + [Production Scope]@row + 1.7

and

[Post-Production Scope]@row / 3

you would need to separate these out by commas to use the correct syntax:

=MIN(([Pre-Production Scope]@row + [Production Scope]@row + 1.7), ([Post-Production Scope]@row / 3))

In my little test here, you see it returned 1.66667 (aka 5 divided by 3) because it's lower than 7.5 (2 + 2 + 3.5)

Jeff Reisman · 02/03/22

@Jeana

What exactly are you trying to do with the MIN function? You have to give the function either a range or a list of values to evaluate. So what are you trying to compare to find the lowest value?

Your first formula essentially says =MIN(1.9) which only gives it one value to evaluate. If you're trying to compare which is lower between the calculated values of:

[Pre-Production Scope]@row + [Production Scope]@row + 1.7

and

[Post-Production Scope]@row / 3

you would need to separate these out by commas to use the correct syntax:

=MIN(([Pre-Production Scope]@row + [Production Scope]@row + 1.7), ([Post-Production Scope]@row / 3))

In my little test here, you see it returned 1.66667 (aka 5 divided by 3) because it's lower than 7.5 (2 + 2 + 3.5)

Jeana · 02/03/22

Jeff, I understand what you did and it's definitely helped me evaluate the process I'm trying to calculate. I was using the MIN wrong (without a range) so I'm going to rethink this a bit.

Thanks so much!!

Jeana

Jeff Reisman · 02/03/22

Jeff, I understand what you did and it's definitely helped me evaluate the process I'm trying to calculate. I was using the MIN wrong (without a range) so I'm going to rethink this a bit.<\/p>

Thanks so much!!<\/p>

Jeana<\/p>","bodyRaw":"[{\"insert\":\"Jeff, I understand what you did and it's definitely helped me evaluate the process I'm trying to calculate. I was using the MIN wrong (without a range) so I'm going to rethink this a bit.\\nThanks so much!!\\nJeana\\n\"}]","format":"rich","dateInserted":"2022-02-03T18:02:21+00:00","insertUser":{"userID":118808,"name":"Jeana","url":"https:\/\/community.smartsheet.com\/profile\/Jeana","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3QlV_6dMjzo!xBjVsS93JyY!3ztBNjGJ7bu","dateLastActive":"2023-03-28T01:37:55+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/318581#Comment_318581","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/318581#Comment_318581

Glad I could help. I'd be happy to offer any more insight you need. Data Analysis and Business processes are my specialties.

Jeana · 02/08/22

@Jeff ReismanI Jeff, I'm back. I thought I had this figured out but I'm still struggling after a few adjustments. I separated out the formulas as you suggested and for the first one I am getting a 0.0 result using this:

I should be getting 7.7 as a result. I thought it might need the VALUE option but that didn't work either. I get 331.7 and without the SUM options I get 331.7.

What am I missing?!!

Jeff Reisman · 02/08/22

@Jeana

You are on the right track regarding using the VALUE function.

You see how the numbers you're trying to add are on the left side of the column? That's because they're most likely text values. So you'll want to change them over to numeric values using the VALUE function, but you have to do it for each cell reference in your formula if you want the SUM formula to evaluate them correctly.

The other thing is that the SUM function just wants a list of values it is supposed to add together. It knows you want to add them, so you don't need to give it a bunch of + signs. So try this:

=SUM(VALUE([Pre-Production Scope]@row), VALUE([Production Scope]@row), 1.7)

Jeana · 02/08/22

@Jeff ReismanPerfect!!! I didn't think that I should have been using VALUE and SUM together.

Thank you!

Formula help - using MIN, multiply and divide but keep getting Invalid operation

Best Answer

Answers

Help Article Resources

Categories