Make Excel Make Coleslaw!

June 21, 2007 8:13 PM

Excelfilter!! Okay, how would I do this in MS Excel???

If the value of cell B is "F", then display 75% of the value of cell H in cell I.

But if the value of cell B is "M", then display 100% of the value of cell H in cell I.

posted by keep it tight to technology (6 comments total)

If those are the only two possible values for B, you could enter this in cell I:
=IF(B="F",H*0.75,H)

If not, then you could do this:

=IF(B="F",H*0.75,IF(B="M",H))
posted by cerebus19 at 8:22 PM on June 21, 2007

=IF(B1=F,PRODUCT(H1*0.75),(IF(B1=M,PRODUCT(H1*1))))
posted by brain cloud at 8:22 PM on June 21, 2007

(I meant to add, you would put that formula in cell I).
posted by brain cloud at 8:23 PM on June 21, 2007

If you have more than two key/value pairs, then you should use a LOOKUP table. E.g. if you have the following data in columns A and B:

A 0.25
B 0.50
C 0.75
D 1.00

And the value "C" in cell D1, then

=LOOKUP(D1,A1:D2)

will return 0.75
posted by randomstriker at 12:16 AM on June 22, 2007

oops, that should be =LOOKUP(D1,A1:D4)
posted by randomstriker at 12:17 AM on June 22, 2007

To clarify what the "IF" statements are doing, it's just saying "If this is true, then do the first thing, if not, do the second." In this case, the second thing is just another "IF" statement. (Hopefully the following makes sense.)

If B=F
  Then I = 75% * H
If not,
  Then check to see if B=M.
    If yes, B=M
       Then I = M
    If no, B!=M
      Since there's nothing specified for this case, I defaults to zero.
posted by inigo2 at 8:14 AM on June 22, 2007