+ Reply to Thread
Results 1 to 10 of 10

Alternative to *long* AVERAGEIFS formula, since all data is on a second Excel sheet?

  1. #1
    Registered User
    Join Date
    12-11-2012
    Location
    DC
    MS-Off Ver
    Excel 2010
    Posts
    7

    Alternative to *long* AVERAGEIFS formula, since all data is on a second Excel sheet?

    Hello all,

    Here's my formula, I'll explain below what I'm trying to accomplish since I'm hoping there's a viable way to do this. The stumbling block now is that the data (and which is not indicated in the formula below!!) is on a different sheet, and so this formula does not work to retrieve that data.

    =SUM((AVERAGEIFS(S27:S32,S27:S32,">"&TODAY()-90,P27:P32,"license*",Q27:Q32,"mailed*",S27:S32,">"&TODAY()-90))-(AVERAGEIFS(N27:N32,N27:N32,">"&TODAY()-90,P27:P32,"license*",Q27:Q32,"mailed*",S27:S32,">"&TODAY()-90)))

    Basically, I want to get the average number of days it took to process a license over the last 90 days. The limiters are the dates (S, N), the word "license" with a wildcard (P), and the word "mailed", with a wildcard (Q). So, what I'm doing is below:

    [Second part of the equation]:
    -(AVERAGEIFS(N27:N32,N27:N32,">"&TODAY()-90,P27:P32,"license*",Q27:Q32,"mailed*",S27:S32,">"&TODAY()-90)))

    I'm taking the 'average' of the "traffic inputted" (date) column (N) for today (minus) 90 where those rows contain cells with the term "mailed*" (column Q) and "license*" (column P) and have a "mailed date" (column S) of today-90.

    [First part of the equation]
    SUM((AVERAGEIFS(S27:S32,S27:S32,">"&TODAY()-90,P27:P32,"license*",Q27:Q32,"mailed*",S27:S32,">"&TODAY()-90))-

    Sum aiside, I'm taking the average of the "mailed date" column (column S) for today-90, where those rows contain the term "mailed*" (column "Q") and "license*" (column "S"), and have a "mailed date" (column S) of today-90.

    This combination will give me the average number of days over the last 90 days it took to complete license work - calculating from when the licenses were entered into the system until the time they were copleted.


    Is there an equivalent formula that I can use to make this work accross sheets?

    Thanks SO SO much for any help!!
    Last edited by restivo; 12-11-2012 at 11:13 AM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,143

    Re: Alternative to *long* AVERAGEIFS formula, since all data is on a second Excel sheet?

    Wherever you have a range like S27:S32, or Q27:Q32, in your formula, you need to put 'Sheet 2'! in front of it - the apostrophes are needed if your sheet name contains spaces. I think this means that you need to do it 10 times within your first formula.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    12-11-2012
    Location
    DC
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Alternative to *long* AVERAGEIFS formula, since all data is on a second Excel sheet?

    Thanks for the response. I am aware that the cell ranges should be preceded with, in my case, 'ACCESS DATA'!. I did have the sheet names with apostrophes in the formula as it was on my Excel sheet, but because that formula doesn't work due to it being an AVERAGEIFS across several pages, I deleted it from Excel. I didn't think it was necesary to edit the non-cross-tab formula before posting here.

    For sake of completeness, here's the full formula that is not working:

    =SUM((AVERAGEIFS('ACCESS DATA'!S27:S32,'ACCESS DATA'!S27:S32,">"&TODAY()-90,'ACCESS DATA'!P27:P32,"license*",'ACCESS DATA'!Q27:Q32,"mailed*",'ACCESS DATA'!S27:S32,">"&TODAY()-90))-(AVERAGEIFS('ACCESS DATA'!N27:'ACCESS DATA'!N32,'ACCESS DATA'!N27:'ACCESS DATA'!N32,">"&TODAY()-90,'ACCESS DATA'!P27:'ACCESS DATA'!P32,"license*",'ACCESS DATA'!Q27:'ACCESS DATA'!Q32,"mailed*",'ACCESS DATA'!S27:'ACCESS DATA'!S32,">"&TODAY()-90)))

    Thanks very much for any guidance on this!

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,143

    Re: Alternative to *long* AVERAGEIFS formula, since all data is on a second Excel sheet?

    Well, you only need to put the sheet name once before the range - you do not need:

    'ACCESS DATA'!N27:'ACCESS DATA'!N32

    for example: instead it should be this:

    'ACCESS DATA'!N27:N32

    This applies to the second part of your formula.

    Hope this helps.

    Pete

  5. #5
    Registered User
    Join Date
    12-11-2012
    Location
    DC
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Alternative to *long* AVERAGEIFS formula, since all data is on a second Excel sheet?

    Thanks. However, I did have the format in my Excel sheet correct when I had it in there, and so that's really not the problem. The problem is that AVERAGEIFS not working between 2 Excel sheets, and so I'm trying to find a solution to that.

    Any advice is greatly appreciated.

    Thanks.

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,143

    Re: Alternative to *long* AVERAGEIFS formula, since all data is on a second Excel sheet?

    AVERAGEIFS should work across another sheet, so what do you mean by "not working"?

    Is there any chance that you can post an example workbook ? The FAQ gives details of how to do this.

    Pete

  7. #7
    Registered User
    Join Date
    12-11-2012
    Location
    DC
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Alternative to *long* AVERAGEIFS formula, since all data is on a second Excel sheet?

    Thanks for the response. I get the result #VALUE! when I use the formula with values from another sheet. I think this should be replicatable. I think this is an established issue, and I did find some attempts, and some examples of, solutions in web searches. Unfortunately, none of the proposed solutions worked for me. Interestingly, a recent Google search pulled up a discussion on a MS website. The discussion subject was "BUG in Excel 2010 SUMIF & SUMIFS functions [EDIT: also AVERAGEIF & -IFS]" at http://social.technet.microsoft.com/Forums/en/excel/thread/cda9fef8-8ad4-4d56-8939-49b1ae50c4e7/ , so there are others who are having the same issues.

  8. #8
    Registered User
    Join Date
    12-11-2012
    Location
    DC
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Alternative to *long* AVERAGEIFS formula, since all data is on a second Excel sheet?

    Unfortunately it seems that no one could help out with this. But, I was able to come up with a solution myself, and so in case anyone needs any help finding a solution to the IFS-across-sheets Excel bug - Averageifs, sumifs, etc - here's the solution I came up with. It's pretty serious - so of course I appreciate any suggestions or pointers on simplifying it.

    =(SUMPRODUCT(--(ISNUMBER(SEARCH("License",'ACCESS DATA'!$H$2:$H$25000))),(ISNUMBER(SEARCH("Mailed",'ACCESS DATA'!$J$2:$J$25000)))*('ACCESS DATA'!$G$2:$G$25000>=TODAY()-90)*('ACCESS DATA'!$K$2:$K$25000)))/COUNTIFS('ACCESS DATA'!$H$2:$H25000,"License*",'ACCESS DATA'!$J2:$J25000,"Mailed*",'ACCESS DATA'!$K2:$K25000, ">="&TODAY()-90)-(SUMPRODUCT(--(ISNUMBER(SEARCH("License",'ACCESS DATA'!$H$2:$H$25000))),(ISNUMBER(SEARCH("Mailed",'ACCESS DATA'!$J$2:$J$25000)))*('ACCESS DATA'!$K$2:$K$25000>=TODAY()-90)*('ACCESS DATA'!$G$2:$G$25000)))/COUNTIFS('ACCESS DATA'!$H2:$H25000,"License*",'ACCESS DATA'!$J2:$J25000,"Mailed*",'ACCESS DATA'!$K2:$K25000, ">="&TODAY()-90)

  9. #9
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Alternative to *long* AVERAGEIFS formula, since all data is on a second Excel sheet?

    Quote Originally Posted by restivo View Post
    =SUM((AVERAGEIFS(S27:S32,S27:S32,">"&TODAY()-90,P27:P32,"license*",Q27:Q32,"mailed*",S27:S32,">"&TODAY()-90))-(AVERAGEIFS(N27:N32,N27:N32,">"&TODAY()-90,P27:P32,"license*",Q27:Q32,"mailed*",S27:S32,">"&TODAY()-90)))
    In this version you are repeating the same criterion twice in the same AVERAGEIFS function, should one of those refer to N27:N32? If so then it might be cleaner to use an "array formula" with AVERAGE function, you don't need to repeat any criteria and it won't have any of the problems you have with AVERAGEIFS, i.e.

    =AVERAGE(IF((N27:N32>TODAY()-90)*(S27:S32>TODAY()-90)*(LEFT(P27:P32,7)="license")*(LEFT(Q27:Q32,6)="mailed"),S27:S32-N27:N32))

    confirmed with CTRL+SHIFT+ENTER

    That will work, no problem, with the data on another sheet
    Audere est facere

  10. #10
    Registered User
    Join Date
    12-11-2012
    Location
    DC
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Alternative to *long* AVERAGEIFS formula, since all data is on a second Excel sheet?

    Yeah I see that now. I think my eyes were getting crossed in trying to find a solution.

    I appreciate your formula, it works great. Thanks a ton!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1