Another solution for multiple embeeded IF

[Stefan Robert]

Hello,

I need to create an Excel sheet to calculate price regarding relative
to the cost of some items. The cost vs price is a table that gives the
following information:

If carrots cost 4$ (A1=4) or less, the price should be 4.25$ (A2 = 4.25)
if carrots costs between 4 and 5$, (A3 = 5) the price should be 5.35$
(A4 = 5.35)
if carrots costs between 5 and 6$, (A5 = 6) the price should be 6.15$
(A6 = 6.15)
and this goes on for at least 20 other conditions.

On another page I have a cell were I enter the cost (B1) and the cell
right next to it (B2) will contain the formula that gives me the answer
according the ranges.

I used embedded IF statements for this and it looks like this:

IF(B1<4; 4.25; IF(B1<5;5.35;IF(B1<6;6.10; IF(...))))

I have written the value of cells A1 to A6 just to make the example
simpler to understand.

Now, the number of conditions might change from week to week and
because of this, I need to edit my IF() statement to satisfy this
everytime, which is very time consuming. Also, this is only for the
carrots, I have about 40 other items that have to be calculated that
way using different range and a different number of conditions for each
item.

Is there a better way to do this than using embedded IF statements? Or
is there a way to dynamically change the number of IF?

Thanks for the help

Stefan

 

[Max]

One better way is to use VLOOKUP ..

In Sheet1
-------------
Create a vlookup reference table in A1:B3
(with values in col A in ascending order)

0 4.25
4 5.35
5 6.15

Note: The table above implies a revised "tighter" criteria,
i.e. one w/o overlapping values

=0, <4, ... price is 4.25
=4, <5 .... price is 5.35
=5 .......... price is 6.15

In Sheet2
-------------
Assume the lookup values (Cost)
are in col A, A1 down

Put in B1:

=IF(A1=0,"",IF(ISNA(VLOOKUP(A1,Sheet1!A:B,2,TRUE)),"",VLOOKUP(A1,Sheet1!A:B,
2,TRUE)))

Copy B1 down

Format col B as currency

Col B will return the corresponding prices
for the cost lookup values in col A

 

[Frank Kabel]

Hi
1. Create a lookup table (e.g. called 'lookup') with the following
layout:
A B
1 0 4.25
2 4.01 5.35
3 5.01 6.15
....

Now on your entry sheet use
=VLOOKUP(B1,'lookup'!$A$1:$B$100,2,TRUE)

 

[Arvi Laanemets]

Hi

When there are 24 or less conditions, then
=CHOOSE(MAX(CEILING(A1,1),4)-3,4.25,5.35,6.15,...,##.##)

When you prefer an editable price table, or when there number of prices is

24, then enter those prices into some range in ascending order, like this:

4.25
5.35
6.15
....
##.##

Further you can refer to this price table directly, or you can define a
named range p.e. PriceList

The formula will be
=INDEX(PriceList;MAX(CEILING(A1,1),4)-3,0)

 

[Stefan Robert]

Hi Arvi,

what happens if they are more than 24 conditions?

 

[Arvi Laanemets]

Hi

CHOOSE() function allows 24 different values to choose between. When there
is more of them, Excel probably returns some error message. So when you have
mor than 24 options, you must have some price table in workbook, and then
you can use VLOOKUP or INDEX to locate the right price in the table.

I wasn't able to detect any pattern in your example prices, but often it's
possible to use (relatively) simple arithmethic in such situations. In your
case the prices are
4+0.25
5+0.35
6+0.15
Its esay with integer part of sum, you can use the formula
=MAX(CEILING(A1,1),4)
to calculate it. But when your figures were right, then there is no
regularity for fractional part of price, I'd be able to detect.

 

[Stefan Robert]

Hi,

yes, thanks Arvi for your answer, and the VLOOKUP() seems to be the way
to go for me.

I was able to make this work with a combination of VLOOKUP() and
COUNT() to have a dynamic range in it.

Stefan

 

[Stefan Robert]

Thank Frank!

Thant's was the answer I was looking for!

The only other thing was to have a dynamic range for my table and I did
this with a combination of OFFSET() and COUNT() function.

Stefan