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

    Nested Formula - 'VALUE' result (Excel 2000)

    I'm having difficulting producing results from a formula that I built. This is for a sales incentive scale which has 3 variables: threshold, target and max earning opporunities based which are calculated as a percentage of their salary. I thought that I was on the right track, however, I'm getting a result of "VALUE" instead of a percentage. The results should be either 0%, 4%, 6% or 8% based on the % Chg that column D states. Would someone be willing to look at my current formula to spot where I could have gone wrong? The goals are this: If the rep has not brought in at least as much this periord this year as they did the same period last year, they are compensated at 0%. If the meet the same sales figure as last year, they receive 4%. If they exceed the sales figure from last year from 1-5%, they receive 6%. If the rep exceeds their goal by 5%, they receive 8%. Thanks in advance!

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

    Re: Nested Formula - 'VALUE' result (Excel 2000)

    Sorry for the inconvenience with the word doc. I had other worksheets and info in there. Just didn't want to copy and paste I guess.

    Thank you for the formula. That worked!

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Nested Formula - 'VALUE' result (Excel 2000)

    I think you mistyped and meant:
    <pre>=IF(D2<0,0,IF(D2=0,0.04,IF(D2<font color=red>></font color=red>0.05,0.08,0.0<font color=red>6</font color=red>)))</pre>


    Steve
    PS though he could want:
    <pre>=IF(D2<0,0,IF(D2<0.01,0.04,IF(D2>0.05,0.08,0. 06)))</pre>

    to allow 0-1% to be "no change"

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

    Re: Nested Formula - 'VALUE' result (Excel 2000)

    No need to copy and paste. Just delete what you don't want to send and SaveAs a new workbook.
    Legare Coleman

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

    Re: Nested Formula - 'VALUE' result (Excel 2000)

    No and yes. I meant the ">=" since the original formula did not look like it returned anything if D2=5%. I made a guess that ">=" was what was wanted. The .05 should have been .06 though. I will go back and correct that. Thanks.
    Legare Coleman

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

    Re: Nested Formula - 'VALUE' result (Excel 2000)

    Why did you upload a word document with a picture of the spreadsheet? I can't tell exactly what is in column D and I can't tell how column E is formatted. It is also not possible to test a solution without retyping the entire spreadsheet, which can't be done accurately without knowing exactly how the values are entered and formatted. It would be much easier to give you a correct answer if you had uploaded the Excel workbook file.

    Making some guesses on what is in columns D and E, the following formula may be what you want:

    <pre>=IF(D2<0,0,IF(D2=0,0.04,IF(D2>=0.05,0.08,0.06 )))
    </pre>


    Edited by Legare to correct formula as pointed out by Steve.
    Legare Coleman

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Nested Formula - 'VALUE' result (Excel 2000)

    I used his description:
    "If they exceed the sales figure from last year from 1-5%, they receive 6%. If the rep exceeds their goal by 5%, they receive 8%"

    Steve

Posting Permissions

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