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

Find more posts tagged with

Formulas

Services

Project Management

Comments

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

Sign in to join the conversation!

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…