# Price Calculation with floor and ceiling

#### jerrymoon1

##### Board Regular
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.

### 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
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
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
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
Barry,

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

#### jerrymoon1

##### Board Regular
bump - any help would be appreciated. Thanks.

#### barry houdini

##### MrExcel MVP
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
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
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
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.

Replies
12
Views
247
Replies
7
Views
424
Replies
1
Views
66
Replies
10
Views
407
Replies
3
Views
300

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.

### Which adblocker are you using?

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

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