Price Calculation with floor and ceiling

jerrymoon1

Board Regular
Joined
Nov 15, 2006
Messages
139
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.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,452
Office Version
  1. 365
  2. 2021
  3. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Board Regular
Joined
Nov 15, 2006
Messages
139
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

Board Regular
Joined
Nov 15, 2006
Messages
139
Barry,

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

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Board Regular
Joined
Nov 15, 2006
Messages
139
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Board Regular
Joined
Nov 15, 2006
Messages
139
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.
 

Forum statistics

Threads
1,181,103
Messages
5,928,077
Members
436,587
Latest member
Slicesofquince

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top