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.

 

[barry houdini]

OK, I thought you were checking G76 to see if that was a number. Try

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

 

[jerrymoon1]

Sorry for the delay. Thanks Barry, this is exactly what I was looking for.