what formula do i use and how?...
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: what formula do i use and how?...

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I need to enter a formula that returns more than two possible results like the "IF" formula. For example if cell E2's value is 'A' then I need cell G2 to return a certain formula such as (F2*.35). If E2 is 'B' then I need G2 to return a different formula and so on. Up to 7 different possible values for cell E2. Hope this makes sense.
    Thanks for any suggestions.

    [ This Message was edited by: saa38 on 2002-03-20 16:59 ]

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    You can nest up to 7 IF statements, although with some clever tricks, you can have more than 7.

    You can also use Boolean algebra to overcome the 7 IF function limit and the formula might be easier to decipher.

    Using CHOOSE is a good option as well.

    The best way may be to use a lookup table and VLOOKUP, HLOOKUP, LOOKUP, INDEX/MATCH.

    Please post more details for a more specific answer.

    Regards,
    Jay

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the reply. How do you enter more than one IF function in a cell?

  4. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Hi,

    With a bit from your post.

    Most basic way using IF
    =IF(E2="A",F2*0.35,IF(E2="B",F2*0.4,IF(E2="C",F2*0.5,0)))

    Better way using IF
    =F2*IF(E2="A",0.35,IF(E2="B",0.4,IF(E2="C",0.5,0)))

    One Boolean way
    =((E2="A")*0.35+(E2="B")*0.4+(E2="C")*0.5)*F2

    This should get you started.

    Regards,
    Jay

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com