Hope somebody can help. This relates to a pricing spreadsheet I have a price list for a software license that varies depending on the qty of licenses. 1-4 cost 1000; 5-9 cost 800 and so on.

I have a cell with a number of licenses that a client requires. This cell can vary.

I have another cell which will show the cost of the licenses.

I would like to put a formula in this cell that looks ate the number of licenses required and applies the corrct license rate.

What is the best method for doing this? An IF statement or a Vlookup table?

I am sort of familiar with easy IF statements but I can't get this one to work. I can get answers to the TRUE FALSE statements but no formula result in the cell. I'm obviously doing something stupid. Or maybe a Vlookup is the best solution.

Any help most welcome.

John

=IF(AND((B1>=VALUE(LEFT(F1,1))),(B1<=VALUE(RIGHT(F1,1)))),G1,IF(AND((B1>=VALUE(LEFT(F2,1))),(B1<=VALUE(RIGHT(F2,1)))),G2,IF(AND((B1>=VALUE(LEFT(F3,2))),(B1<=VALUE(RIGHT(F3,2)))),G3,G4))) This is for a list of quantities in column F, as 1-4, 5-9, 10-15, 16-20

and price in column G. Lookup value in B1.

Many thanks!! If I wanted to add additional quantity in column F and corresponding price how would this impact on the statement?? I just need a 50+ qty. Would it look like this =IF(AND((B1>=VALUE(LEFT(F1,1))),(B1<=VALUE(RIGHT(F1,1)))),G1,IF(AND((B1>=VALUE(LEFT(F2,1))),(B1<=VALUE(RIGHT(F2,1)))),G2,IF(AND((B1>=VALUE(LEFT(F3,2))),(B1<=VALUE(RIGHT(F3,2)))),G3,IF(AND((B1>=VALUE(LEFT(F4,3)),(B1<=VALUE(left(F4,3))))),G4,54)))

You have saved my sanity:)

John

This works if you have lest than 7 nested Ifs. That is the limit on Excel.

Ask Your Question

Weekly Poll

Do you believe Comcast and Time Warner should be allowed to merge?

Discuss in The Lounge

Poll History