Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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) |
#2
|
|||
|
|||
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) |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What is Multiple R, it is the first solution obtained in Regressi. | Excel Worksheet Functions | |||
What is Multiple R, it is the first solution obtained in Regressi. | Excel Worksheet Functions | |||
"Is Between" Function Solution --- Many Thanks | Excel Worksheet Functions |