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

Jeana
Jeana ✭✭✭✭✭✭
edited 02/03/22 in Formulas and Functions

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.


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

Thanks,

Jeana

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 02/03/22 Answer ✓

    @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)


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 02/03/22 Answer ✓

    @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)


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Jeana
    Jeana ✭✭✭✭✭✭

    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
    Jeff Reisman ✭✭✭✭✭✭

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

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Jeana
    Jeana ✭✭✭✭✭✭

    @Jeff Reisman I 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
    Jeff Reisman ✭✭✭✭✭✭
    edited 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)

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Jeana
    Jeana ✭✭✭✭✭✭

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

    Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!