Multiple IF functions in one?

[Big Davie]

Hi

I hope someone can help with the following little problem of calculating
Column 3?

Column 1 Column 2 Column 3
B16 3900 x
B10 4250 x
B25 6700 x
B20 1200 x

If Column 1 = B10 then Column 3= Column 2 * 0.000616 if not then:
If Column 1 = B12 then Column 3= Column 2 * 0.000888 if not then:
If Column 1 = B16 then Column 3= Column 2 * 0.001579 if not then:
etc,etc.

 

[Mike H]

Hi,

Try this which assumes your data are in A2 - B2

=B2*LOOKUP(A2,{"B10","B12","B16"},{0.000616,0.000888,0.001579})

You can add more conditions to meet the requirements of your 'etc'

 

[Daryl S]

Big Davie -

If you don't have too many conditions to check, use this format:
=IF(A2="B10",B2*.000616,IF(A2="B12",B2*.000888,IF(A2="B16",B2*.001579,<Else
Value>)))
where <Else Value> is what you want if none of the prior conditions are met.


If there are a log of conditions, I would recommend using a separate
worksheet to house the the values to use, such as this:

B10 .000616
B12 .000888
B13 .001579
etc.

Then use a VLOOKUP to populate column 3 as follows:

=VLOOKUP(A2,<location and range of new lookup table>,2,FALSE)*B2

selecting the range of the table you just created, and make sure the $ are
there before dragging the forumula down (e.g. $A$2:$B$15 instead of A2:B15).