Join 3,433 readers in helping fund MetaFilter (Hide)

Tags:

Make Excel Make Coleslaw!
June 21, 2007 8:13 PM   Subscribe

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 answers total) 2 users marked this as a favorite
 
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


« Older Don't ask me why, but this que...   |  Unable to view preview picture... Newer »
This thread is closed to new comments.