Formula Help Needed

jbboutilier

I currently have 3 Columns: Column 1 "NUMBER OF UNITS", Column 2 "UNIT PRICE" & Column 3 "TOTAL PRICE".

What I am trying to do is if in Column 1, the "NUMBER OF UNITS" is 600 or less, I need Column 3 "TOTAL PRICE" to default 515. However, if Column 1 "NUMBER OF UNITS" is more than 600, I need the Column 3 "TOTAL PRICE" to increase by 0.85 for each unit over. IF Column 1 "Number of Units is Blank, I need Column 3 "TOTAL PRICE" to remain blank.

New to this an I have spent hours trying to figure this out. Any help would be appreciated.

 

Below is where I am at currently but again it is not what I need.

 

=IF(ISBLANK([Number of Units]1), "$0.00", 515 + (MAX(0, ([Number of Units]1 - 600) * [Unit Price]1)))

 

~Jon

 

Andrée Starå

Hi,

Try this.

=IF(ISBLANK([NUMBER OF UNITS]@row); ""; IF([NUMBER OF UNITS]@row <= 600; 515; IF([NUMBER OF UNITS]@row >= 601; ([NUMBER OF UNITS]@row - 601) * ([UNIT PRICE]@row) + 515; "")))    

The same version but with the below changes for your and others convenience.    

=IF(ISBLANK([NUMBER OF UNITS]@row), "", IF([NUMBER OF UNITS]@row <= 600, 515, IF([NUMBER OF UNITS]@row >= 601, ([NUMBER OF UNITS]@row - 601) * ([UNIT PRICE]@row) + 515, "")))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

eric.o

Hi Jon,

Happy to help! When constructing formulas that require multiple calculations in Smartsheet, it's often best to break them out into there individual calculations then string them together in a nested statement. This ensures the desired results are met and limits the accidental errors. 

 

From the description given it sounds like there are three things that need to be achieved. 

1. IF Column 1 is blank, Column 3 is blank. 

This could look like:

=IF(ISBLANK([NUMBER OF UNITS]@row), " ")

 

 

2. IF Column 1 is equal to 600 or less, Colum 3 is to default to 515.

This could look like this:

=IF([NUMBER OF UNITS]@row <= 600, 515)

 

 

3. IF Column 1 is more than 600, Column 3 is to increase by 0.85 for each unit over. 

This could look like this:

=IF([NUMBER OF UNITS]@row > 600, ([NUMBER OF UNITS]@row - 600) * .85

 

Note: I believe the above math is correct, you may want to confirm. From my understanding, to increase each unit over by .85 you have to subtract 600 from the total in NUMBER OF UNITS, which would give you the unit difference, then multiply by 0.85. For example, (800-600) would produce 200 x .85. If I'm incorrect in how you'd like to perform this calculation you can adjust the *.85 part of the above-stated formula to calculate the desired figure. 

Once the 3 formulas are broken out, as above, to string them together you could create a Nested IF Function by placing the next proceding formula in the false section of the IF Function. As outlined by this Help Center article. https://help.smartsheet.com/function/if

 

▸ For the above stated formulas, it could appear nested like this:

=IF(ISBLANK([NUMBER OF UNITS]@row), " ", IF([NUMBER OF UNITS]@row <= 600, 515, IF([NUMBER OF UNITS]@row > 600, ([NUMBER OF UNITS]@row - 600) * .85)))

 

Note: The value @row allows you to reference the cells of the stated columns at the row level of the formula. This is further outlined here: https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

 

Cheers, 

Eric  

Smartsheet Support