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.
