Formula Help Needed
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
Comments

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 semicolon to a comma.
Did it work?
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

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, (800600) 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 abovestated 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/2476491createefficientformulaswithatcell
Cheers,
Eric
Smartsheet Support
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!