Results 1 to 7 of 7

20040408, 13:44 #1
 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 15%, they receive 6%. If the rep exceeds their goal by 5%, they receive 8%. Thanks in advance!

20040408, 14:13 #2
 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!

20040408, 14:28 #3
 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 01% to be "no change"

20040408, 14:32 #4
 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

20040408, 14:36 #5
 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

20040408, 14:38 #6
 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

20040408, 15:36 #7
 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 15%, they receive 6%. If the rep exceeds their goal by 5%, they receive 8%"
Steve