Despite my best efforts I can't make this work. Here is the situation: Analyze the quality of the sales volumes (cell range) worksheet 1. Compare this year's sales volumes with last year's actual annual volumes (cell range) worksheet 2.
If: Actual exceeded last year by over 20% = Display "Excellent Year". If actual exceeded last year by 10-20% = Display "Good Year". If actual exceeed last year by 0-9.99% = Display "Average Year". If actual did not exceed last year display = "Poor Year".
So this is the formula I generated with all the IF statements:
=IF(ABS(F16 -The first cell of data in the column (-) G16 - The corresponding cell on the last year's data sheet)/F16<0.01%,"Poor Year",IF(ABS(F16-G16)/F16>20%,"Excellent Year",IF(ABS(F16-G16)/F16>10%,"Good Year",IF(ABS(F16-G16)/F16>=0.01%,"Average Year")))))
Most of this works correctly. The percentages are correct, and the "Excellent", "Good", "Average" all display except the "Poor Year". I can't figure out if I have something out of order in the IF statements that is making the formula not display the "Poor" years, or if it has something to do with the negative percentage this value involves. I've looked online everywhere I could and no answer ... so I hope you wonderful people can give a hand. Thanx.
Bookmarks