# Thread: Averaging cells (Excel 2000)

1. ## Averaging cells (Excel 2000)

Attached is a worksheet that I need to have different averages in. I need to average a week's worth of calls/emails for a call center, excluding the dates that my staff members were not here. I like to have a 0 as a place holder instead of a blank. Each day's data is not in a cell right next to one another as I have other calculations going on here as well. Please advise on how I can do these averages.

Thanks!

2. ## Re: Averaging cells (Excel 2000)

Can't you just use:
total / Countif(range,">0")/2

I use the 2 since you have 2 values (total and percent) when it is >0

Steve

3. ## Re: Averaging cells (Excel 2000)

Do I just use the SUM function or =AVERAGE before entering the cell with the total? Will the range be off if there are blank cells between the values? I don't need the % used for this purpose. Just another way of looking at the data. If I divide by 2, I still don't get the right answer. Here's what it looks like in the attachment. Also, I need to get rid of the "#DIV2"

4. ## Re: Averaging cells (Excel 2000)

Sum/Count = average, there is no need to average.

How is this. got rid of div/0 with if statement

Steve

5. ## Re: Averaging cells (Excel 2000)

This only works for those cells that don't have the percentage. Look in the other rows where there are percentages. I have to skip these cells to get an actual count. How can I represent A1 AND C1 AND E1, etc etc. I only know how to use "," but that means something different in the formulas.

Thanks!

6. ## Re: Averaging cells (Excel 2000)

The ones with the % (Rows 4 -9) I used sum/countif/2. SInce the ones with the percentages have double the count!

Steve

7. ## Re: Averaging cells (Excel 2000)

Me again. I did look at your formulas for rows 4-9, however, the answer that it gives doesn't make sense. The total for the days with values >0 (4 of the days are >0) is 104. 104/4 days = 26. The value given by the formula as it stands is 7, which is way off. Any thoughts? Is it calculating the percentages in there as well when dividing by 2?

8. ## Re: Averaging cells (Excel 2000)

1) You need to MULTIPLY not divide by 2 or use parenthese. Countif/2 os correct but sum/(countif/2) = sum/countifi*2
2) Range should be extended to column J:

=IF(COUNTIF(A4:J4,">0")=0,"",K4/COUNTIF(A4:J4,">0")*2)

Sorry,
Steve

9. ## Re: Averaging cells (Excel 2000)

Actually, it just divided the totals by 2 instead of the number of days that actually had totals. In other words, if someone called Monday-Thursday but not Friday, I needed the formula to read: give me an average for all the days actually here and making calls. I think this formula DID work though: =IF(COUNTIF(A4:I4,">0")=0,"",K4/COUNTIF(A4:I4,">=1")). For whatever reason. I'm not sure what it means but it works! Thanks again for all of your help!!!! I really appreciate it

10. ## Re: Averaging cells (Excel 2000)

I did with the countif/2 since I was afraid that:
<pre> =IF(COUNTIF(A4:I4,">0")=0,"",K4/COUNTIF(A4:I4,">=1"))
</pre>

there would be an extra hit on those rare occasions when the percentage was 100% (=1). But if that will never happen this formula will work and will work for ALL the examples not just the ones with percentages.

Steve

11. ## Re: Averaging cells (Excel 2000)

OK, I thought I had it but I'm still stuck. Here is a sample. My averages are still not coming out right. I've highlighted them in yellow. The first 2 are okay but from row 5 on down, they are not correct. I'm averaging the total call made in a day by the number of days this person worked. Thanks!

12. ## Re: Averaging cells (Excel 2000)

Hi there,

Your problem on row 5 is caused by the fact that the percenatge in col K is 100%, and your averaging formula was based on a count of items in columns ">=1". To avoid that, you need to change the formula to evaluate only the cells with numbers (ie not percentages). If you put the following into N3 and copy it down, you should get the right answer:
=IF(L3=0,0,L3/COUNT(B3<>0,D3<>0,F3<>0,H3<>0,J3<>0))

Cheers

PS: In some of your % formulae, you've specified a false answer for the 'if' tests of "0%", which is a text string rather than a value. You could just as easily use 0, which has the advantage of being able to be used as a number in other formulae if you need it. Alternatively, you could turn all the % cells into text strings, so that they aren't treated as values by the formula is column N. For example, you could change the formula in K4 to:
=IF(J3,TEXT(J4/J3,"?0.00%"),"0%")

13. ## Re: Averaging cells (Excel 2000)

As I mentioned before, That is why I used:
<pre>=IF(COUNTIF(A4:J4,">0")=0,"",K4/COUNTIF(A4:J4,">0")*2)
</pre>

I was afraid that you would get a 100% (=1!) and you can't use >1 since you have some 1s

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•