Error Checking in Excel
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

Some videos you may like

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
  •