excel formula that doesn't default to 0

[Philea_92]

Hi,

I am trying to average a range of data that spreads accross many sheet
(one cell in each sheet). I am sorry if my explanation is vague onl
am struggling to describe the problem butr here goes.

=IF(ISERROR(AVERAGE(IF($H$4:$H$18=$H21,$N$4:$N$18))),"",AVERAGE(IF($H$4:$H$18=$H21,$N$4:$N$18)))

is a formula which all values in a column which are equal to a value i
column H21.

The problem I am faced with is that when creating a sheet to averag
all the values from each sheet using formula:

=SUM('calls closed 3, 4 Dec:Calls closed 15 Dec'!I21)/COUNTA('call
closed 3, 4 Dec:Calls closed 15 Dec'!I21)

the avaerage values are incorrect,

I think this is because it is counting the 0's which are returned whe
there are no values in the first formula.

can anyone help?


Thanks to Max

 

[Don Guillett]

=AVERAGE(Sheet4:Sheet15!$C$1)
will add the numbers in cell c1 and divide by the number of sheets.

 

[Philea_92]

thankyou very much

that formula creates the same issues as I had though

the cell reference I am trying to average is the result of th
formula:

=IF(ISERROR(AVERAGE(IF($H$4:$H$18=$H21,$N$4:$N$18)
)),"",AVERAGE(IF($H$4:$H$1
8=$H21,$N$4:$N$18)))

the problem is :

where there is no value for the above formula

=AVERAGE(Sheet4:Sheet15!$C$1) counts this as a zero value.

I hope that I am explaining myself okay

 

[Max]

Try changing the COUNTA to COUNT in the summary formula:

=SUM('calls closed 3, 4 Dec:Calls closed 15 Dec'!I21)/COUNTA('calls
closed 3, 4 Dec:Calls closed 15 Dec'!I21)
---------------
Btw, please note that the sheet refs given are examples only,
assuming the first sheet is: calls closed 3, 4 Dec
and the last sheet is: Calls closed 15 Dec

If you want to average for the whole month's worth of sheets
from 3 dec to 31 dec, change the last sheet ref to: Calls closed 31 Dec
[this assumes all other sheets for 5 dec - 30 dec are placed in-between
the first and last sheets]

 

[Philea_92]

how do I exclude sheets from the count formula?

=SUM('calls closed 3, 4 Dec:Calls closed 15 Dec'!I21)/COUNT('call
closed 3, 4 Dec:Calls closed 11 Feb'!I21)


say I didn't want to count "calls closed 17th dec" and "calls closed 1
feb"

 

[Max]

Just drag / move the sheet(s) you don't want included out of the "range"
covered by the first and last sheets stated in the formula,
i.e. either drag / move to the left of the first sheet, or
to the right of the last sheet.

 

[Philea_92]

Max I moved the sheets that didn't have any results in them to the righ
of the last sheet, only it didn't make any difference to the averag
scores (well not much)

is there a formula like

=SUM('calls closed 3, 4 Dec:Calls closed 15 Dec'!I21)/COUNT('call
closed 3, 4 Dec:Calls closed 11 Feb'!I21)

but exluding instsances where there are no values in the cell

 

[Philea_92]

sorry forget the last question think I have got it sorted now, thank
for your help

 

[Max]

Glad to hear that.
You're welcome !