XL97- Ignore Zero in avgerage - Every 2nd column

[BatFace]

Hi Gurus,

I am working on a spreadsheet which has data for each day of the week, and I want to average this by the number of days that has passed (skipping any zero's).

I have tried =sum(range)/countif(range<>,&quot;0&quot and {=average(if(range<>0,range))}, but these formula's won't work because the cells with data are b49,d49,f49 -ie every second column...

so I am trying a nested if formula using =if(b49>0,b49,if(d49>0,(b49+d49)/2,if(f49>0,(b49+f49+f49)/3 and so on up to cell n49 (adding b through to n and dividing by 7,&quot;&quot....however this is not working either, this formula is returning the value in cell b49 only....I'm sure there is a simple answer, but this doing my head in.....

I can't move the columns so they are next to each other either because there are too many other reports linked to this one by different users..

I would be grateful for any suggestions - thank you in advance! :

 

[mymou]

Hi BatFace

Im sure there must be a better solution - but you could do the average the old way.

Sum the values you need and divide by how many there are (less the number of values that contain zero).

=SUM(A1:G1)/(4-COUNTIF(A1:G1;0))

Not very flexible - might might be good enough for you needs.


Stew

 

[ETID]

Mymou was close, but this might suit just a tad cleaner


=SUMIF(B49:F49,&quot;>0&quot/COUNTIF(B49:F49,&quot;>0&quot

 

[BatFace]

That's fantastic thanks for the formula Etid. Sorry Mymou this formula returned zero (?). Thanks to both for your very prompt help! :