Results 1 to 13 of 13
Thread: Averaging cells (Excel 2000)

20021121, 13:15 #1
 Join Date
 Jun 2002
 Location
 Worthington, Ohio, USA
 Posts
 66
 Thanks
 0
 Thanked 0 Times in 0 Posts
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!

20021121, 13:22 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20021121, 13:42 #3
 Join Date
 Jun 2002
 Location
 Worthington, Ohio, USA
 Posts
 66
 Thanks
 0
 Thanked 0 Times in 0 Posts
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"

20021121, 16:27 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20021122, 14:30 #5
 Join Date
 Jun 2002
 Location
 Worthington, Ohio, USA
 Posts
 66
 Thanks
 0
 Thanked 0 Times in 0 Posts
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!

20021122, 16:08 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20021202, 15:23 #7
 Join Date
 Jun 2002
 Location
 Worthington, Ohio, USA
 Posts
 66
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Averaging cells (Excel 2000)
Me again. I did look at your formulas for rows 49, 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?

20021202, 15:56 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Averaging cells (Excel 2000)
Sorry, I had a "brainfart"
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

20021202, 18:05 #9
 Join Date
 Jun 2002
 Location
 Worthington, Ohio, USA
 Posts
 66
 Thanks
 0
 Thanked 0 Times in 0 Posts
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 MondayThursday 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

20021202, 19:26 #10
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20021205, 20:47 #11
 Join Date
 Jun 2002
 Location
 Worthington, Ohio, USA
 Posts
 66
 Thanks
 0
 Thanked 0 Times in 0 Posts
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!

20021205, 21:23 #12
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,158
 Thanks
 2
 Thanked 453 Times in 372 Posts
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%")Cheers,
Paul Edstein
[MS MVP  Word]

20021205, 23:36 #13
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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