Results 1 to 13 of 13
  1. #1
    Star Lounger
    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!
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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

  3. #3
    Star Lounger
    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"
    Attached Files Attached Files

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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
    Attached Files Attached Files

  5. #5
    Star Lounger
    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!

  6. #6
    WS Lounge VIP sdckapr's Avatar
    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

  7. #7
    Star Lounger
    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 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. #8
    WS Lounge VIP sdckapr's Avatar
    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 "brain-fart"
    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. #9
    Star Lounger
    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 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. #10
    WS Lounge VIP sdckapr's Avatar
    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

  11. #11
    Star Lounger
    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!
    Attached Files Attached Files

  12. #12
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 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]

  13. #13
    WS Lounge VIP sdckapr's Avatar
    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

Posting Permissions

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