If statement with negative numbers = stumped analyst

[lukep10]

Ok i've got a massive workbook with sales data and the purpose is to create a "report" card for all of the salepeople in various categories to show strengths and areas to improve upon. Since I've assumed this project I've decided to automate as much as is practical since this is run frequently.

Everything is good except for the Gross Profit where there is a sticky weighting system as follows

Quota less Actual = X
0.0% = 10
-0.2% = 9
-0.4% = 8
-0.6% = 7
-0.8% = 6
-1.0% = 5
-1.2% = 4
-1.4% = 3
-1.6% = 2
-1.8% = 1
-2.0% = 0

So I've tried the following if statement but and I can't seem to get it right

=IF(E4>=0,"10",IF(E4>=-0.002,"9",IF(E4<=-0.004,"8",IF(E4<=-0.006,"7",IF(E4<=-0.008,"6",IF(E4<=-0.01,"5",IF(E4<=-0.012,"4",IF(E4<=-0.014,"3",IF(E4<=-0.016,"2",IF(E4<=-0.018,"1","0"))))))))))

Any help would be greatly appreciated I've beat my head against a wall for three days now.

[darkyam]

=if(e4>0,10,if(e4<-0.2,0,10-(roundup(e4*5,2)/5)/-0.002))

If you wanted to go the other way on scoring (e.g., anything better than -.8%=7 instead of 6), just change roundup to rounddown.

[ChemistB]

I think the easiest thing to do is to use the VLOOKUP function. Create a table of your criteria going from lowest to highest. Then highlight the table and name it (I called it quota in my example) (Name it by highlighting and then entering the table name in the name box).
VLOOKUP uses the following format[quote]=VLOOKUP(value to lookup or cell where value is, table range or name, column to return 2 in your case)[/code]

Look at my example and check Excel help and let us know if you have any questions.

ChemistB

[daddylonglegs]

I agree with ChemistB. A lookup table is probably best, easier to change too.....but if you wanted a single formula try

=MIN(10,CEILING(MAX(0,(E4+0.02)*500),1))

[nrage21]

I kind of like the Index/Match combo better than I like Vlookup... it adds a lot more flexibility to the search.

please see the attached... the sample file borrows from the concepts utilized by Chemistb. The match type condition on the Match WF is set to -1 so it could find the smallest value that is greater than or equal to the lookup value.

Regards,
nrage21

[lukep10]

Thank you all so very much! I'm going to try these today in several versions of the report. I have a good knack for Excel, but where do you guys learn some things like this? I found nothing in my excel bibles that even got me close!