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

How concerned are you about the OpenSSL "Heartbleed" bug?

Discuss in The Lounge

Poll History