I am working on a Excel spreadsheet that is a score sheet. I need some
help as I keep getting a divide by 0 error. My knowledge of Excel is
not great.
---------------------------------
Here is the situation -

I have 12 months (Jan-Dec) in which I score newsletters on 25 items -
18 of which are positive values, 4 are negeative values, and 3 are
disqualifications. I have it set up where I total each month's score.
No problem here. However, if a newsletter gets disqualified for some
reason the total for that month's newsletter is 0. Now this is where
the problem comes in. I want to automatically average the scores as I
go. Here is the formula I have in one cell for the months total -

=IF((E43)="Y",0,IF((E44)="Y",0,IF((E45)="Y",
0,SUM(E5:E11,E13:E37,E39:E42))))


Now all the month's totals are showing a 0 value. And this is where
the problem is.

I origianlly had it set up to average the score <0 -

=IF(SUM(D46:O46)=0,0,(SUM(D46:O46))/COUNTIF(D46:O46,"<0"))

But this isn't correct. I need it to average the scores as I go, which
could include a 0 score not just non-zero scores.

I hope that makes sense. Any help would be appreciated. Thanks in
advance.

=AVERAGE(IF(SUM(D46:O46)0,D46:O46))

ctrl+shift+enter, not just enter


" wrote:

I am working on a Excel spreadsheet that is a score sheet. I need some
help as I keep getting a divide by 0 error. My knowledge of Excel is
not great.
---------------------------------
Here is the situation -

I have 12 months (Jan-Dec) in which I score newsletters on 25 items -
18 of which are positive values, 4 are negeative values, and 3 are
disqualifications. I have it set up where I total each month's score.
No problem here. However, if a newsletter gets disqualified for some
reason the total for that month's newsletter is 0. Now this is where
the problem comes in. I want to automatically average the scores as I
go. Here is the formula I have in one cell for the months total -

=IF((E43)="Y",0,IF((E44)="Y",0,IF((E45)="Y",
0,SUM(E5:E11,E13:E37,E39:E42))))


Now all the month's totals are showing a 0 value. And this is where
the problem is.

I origianlly had it set up to average the score <0 -

=IF(SUM(D46:O46)=0,0,(SUM(D46:O46))/COUNTIF(D46:O46,"<0"))

But this isn't correct. I need it to average the scores as I go, which
could include a 0 score not just non-zero scores.

I hope that makes sense. Any help would be appreciated. Thanks in
advance.

I tried your formula but that doesn't seem to work. For example, I put
in a total score for Jan and Feb of 45. The average should be 45. Your
formula gaved me.

By the way, what does CTRL + SHIFT +ENTER vs ENTER do? I do
appreciate your suggestion.

On Jan 31, 10:45 pm, Teethless mama
wrote:
=AVERAGE(IF(SUM(D46:O46)0,D46:O46))

ctrl+shift+enter, not just enter

" wrote:
I am working on a Excel spreadsheet that is a score sheet. I need some
help as I keep getting a divide by 0 error. My knowledge of Excel is
not great.
---------------------------------
Here is the situation -

I have 12 months (Jan-Dec) in which I score newsletters on 25 items -
18 of which are positive values, 4 are negeative values, and 3 are
disqualifications. I have it set up where I total each month's score.
No problem here. However, if a newsletter gets disqualified for some
reason the total for that month's newsletter is 0. Now this is where
the problem comes in. I want to automatically average the scores as I
go. Here is the formula I have in one cell for the months total -

=IF((E43)="Y",0,IF((E44)="Y",0,IF((E45)="Y",
0,SUM(E5:E11,E13:E37,E39:E42))))

Now all the month's totals are showing a 0 value. And this is where
the problem is.

I origianlly had it set up to average the score <0 -

=IF(SUM(D46:O46)=0,0,(SUM(D46:O46))/COUNTIF(D46:O46,"<0"))

But this isn't correct. I need it to average the scores as I go, which
could include a 0 score not just non-zero scores.

I hope that makes sense. Any help would be appreciated. Thanks in
advance.

Control Shift Enter designates it as an array formula.
Look up array formula in Excel help.
--
David Biddulph

wrote in message
ups.com...
I tried your formula but that doesn't seem to work. For example, I put
in a total score for Jan and Feb of 45. The average should be 45. Your
formula gaved me.

By the way, what does CTRL + SHIFT +ENTER vs ENTER do? I do
appreciate your suggestion.

On Jan 31, 10:45 pm, Teethless mama
wrote:
=AVERAGE(IF(SUM(D46:O46)0,D46:O46))

ctrl+shift+enter, not just enter

" wrote:
I am working on a Excel spreadsheet that is a score sheet. I need some
help as I keep getting a divide by 0 error. My knowledge of Excel is
not great.
---------------------------------
Here is the situation -

I have 12 months (Jan-Dec) in which I score newsletters on 25 items -
18 of which are positive values, 4 are negeative values, and 3 are
disqualifications. I have it set up where I total each month's score.
No problem here. However, if a newsletter gets disqualified for some
reason the total for that month's newsletter is 0. Now this is where
the problem comes in. I want to automatically average the scores as I
go. Here is the formula I have in one cell for the months total -

=IF((E43)="Y",0,IF((E44)="Y",0,IF((E45)="Y",
0,SUM(E5:E11,E13:E37,E39:E42))))

Now all the month's totals are showing a 0 value. And this is where
the problem is.

I origianlly had it set up to average the score <0 -

=IF(SUM(D46:O46)=0,0,(SUM(D46:O46))/COUNTIF(D46:O46,"<0"))

But this isn't correct. I need it to average the scores as I go, which
could include a 0 score not just non-zero scores.

I hope that makes sense. Any help would be appreciated. Thanks in
advance.