Excel (value) error question.

[GILLAN]

I am trying to figure pricing based on qty and height x length, but also have the value subject to a minimum. So in this example I have a quantity 4 with the length (3) x height (2). The price is based on another formulas that takes the 6 x 1.20 to give you the 7.2 but the minimum charge is 10.00.
I created the formula below, but it gives me an value error. If anyone could help point me in the right direction, it would be great.



QTY LENGTH HEIGHT TOTAL SIZE
4 3 2 6


CHARGE PER FORMULA CHARGE SUBJECT TO MINIMUM
7.2 #VALUE!


=IF(A4<3,E4<10,10),IF(A4<11,E4<8,8),IF(A4<35,E4<7.5,7.5),IF(A4<71,E4<7.25,7.25),IF(A4<143<6.5,6.5)

[martindwilson]

to do that it would have to look like this

Please Login or Register  to view this content.

but this still wont work as
A4<11,E4<8
and
A4<71,E4<7.25
would still give same result for a4=6 e4=7 say

[GILLAN]

I still cannot get it to work. It says "False" in the cell.

Thanks for your trouble.

Eric

[daddylonglegs]

I am trying to figure pricing based on qty and height x length, but also have the value subject to a minimum. So in this example I have a quantity 4 with the length (3) x height (2). The price is based on another formulas that takes the 6 x 1.20 to give you the 7.2 but the minimum charge is 10.00.

I don't see where the other values in your formula come from.

Given the description above it looks like you want to multiply length by height, subject to a minimum, and then multiply that result by the quantity, so for your example above, if quantity is in A4, length in B4 and height in C4 the following formula will give the result of 40

=A4*MIN(10,B4*C4)

Does that help at all?

[GILLAN]

Attached is the layout and info I was trying to formulate.

I would appreciate any help.

Thanks,

Eric

[daddylonglegs]

Hello Eric

I amended your sheet, attached. Does that work for you?

I changed B2:I2 to show the lower bound for each range [the formulas don't work otherwise] then formula in E12 would be

=LOOKUP(A12,$B$2:$I$3)*D12

and F12

=LOOKUP(A12,$B$2:$I$4)

I assume for PRICE you want the maximum of those two, multiplied by the quantity so for PRICE in G12 I have

=MAX(E12:F12)*A12

[GILLAN]

That works!

I appreciate your help. You are an asset to this forum.

Thanks,

Eric