Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Redmond, Washington, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IF Function (Excel 2000 SR 1)

    I'd like to apply some criteria to a percentage to indicate whether something is on budget, a little bit over or under budget or a lot over or under budget. I can't seem to figure out a way to get Excel to accept these criteria no matter which way I try to do it. Can anybody help? The criteria are
    Criteria
    Within 10% On budget
    Within 25% Slightly above or below
    > 25% Significantly above or below

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF Function (Excel 2000 SR 1)

    If the percent is in A1, then something like this should do what you want:

    <pre>=IF(A1>0.25,"Significantly above or below",IF(A1>0.1,"Slightly above or below","On budget"))
    </pre>

    Legare Coleman

  3. #3
    New Lounger
    Join Date
    Jan 2001
    Location
    Redmond, Washington, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF Function (Excel 2000 SR 1)

    Thanks. But I guess I didn't say this right. I guess I need something that shows the variance from 100%. So it a percentage is bewteen 90% and 110% it would be on budget. If between 75% and 90% AND bewteen 110% and 125% - slightly above or below. and below 75% or above 125% - significantly above or below.
    Here are my percentages.
    Percentage
    103%
    84%
    80%
    112%
    130%
    163%
    81%
    164%
    73%
    83%
    29%

    When I try to use between in the function Excel rebels.

  4. #4
    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 Function (Excel 2000 SR 1)

    Using Legare's formula, assuming that your percentage is in A1 and down column A, substitute the A1 reference with

    ABS(1-A1)

    and copy down as required.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF Function (Excel 2000 SR 1)

    OK, Try:

    <pre>=IF(ABS(1-A1)>0.25,"Significantly above or below",IF(ABS(1-A1)>0.1,"Slightly above or below","On budget"))
    </pre>

    Legare Coleman

  6. #6
    New Lounger
    Join Date
    Jan 2001
    Location
    Redmond, Washington, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF Function (Excel 2000 SR 1)

    That's perfect! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> I appreciate everybody's help on this.

Posting Permissions

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