Average Problem

[Guest]

I've got a spreadsheet with columns labeled “Year 1†all the way to “Year 10†that I need to take an average on. I’ve got another cell that will tell me how many years I should take the average of. The problem I’m having is that due to timing issues, I could have numbers from Year 3 to Year 9 and no numbers in years 1 and 2. Because of the way the spreadsheet is formatted, a blank cell shows a 0 that gets taken into effect when I do an average formula. Is there any way around this issue? Is there a formula I can use to help me with this issue

Also, I could have an issue where my numbers fall in year 3 to year 9, but I only need to average the first four years (because of the cell that is telling me how many years I should take the average on). Is there anyway to work around this problem as well? Is there a formula I can use to help me with this issue

Thanks in advance for the help

 

[Charlie]

One possible way would be to range name the years. For
example 2001 name 'one', 2002 name 'two' and 2003
name 'three'.

Now to average years 2001,2002,and 2003 enter

=SUM(One,Two,Three)/COUNTIF(One:Two:Three,">0")

Hope this helps

Charlie O'Neill

-----Original Message-----
I've got a spreadsheet with columns labeled â?oYear 1â?

all the way to â?oYear 10â? that I need to take an
average on. Iâ?Tve got another cell that will tell me how
many years I should take the average of. The problem Iâ?
Tm having is that due to timing issues, I could have
numbers from Year 3 to Year 9 and no numbers in years 1
and 2. Because of the way the spreadsheet is formatted, a
blank cell shows a 0 that gets taken into effect when I do
an average formula. Is there any way around this issue?
Is there a formula I can use to help me with this issue?

Also, I could have an issue where my numbers fall in year

3 to year 9, but I only need to average the first four
years (because of the cell that is telling me how many
years I should take the average on). Is there anyway to
work around this problem as well? Is there a formula I
can use to help me with this issue?

 

[Leo Heuser]

If I have understood you correctly, here is
one way to overcome both issues.

Assuming numbers for the various years in B2:K2,
and the number telling how many years to average
in A1, enter this formula in any cell:

=IF(COUNTIF(B2:K2,"<>0")>=A1,SUMPRODUCT((COUNTIF(
OFFSET(B2,,,,COLUMN(B2:K2)-COLUMN(B2)+1),"<>0")<=A1)*
B2:K2)/A1,"No cigar!")

Example:
In B2:K2: 0,0,3,0,6,5,0,3,4,5
In A1: 4

Average=(3+6+5+3)/4 = 4.25

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

I've got a spreadsheet with columns labeled "Year 1" all the way to "Year

10" that I need to take an average on. I've got another cell that will tell
me how many years I should take the average of. The problem I'm having is
that due to timing issues, I could have numbers from Year 3 to Year 9 and no
numbers in years 1 and 2. Because of the way the spreadsheet is formatted,
a blank cell shows a 0 that gets taken into effect when I do an average
formula. Is there any way around this issue? Is there a formula I can use
to help me with this issue?

Also, I could have an issue where my numbers fall in year 3 to year 9, but

I only need to average the first four years (because of the cell that is
telling me how many years I should take the average on). Is there anyway to
work around this problem as well? Is there a formula I can use to help me
with this issue?