Biff - I want to write the formula so that it responds to the worksheet
layout I displayed. Bottom line the formula must calculate the average of
all "non-zero" columns f18:m18, and subtract the average of all
"non-zero"columns f22:m22.
Any ideas?

Thanks
--
Sony Luvy


"Biff" wrote:

Hi!

So you want to exclude 0 values. The 0's at the end of the range are
probably due to the row below not having a value entered.

What if you have this:

************WORKSHEET LAYOUT****************
COLUMN D E F G H I J K L M
ROW
18 0% 100% -25% 50% 0% 0% 0% 0%
19 4 4 8 6 9

Do want to exclude the first 0 also?

Biff

"sony654" wrote in message
...
Your help will be appreciated. PROBLEM: The formula below returns an
answer
of 100% (WHICH ISN'T RIGHT). The correct result should be 11%.

=IF(COUNTIF(F18:M18,"<0"),LOOKUP(2,1/(F18:M18<0)*ISNUMBER(F18:M18)),AVERAGE(F18:M18)-AVERAGE(F22:M22))

WHAT I AM TRYING TO ACCOMPLISH (LAYOUT BELOW):
1) LOOKUP CALCULATED VALUES IN F18:M18
I.E. F18:I18
2) LOOKUP CALCULATED VALUES IN F22:M22
I.E. F22:I22
3) SUBTRACT INCREMENTING AVERAGE OF F22:M22 FROM
INCREMENTING AVERAGE OF F18:M18

************WORKSHEET LAYOUT****************
COLUMN D E F G H I J K L M
ROW
18 50% 33% -25% 50% 0% 0% 0% 0%
19 4 6 8 6 9
20
21 5 4 2 6 2
22 -20%-50% 200% -67% 0% 0% 0% 0%



Thank-you - Sony



--
Sony Luvy