Div/o error please Help

[pazza101]

hi
have several lines in a workbook as i need to take an eaverage of performance each day and then transfer the info to a summary sheet when the fields have at least one nuber above zero it works ok but at the start of the week we start a new sheet. giving the dreaded div/o error i know you can use the IF command to get rid of this but unsure how to implement it.


this is an example

=SUM('Tuesday nights'!B20:B28)/COUNTIF('Tuesday nights'!B20:B28,"<>0")

have a string like this for days and nights sunday through saturday then another for the summary.

would be grateful for any help

Kerrrie

 

[AlphaFrog]

Try something like this...

=IF(COUNTIF('Tuesday nights'!B20:B28,"<>0")=0,"",SUM('Tuesday nights'!B20:B28)/COUNTIF('Tuesday nights'!B20:B28,"<>0"))

 

[pazza101]

Try something like this...

=IF(COUNTIF('Tuesday nights'!B20:B28,"<>0")=0,"",SUM('Tuesday nights'!B20:B28)/COUNTIF('Tuesday nights'!B20:B28,"<>0"))

wow fast reply now that works but creates another small problem i have a summary sheet and dont need sunday day and saturday nights
so had this
=SUM(K17:V17)/COUNTIF(K17:V17,"<>0")
which worked ok before as i had zero in each box so now no zero it divde the performance x 12 instead if i was 3 shifts in to the week by 3. eg s0 101% + 103% + 104% divide this x 3 but its dividing by 12

kerrie

 

[Aladin Akyurek]

hi
have several lines in a workbook as i need to take an eaverage of performance each day and then transfer the info to a summary sheet when the fields have at least one nuber above zero it works ok but at the start of the week we start a new sheet. giving the dreaded div/o error i know you can use the IF command to get rid of this but unsure how to implement it.


this is an example

=SUM('Tuesday nights'!B20:B28)/COUNTIF('Tuesday nights'!B20:B28,"<>0")

have a string like this for days and nights sunday through saturday then another for the summary.

would be grateful for any help

Kerrrie

One of:

=SUM('Tuesday nights'!B20:B28)/MAX(1,COUNTIF('Tuesday nights'!B20:B28,"<>0"))

=SUM('Tuesday nights'!B20:B28)/MAX(1,INDEX(FRQUENCY('Tuesday nights'!B20:B28,0),2))

 

[pazza101]

One of:

=SUM('Tuesday nights'!B20:B28)/MAX(1,COUNTIF('Tuesday nights'!B20:B28,"<>0"))

=SUM('Tuesday nights'!B20:B28)/MAX(1,INDEX(FRQUENCY('Tuesday nights'!B20:B28,0),2))

first one fantastic all is good great help on here thank you

 

[Aladin Akyurek]

first one fantastic all is good great help on here thank you

You are welcome.