Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple IF functions in one?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple IF functions in one?
Hi,
Try this which assumes your data are in A2 - B2 =B2*LOOKUP(A2,{"B10","B12","B16"},{0.000616,0.0008 88,0.001579}) You can add more conditions to meet the requirements of your 'etc' "Big Davie" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple IF functions in one?
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). -- Daryl S "Big Davie" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple AND OR functions | Excel Worksheet Functions | |||
Using multiple NOW functions | Excel Worksheet Functions | |||
Multiple functions, conditional functions | Excel Worksheet Functions | |||
Index & Match functions - multiple criteria and multiple results | Excel Worksheet Functions | |||
How do I use multiple functions? | Excel Worksheet Functions |