Looking-up Columns w/calc'd Values ONLY to Calculate Average

[sony654]

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

 

[Biff]

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]

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

 

[Biff]

Try this:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=AVERAGE(IF(F18:M18<>0,F18:M18))-AVERAGE(IF(F22:M22<>0,F22:M22))

Format as PERCENTAGE

Biff

 

[sony654]

Biff - Thanks. We are almost there. I applied the formula:
=AVERAGE(IF(F18:M18<>0,F18:M18))-AVERAGE(IF(F22:M22<>0,F22:M22))

but what does key combo of ctl, hift, enter mean, and how do I execute it?

When the formula did work a couple of times, and didn't default to a
"value" error, it did not calculate the percent difference until I entered
values in column I.

Calculations in 18f:18h, and 22f:22h did not trigger the formula to
calculate and display a percent difference.

Your thoughts? Thanks for your continued assistance.

 

[Biff]

but what does key combo of ctl, hift, enter mean, and how do I execute it?

The formula is an array formula. For it to work properly you MUST use a
combination of keys strokes to enter it. That combination of keys are the
CTRL key, the SHIFT key and the ENTER key.

Try this:

Double click the cell that the formula is in.

Hold down both the CTRL key and the SHIFT key then hit ENTER.

If done properly Excel will enclose the formula in squiggly braces { }. You
cannot just type these braces in. You MUST use the key combo. Also, if you
ever edit an array formula it MUST be re-entered using the key combo.

Biff