Formula Question IF --- CHOOSE

  • Thread starter Thread starter kmyers
  • Start date Start date
K

kmyers

I need to multiply

B8

by D8 if B4 is 1
by G8 if B4 is 12
by J8 if B4 is 36
by M8 if B4 is 60

These are the actual cells and numbers I will be using.
I need the formula for the sum cell AA8
Thank you,
Tina
 
One way:

=B8*INDIRECT(VLOOKUP(B4,{1,"D8";12,"G8";36,"J8";60,"M8"},2,
0))

HTH
Jason
Atlanta, GA
 
I believe I am understanding you correctly, so here goes...
These are the conditions you specified (1st argument) and an IF statement
encasing each one:
IF(B4=01, B8*D8, 0)
IF(B4=12, B8*G8, 0)
IF(B4=36, B8*J8, 0)
IF(B4=60, B8*M8, 0)
If the condition is met, then the cell will be assigned a value of B8*D8 or
whatever is in the 2nd argument.
0, the 3rd argument is a placeholder, tellling Excel what to put in as the
value of the cell if the condition is NOT met.
How do you connect these conditions and, hence, these IF statements??
One way to do this is by nesting them as follows, substituting the 0 with
another IF statement.
You can put the following formula in the cell you would like your value to
be (don't put it in any of the referenced cells, or this would be a circular
reference). Keep in mind that there is still one 0 left in the formula and
this will be the result if none of the conditions are met (ie. if B4 is not
equal to 1, 12, 36, or 60). If you would like something other than 0 to
signify this, then just put that in the formula instead.

=IF(B4=01, B8*D8, IF(B4=12, B8*G8, IF(B4=36, B8*J8, IF(B4=60, B8*M8, 0))))

There are probably more elegant ways to do this, but I believe this gets the
job done.
good luck, hj.

______________________
"peace - it's worth killing for"
- Bush
: I need to multiply
:
: B8
:
: by D8 if B4 is 1
: by G8 if B4 is 12
: by J8 if B4 is 36
: by M8 if B4 is 60
:
: These are the actual cells and numbers I will be using.
: I need the formula for the sum cell AA8
: Thank you,
: Tina
:
:
:
: ------------------------------------------------
:
:
:
 
I'm curious... How does this work? I tried pasting this into a cell and it
gives me an error (#VALUE!)
hj
--
______________________
"peace - it's worth killing for"
- Bush
: Tina,
:
: Ues
: =B8*CHOOSE(B4,D8,G8,J8,M8)
:
: --
:
: HTH
:
: Bob Phillips
:
: : > I need to multiply
: >
: > B8
: >
: > by D8 if B4 is 1
: > by G8 if B4 is 12
: > by J8 if B4 is 36
: > by M8 if B4 is 60
: >
: > These are the actual cells and numbers I will be using.
: > I need the formula for the sum cell AA8
: > Thank you,
: > Tina
: >
: >
: >
: > ------------------------------------------------
: >
: > ~~ View and post usenet messages directly from
http://www.ExcelForum.com/
: >
:
:
 
The CHOOSE function takes the first value, the value in B4, and selects from
the remaining items based upon that value. So if B4 = 1, it returns D8, B4
=2 returns G8, etc.

You will get #VALUE if B4 has any value other than 1 - 4.
 
I can't believe it worked!!

Thank you so much!!

=IF(B4=1, B8*D8, IF(B4=12, B8*G8, IF(B4=36, B8*J8, IF(B4=60, B8*M8,
0)))):D :D :D
 
Back
Top