Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

Getting an average age from three sheets sorted by company.

Latest post Sun, Nov 9 2014 8:53 AM by Jerard. 4 replies.
  • Tue, Oct 28 2014 10:05 AM

    • Jerard
    • Top 150 Contributor
    • Joined on Sun, Mar 2 2014
    • Posts 8
    • Points 104

    Getting an average age from three sheets sorted by company.

    Hi all, I'm pulling my hair out on this one. The formua I have now is on H60:

     

    =IFERROR(AVERAGEIFS('M1'!H4:H33,F4:F33,1,C4:C33,"Y"),0)

     

    Column H is the age of the client

    Column F the company code (1 - 14) that the client has business with

    Column C is if a client is active or not

     

    M1 is the sheet name for January, and it seems to be working good. But I need to be able to include M2 and M3 but nothing I do works. The other columns work fine (I, J...etc). My objective is to calculate the quarterly average age sorted by company. I've included a screenshot, if it helps.

    Also I'm using Excel 2013 but will need it to be compatable with older virgins. Opps, I mean versions.

     

    Thank you all.

     

     

    • Post Points: 21
  • Tue, Oct 28 2014 8:19 PM In reply to

    • Omar
    • Top 10 Contributor
    • Joined on Sat, Jan 12 2008
    • Kitchener, ON
    • Posts 217
    • Points 2,949

    Re: Getting an average age from three sheets sorted by company.

    If I'm reading this right, you are logging each month on a separate sheet. Is there any way you can get everything into one table? If so, reporting gets a lot easier. Pivot tables might even go a long way towards your needs.

    If you are stuck on having a separate data tab for each month, I think you are left with using (SUMIFS('M1'!...,xx)+SUMIFS('M2'!...,xx)+SUMIFS('M3'!...,xx))/(COUNTIFS('M1'!...,xx)+COUNTIFS('M2'!...,xx)+COUNTIFS('M3'!...,xx))

    Pardon my brutal shortcutting in the example formulas.

     

    Omar Freeman Kitchener, ON

    • Post Points: 21
  • Wed, Oct 29 2014 9:19 AM In reply to

    • Jerard
    • Top 150 Contributor
    • Joined on Sun, Mar 2 2014
    • Posts 8
    • Points 104

    Re: Getting an average age from three sheets sorted by company.

    Thanks for the response. Each tab (month) represents a different sales month, so it has to stay like that. I played around with your formula but I couldn't get it to work. I'm not an Excel expert, I'm just an insurance agent working on this for my own, and other agents, use, and it's sortof a hobby, as well.

    In working with this forumula, I couldn't figure out what xx is supposed to be nor what the "..." represesnts. Perhaps if I knew that I could get it to work.

     

    Thanks

    • Post Points: 21
  • Wed, Oct 29 2014 11:01 AM In reply to

    • Omar
    • Top 10 Contributor
    • Joined on Sat, Jan 12 2008
    • Kitchener, ON
    • Posts 217
    • Points 2,949

    Re: Getting an average age from three sheets sorted by company.

    No problem as far as not being an expert. I was just a sales guy who learned Excel and eventually became the "goto" Excel reporting guy in our business. It took a lot of hours of saying something has to work, and just making it work to get where I am.

    Basically, I was just using the xx and ... as placeholders for the stuff that is required by those functions. They are similar to the AVERAGEIFS so assumed you would be able to use them. Since AVERAGE is the same as SUM/COUNT I was just pointing you to break it out as AVERAGEIFS can't handle multiple ranges.

    In a cleaner view, this looks like:

    (SUMIFS + SUMIFS + SUMIFS)/(COUNTIFS + COUNTIFS + COUNTIFS)

    For the COUNTIFS function, you need COUNTIFS(count_range,criteria).

    For the SUMIFS function, you need SUMIFS(sum_range,criteria_range,criteria). Optional is a second criteria range and second criteria.

    This is similar to AVERAGEIFS which needs AVERAGEIFS(average_range,criteria_range,criteria).

    I have lots of reports that have multiple months and years of sales. I put all the sales in one data table that has a date of sale column. Then I use reports with formulas that look only at the specific month (using multiple criteria_range and criteria to pull only that data) for the sales information. Life is a lot simpler that way.

    BTW, your original example didn't have the sheet name in each of the data ranges in your formula. Make sure you are including that if the reference is to a different sheet.

    Omar Freeman Kitchener, ON

    • Post Points: 21
  • Sun, Nov 9 2014 8:53 AM In reply to

    • Jerard
    • Top 150 Contributor
    • Joined on Sun, Mar 2 2014
    • Posts 8
    • Points 104

    Re: Getting an average age from three sheets sorted by company.

    Omar,

    Thanks for the detailed response. I played around with this formula for several days but could not get it to average the age correctly. However, I did come up with an alternate solution that worked:

    I created a range of formulas in a hidden area and used those totals to give me the correct, and idemized, average age.

    I wanted to follow up on this not just abandon the tread. Thanks again.

    Jeff

    • Post Points: 5
Page 1 of 1 (5 items) | RSS
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.