Price Calculation with floor and ceiling

[jerrymoon1]

I have the following calculation that I use to determine if a price is outside of a floor or ceiling, if it is outside of the range it uses either the floor or ceiling price

=IF($G$76<F76,((F76-$G$76)*F13),IF($G$76>E76,-(($G$76-E76)*F13),0))

the formula is in cell G71
F13 is the total quantity
E76 is the ceiling price of $15.00
F76 is the floor price of $7.50
G76 is the calculated price of ($6.21)

In this case the floor of $7.50

I would like to modify the formula to where if you input N/A (or something else) that it will give a result of $0. I do not want to put a zero in the cells for the floor and ceiling price because it will give me a result of $0.

Thanks for your help.

 

[Andrew Fergus]

Hi Jerry

I don't think we are seeing the full formula. Can you please post the formula again? If it continues to display incorrectly either use code tags or insert some spaces in the formula before you press Submit.

In any case, there are a couple of functions that come to mind that might help you. The IsText(cellref) function will return "true" value if cellref contains text, or IsNumber(cellref) will return "true" if cellref contains a number. You could wrap these in another 'If' statement.

Also, rather than using an 'If' statement to work out the price you can use something like this instead:
MIN(MAX(floor,calculated),ceiling)

So I'm guessing something like this might work:
=If(IsNumber(cellref),MIN(MAX(floor,calculated),ceiling),0)

HTH, Andrew

 

[barry houdini]

Using the "Quote" button you can see the full formula which is:

=IF($G$76< F76,((F76-$G$76)*F13),IF($G$76 >E76,-(($G$76-E76)*F13),0))

I'm not sure that your description matches the formula, is it giving the results you want (when G76 is a number at least)?

Along the same lines as Andrew's suggestion, perhaps you can use

=If(ISNUMBER($G$76),MEDIAN(E76,F76,$G$76)*F13,0)

 

[jerrymoon1]

Sorry about that, not sure what happened. Here is the entire formula:

=IF($G$76<F76,((F76-$G$76)*F13),IF($G$76>E76,-(($G$76-E76)*F13),0))

 

[jerrymoon1]

Barry,

When I use your formula it seems to double the price. Any suggestions? Thanks.

 

[jerrymoon1]

bump - any help would be appreciated. Thanks.

 

[barry houdini]

Hello jerry,

I found your first post a little confusing but the formula I suggested

=If(ISNUMBER($G$76),MEDIAN(E76,F76,$G$76)*F13,0)

Assumes you want to find the middle value out of E76, F76 and G76, so if E76 is fixed at 15, F76 fixed at 7.5 then MEDIAN(E76,F76,G76) returns the middle one of these, i.e. if G76 > 15 you get 15, if G76 < 7.5 you get 7.5 otherwise you get G76. This price is then multiplied by the quantity in F13.

All the above only happens if G76 contains a number, otherwise you get zero.

Can you give an example where it returns the wrong amount, what's in G76 and F13 in that case?

 

[jerrymoon1]

Here is an example:

Ceiling price in cell E76 is $15.00, Floor price in cell F76 is $7.50 and the calculated price is $10.81. F13 is a quantity to change the per unit to total $. In this case F13 is 500.

With the formula I used it is returning $21.61 (which is $10.81 multiplied by 2). If the calculated price falls between the floor and ceiling, or if there is a non-number in the floor and ceiling, then I would like $0. Otherwise I need a formula to calculate an adjustment to get the price down to the ceiling or up to the floor. Here is my current formula which works, but I need the additional criteria if it is a non-number.

=IF($G$76<F76,((F76-$G$76)*F13),IF($G$76>E76,-(($G$76-E76)*F13),0))

 

[barry houdini]

OK, jerry, I think I understand now....just a small adjustment to my original suggestion...

=IF(ISNUMBER($G$76),(MEDIAN(E76,F76,$G$76)-$G$76)*F13,0)

 

[jerrymoon1]

Barry - the formula you gave me is not working quite right. The formula I currently have:

=IF($G$76<F76,((F76-$G$76)*F13),IF($G$76>E76,-(($G$76-E76)*F13),0))

This formula works as intended. If a price falls above the floor or below the ceiling it calculates the difference to get the calculated price to the floor or ceiling. If the price is in between, then it gives a 0. I would like to modify it to look at cells E76 and F76. If they have a non-number in them I would like the formula to return a 0. Not sure if need to use the current formula and add a isnumber using the and statement.