Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    Worthington, Ohio, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    If/then constructs in conditional formatting (Excel 2000)

    I have one cell value S3 dependent on cell value N3 and I want the S3 value to change based on what I enter into N3. For example: If N3 = 1, then S3 should equal 86. If N3 = 2, then S3 should equl 90. So on and so on.

    I know this is easy but I'm just learning

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: If/then constructs in conditional formatting (Excel 2000)

    Could you explain what you mean by 'so on'. What value would S3 hold if N3 = 3 ?. Depending on the number of possible values in N3, using IF THEN constructs may not be the best approach, or even possible in all cases.

    Andrew C

  3. #3
    Star Lounger
    Join Date
    Jun 2002
    Location
    Worthington, Ohio, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If/then constructs in conditional formatting (Excel 2000)

    Andrew -

    S3 would be 96 if N3 = 3. I'm in sales support and try to say...if my staff passes 1 lead over goal, I'll pay them A. If they pass 2 leads over goal, I'll pay them B, if they pass 3 leads over goal, I'll pay them C. There are only 3 levels they can go and then the dollar amount caps. Does that help?

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: If/then constructs in conditional formatting (Excel 2000)

    In that case the following formula in S3 should dowhat you want (Using nested IF staements) :

    <big>=IF(N3=3,96,IF(N3=2,90,86))</big>

    Andrew C

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: If/then constructs in conditional formatting (Excel 2000)

    Just to annoy Andrew <img src=/S/grin.gif border=0 alt=grin width=15 height=15>:

    =CHOOSE(1+N3,86,86,90,96)

    and if they generate no leads, you can change the first 86 to something else, such as zero.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: If/then constructs in conditional formatting (Excel 2000)

    John,

    If you really wanted to annoy Andrew, for the limited number of values you could have done
    =86 + (N3-1)*4 + (N3=3)*2

    I have too much time on my hands.

    Fred

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: If/then constructs in conditional formatting (Excel 2000)

    That's good, Fred! All our formulas probably yield errors or incorrect results for values of N3 greater than 3, so

    =86+(N3=2)*4+(N3>=3)*10

    Come Monday I won't have too much time on my hands. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: If/then constructs in conditional formatting (Excel 2000)

    We are not annoyed

    Andrew

Posting Permissions

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