Numbers Formula, "if this", "then this"

[xplizt]

I have a basic knowledge of numbers formulas and have been able to get along just fine doing some research but I am stuck with this complex/unique formula.

Looking for a way to create a formula that takes the quantity into account for different rules.

Basically I have to create price breaks at different quantities, so if cell 1B is 0-5 qt. there is no discount, if it is 6-20 it carries out a 2% discount, 21-40 4% discount and so on.

Thank you to everyone in advance for their contributions.

 

[ftaok]

I'm not sure how the syntax is for Numbers, but in Excel, you could use nested if/then statements.

It would look something like this. In excel, the if function is IF(cell=X , then, else)


=if(B1<0,"error",if(B1<6,0,if(B1<20,2%,if(B1<40,4%,if(B1>41,6%,"error"))))

In the scene above, any quantity less than 0 returns an error. Also, any number between 40 and 41 returns an error. I'm assuming you can't have negative quantities, nor decimal quantities. Decimals between 0 and 40 are accepted in the statement above.

0 - 5 0%
6 - 20 2%
21 - 40 4%
41 and more 6%


Hope this helps.

 

[jamietshaw]

Yes, ftaok’s formula works in Numbers. These formulas can get pretty incomprehensible but you can make it a little more readable by adding line breaks (option-return). Sadly there aren’t more readable ways of doing this that you might get in a programming language.

 

[xplizt]

I'm not sure how the syntax is for Numbers, but in Excel, you could use nested if/then statements.

It would look something like this. In excel, the if function is IF(cell=X , then, else)


=if(B1<0,"error",if(B1<6,0,if(B1<20,2%,if(B1<40,4%,if(B1>41,6%,"error"))))

In the scene above, any quantity less than 0 returns an error. Also, any number between 40 and 41 returns an error. I'm assuming you can't have negative quantities, nor decimal quantities. Decimals between 0 and 40 are accepted in the statement above.

0 - 5 0%
6 - 20 2%
21 - 40 4%
41 and more 6%


Hope this helps.

Thank you for the reply... this seems like it would almost work.. but.. this currently looks like the formula may just return a percentage number vs. actually carrying out a calculation. I'm missing the part on where to input the formula to solve the equation. I think I could probably take your formula and combine it with a separate cell formula to achieve my goal, but I would think there is a better way to do this.

This is probably my fault for not clarifying in better detail.
• B1 is the quantity of the item which is input by the user. Currently I am using a "slider" format on the cell so it is limited to quantities of 1-100 depending on where the slider is positioned.

• D7 is the resulting price. If I change the quantity, I have the price in D7 currently changing as well.

I would like the formula to calculate the % discount for D7 automatically, based on what number is in the quantity cell of B1.

B1 = 6 qt.
D7 = $36
Assuming the price for 1 item is $6, The formula should solve 2% discount on $36 for 6 items = $35.28, if the quantity is changed to 21 qt., the formula will perform the following, $6x21 = $126 - 4% discount = $120.96

Yes, ftaok’s formula works in Numbers. These formulas can get pretty incomprehensible but you can make it a little more readable by adding line breaks (option-return). Sadly there aren’t more readable ways of doing this that you might get in a programming language.

Nice tip... I think I'm going to play around with line breaks which would help in the long term

 

[ftaok]

Thank you for the reply... this seems like it would almost work.. but.. this currently looks like the formula may just return a percentage number vs. actually carrying out a calculation. I'm missing the part on where to input the formula to solve the equation. I think I could probably take your formula and combine it with a separate cell formula to achieve my goal, but I would think there is a better way to do this.

This is probably my fault for not clarifying in better detail.
• B1 is the quantity of the item which is input by the user. Currently I am using a "slider" format on the cell so it is limited to quantities of 1-100 depending on where the slider is positioned.

• D7 is the resulting price. If I change the quantity, I have the price in D7 currently changing as well.

I would like the formula to calculate the % discount for D7 automatically, based on what number is in the quantity cell of B1.

B1 = 6 qt.
D7 = $36
Assuming the price for 1 item is $6, The formula should solve 2% discount on $36 for 6 items = $35.28, if the quantity is changed to 21 qt., the formula will perform the following, $6x21 = $126 - 4% discount = $120.96



Nice tip... I think I'm going to play around with line breaks which would help in the long term

you can certainly combine the entire formula into the IF function, however, I would advise against doing so. Just put everything into its own cell, that way it makes it easier to adjust later. For instance, you change the unit price. Your way has you changing the price in the formula several times. My way, just once.

For instance.

A1 qty
B1 unit price
C1 if function return the discount based on A1
D1 total before discount A1*B1
E1 discount C1*D1
F1 total D1+E1

 

[xplizt]

you can certainly combine the entire formula into the IF function, however, I would advise against doing so. Just put everything into its own cell, that way it makes it easier to adjust later. For instance, you change the unit price. Your way has you changing the price in the formula several times. My way, just once.

For instance.

A1 qty
B1 unit price
C1 if function return the discount based on A1
D1 total before discount A1*B1
E1 discount C1*D1
F1 total D1+E1

I think you're right about putting everything in it's own cell.. initially viewing everything would look cleaner but my formulas could get convoluted for future reference. Thank you for your help, I should have this figured out now.

 

[xplizt]

Without opening a new thread.... hopefully...

Can anyone tell me if they know how to code this formula.

I am calculating sheet sizes. For example, a standard sheet is 4x8 feet. We use inches currently to calculate, so 48 wide 96 tall. In my spreadsheet, I have a width and height calculator, currently if someone has a 48x60" sheet, but inputs 60" in the WIDTH section and 48" in the HEIGHT section... It still calculates fine initially.... BUT, I need it to properly "flip" the positions, otherwise the math down the line can mis-calculate.

Additionally, I need some sort of rule to be carried out (then do this), if the width is larger than the "maximum width", as well as the height.

Basically the problem it should solve is to "slice" the extra off the width and add it to another sheet.... if that even makes any sense.

 

[ftaok]

The quick and dirty way to do it would be to use a few cells and a couple of IF functions.

Let's say that you want to input two dimensions, length and width. Let's then say that you would prefer that Length was put into A1 and Width is put into A2.

I would put the IF function Column B.

B1 = IF (A1<A2, A2, A1)
B2 = IF (A2>A1, A1, A2)

That way, B1 will always be longer than B2 and you would use those numbers as the basis for your subsequent calculations.

For the maximum width issue, you could carry out another IF function in Column C.

C1 = B1
C2 = IF (B2>48, "ERROR", B2)

Obviously, if you have a max length, you could put in an IF function in C1. Then you would carry out the rest of your calcs using C1 and C2.

 

[xplizt]

You are awesome! It seems to obvious once you laid it out... my constant "brain fog" doesn't help me much in this process, but I'm glad to have help from people like you guys on the net. Thank you so much!

 

[ftaok]

You are awesome! It seems to obvious once you laid it out... my constant "brain fog" doesn't help me much in this process, but I'm glad to have help from people like you guys on the net. Thank you so much!

No problem. I'm an engineer and Excel is like a second language. It's fun to assist on problems like this. It breaks up the monotony of my regular day ... kinda like a Soduku puzzle.