# Thread: Nested Formula - 'VALUE' result (Excel 2000)

1. ## 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. ## 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. ## 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. ## 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.

5. ## 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.

6. ## 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.

7. ## 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
•