Excel Nested IF/Vlookup

March 10, 2005 at 12:19:17
Specs: Win 2003, P3 and 256K

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


See More: Excel Nested IF/Vlookup

Report •


#1
March 10, 2005 at 16:27:44

=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.


Report •

#2
March 11, 2005 at 08:35:32

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



Report •

#3
April 8, 2005 at 13:33:45

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

Report •

Related Solutions


Ask Question