Trying to avoid long IF statement.

**Lukus** · 01-05-2010, 04:31 PM

I've attached an example of what I am trying to achieve and an explanation as well. I'm trying to avoid the use of a macro as I don't understand them and I will need to modify the sheet later on which could cause problems. However, if a macro is the only solution, then I will have to make due with one.

**teylyn** · 01-05-2010, 05:29 PM

Hi Lukus

see the attached.

I created five columns where all rating results are calculated for each row, and then an INDEX/MATCH function in column F to lookup the one that should be applied to the row. You can hide the columns I to M if they mess up your spreadsheet design.

hth

**daddylonglegs** · 01-05-2010, 05:30 PM

There are ways to use that approach but I wouldn't advise it. My preference would be for a formula like this in F6 copied down

=CHOOSE(MATCH(A6,A$23:A$27,0),1,2,3,4,5)

[Note: I had to remove the colons from the end of the ratings in A23:A27]

In that formula you need to replace each digit in 1,2,3,4,5 with the relevant formula, so 1 get's replaced with the formula for "Grease 2-hr" 2 gets replaced with the formula for "Vent 1-hr" etc.

I'm not sure I could decipher all the formulas but doing it that way you don't need ROW and INDIRECT and you can avoid some of the parentheses, e.g. in the following I added the 2 formula and the 4 formula ["Vent 1-hr" and "Round 1-hr"]

=CHOOSE(MATCH(A6,A$23:A$27,0),1,(B6*2+C6*2+2)*D6*14/144,3,(B6*3.1416+6)*D6*14/144,5)

you just need to add the versions for 1, 3 and 5.....

Edit: this is similar to Teylyn's suggested approach above - this method avoids the helper columns.......but you get quite a long, complex formula in return.....

**teylyn** · 01-05-2010, 05:36 PM

Daddylongleg's suggestion is putting it all in one formula, but the principle is basically the same. Each version has its benefits, i.e.

- DLL's solution is more elegant and does not require helper columns,
- my solution shows you the results of the individual calculations and it may be easier to maintain the formulae.

you can use the formulae in my spreadsheet to copy and paste into DLL's formula....

hth

**contaminated** · 01-05-2010, 05:38 PM

There are two options in workbook

EDIT:

Oops
seems I late

**Lukus** · 01-06-2010, 11:18 AM

Thank you guys... both solutions achieve what I wanted.

I think I prefer Teylyn's approach as it keeps all formulas to a minimal size and therefore, easier to maintain as he mentioned.