Average of cells which are not in a range which ignores any zero values

**Scott_88** · 12-04-2013, 06:42 AM

Hi

Looking for some help.

I have a worksheet which has various figures for each day of the week however I need to establish the weekly average of these figures.

Due to the way in which the figures are displayed, I am unsure how to use a formula which does not require a range with cells located adjacent to one another.

I have attached a test sheet as an example. The cells in yellow require the formula and I need a weekly average for criteria 1-3. This formula also needs to be compatible in Excel 2003

Test Sheet.xls

Thanks

Scott

**XOR LX** · 12-04-2013, 06:58 AM

Hi,

One way is this array formula in AF4 and copy across and down:

=AVERAGE(IF($D$3:$AD$3=AF$3,$D4:$AD4))

Regards

**canapone** · 12-04-2013, 06:59 AM

Hi,

you could try in AF4

Please Login or Register  to view this content.

formula to be copied down and to the right

Hope it helps

**ragulduy** · 12-04-2013, 07:01 AM

=AVERAGE(D4,H4,L4,P4,T4,X4,AB4)

in AF4, copy down and across.

Average of cells which are not in a range which ignores any zero values

LinkBack

Thread Tools

Rate This Thread

Display

Average of cells which are not in a range which ignores any zero values

Re: Average of cells which are not in a range which ignores any zero values

Re: Average of cells which are not in a range which ignores any zero values

Re: Average of cells which are not in a range which ignores any zero values

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Formula to average range of cells only if difference between values is no more than 1

[SOLVED] Finding average that ignores error and zeros

Looking for a code for an average formula that ignores hidden cells.

to change a descending rank formula that ignores '0's to one that ignores blank cells

[SOLVED] Average a set of figures which ignores 0 entries

Bookmarks

Bookmarks

Posting Permissions