Another solution for multiple embeeded IF
 

Hi Arvi,

what happens if they are more than 24 conditions?


On 2004-12-10 01:19:00 -0500, "Arvi Laanemets" said:

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)

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.


--
Arvi Laanemets
(When sending e-mail, use address arvil<Attarkon.ee)


"Stefan Robert" wrote in message
news:2004121008365716807%srobertDELETEME@b2b2cca.. .
Hi Arvi,

what happens if they are more than 24 conditions?


On 2004-12-10 01:19:00 -0500, "Arvi Laanemets" said:

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)

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


On 2004-12-10 09:15:39 -0500, "Arvi Laanemets" said:

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.

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


On 2004-12-10 01:02:14 -0500, "Frank Kabel" said:

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)