CODEWITHSUNDEEP
excelif-statementexcel-formulalookupworksheet-function
PKGrem
PKGrem

Reputation: 147

Repetition of nested IF statements

I'm struggling to get an Excel query to work. I'm trying to have multiple queries like this within a query and am not sure how to work it. The query so far is this:

     =IF(B6=$R$2,B2*$S$2,IF($B$6=$R$3,B2*$S$3,IF(B6=$R$4,B2*$S$4,B2)))

Here is what I'm trying to do, there are the 7 multipliers below, the above statement is for only one of them.

enter image description here

The logic is, that if the cell says Good it is multiplied by 97.5%, if Bad by 102.5%, if OK it's multiplied by 100%, but I need the odds to be multiplied by all 7 areas beneath. So its essentially an if/else statement 7 times within itself:

If B6 = Good * S2, else * S4, then if B7 = Good * S2, else S4, essentially that repeated 7 times down.

How can I do this in Excel?

Upvotes: 0

Views: 169

Answers (2)

pnuts
pnuts

Reputation: 59495

In B2 and copied down: 

=B2*LOOKUP(B6,{"Bad","Good","OK"},{1.025,0.975,1})

might suit but would still require entries into E2 etc.

Might not be as easy to understand but should give the same results (for the three conditions) and is a little shorter: 

=B2*(1+.025*((B6="Bad")-(B6="Good")))

Upvotes: 2

Fnaxiom
Fnaxiom

Reputation: 396

It would be better to use VLOOKUP as follows : 

   =B2 * VLOOKUP(B6,$R$2:$S$4,2) * VLOOKUP(B7,$R$2:$S$4,2) * 
         VLOOKUP(B8,$R$2:$S$4,2) * VLOOKUP(B9,$R$2:$S$4,2) * 
         VLOOKUP(B10,$R$2:$S$4,2) * VLOOKUP(B11,$R$2:$S$4,2) * 
         VLOOKUP(B12,$R$2:$S$4,2)

You could develop a simple User Defined function through VBA though. It would be better and more efficient.

Upvotes: 0

Related Questions