+ Reply to Thread
Results 1 to 12 of 12

Average if of unique values with conditions

Hybrid View

  1. 01-10-2024, 12:28 PM #1
    aurelien_21
    aurelien_21 is offline
    Registered User
    Join Date
    12-17-2019
    Location
    Leeds, UK
    MS-Off Ver
    365
    Posts
    63

    Question Average if of unique values with conditions

    Hi all,

    I have a table of rail fares for a set of Origin-Destinations. At a high-level, each fare is either an Advance ticket or a Single ticket (but a lot of fares have restrictions on the train operators you can use, so some Advance tickets for a given origin-destination at a given fare price may appear more than once, creating effectively duplicate entries). A given Origin-Destination could also have several different Advance ticket prices (as prices for a train ticket can change as the day of travel approaches).

    I'm after a formula which computes an average fare for each unique Origin-Destination and ticket type (eg. average Advance fare to travel from London Euston to Manchester), but I want to omit duplicates (identical Origin-Destination, ticket type, and fare price) from the calculation.

    Any help would be greatly appreciated, thanks.

    Example with expected answers provided - red text cells are the example of duplicate, identical fares.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-10-2024, 12:42 PM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,181

    Re: Average if of unique values with conditions

    Your averaging is not accurate.

    Try this:

    =LET(u,UNIQUE(E2:E9),HSTACK(u,ROUND(AVERAGEIFS(D2:D9,E2:E9,u),2)))

    AliGW on MS365 Beta Channel (Windows 11) 64 bit

    G
    H
    7
    		 Pontefract - Woodlesford_ADVANCE
    5.10
    8
    		 Leeds - Bradford_ADVANCE
    5.13
    9
    		 Leeds - Bradford_SINGLE
    7.50
    10
    		 Castleford - Pontefract_ADVANCE
    3.50
    Sheet: Sheet1
    Attached Files Attached Files
    Last edited by AliGW; 01-10-2024 at 12:49 PM. Reason: Workbook added.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 01-10-2024, 12:50 PM #3
    aurelien_21
    aurelien_21 is offline
    Registered User
    Join Date
    12-17-2019
    Location
    Leeds, UK
    MS-Off Ver
    365
    Posts
    63

    Re: Average if of unique values with conditions

    Hi Ali,

    Thanks but this is still including both instances of £5.50 Leeds-Bradford Advance tickets, I'd like it to skip over duplicates
    Reply With QuoteRegister To Reply
  4. 01-12-2024, 10:43 PM #4
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    18,815

    Re: Average if of unique values with conditions

    This proposal is employs a helper column (F) which may be moved and/or hidden for aesthetic purposes.
    The formula in column F is: =COUNTIFS(D$2:D2,D2,E$2:E2,E2)
    The formula for column H is: =AVERAGEIFS(D$2:D$9,E$2:E$9,G2,F$2:F$9,1)
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
    Reply With QuoteRegister To Reply
  5. 01-13-2024, 07:57 AM #5
    Gregb11
    Gregb11 is offline
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    3,168

    Re: Average if of unique values with conditions

    I do not know the BYROW formula that well, but I got it to work here. I also put this together piecemeal so I assume someone can reduce it, BUT, it seems to work. With this solution you do not need anything in column E or Column G.

    Clear all cells from G2:H5 and put this formula in cell G2:

    =LET(
    uOD,UNIQUE(A2:A9&" - "&B2:B9&"_"&C2:C9),
    uArray,UNIQUE(HSTACK(A2:A9&B2:B9&C2:C9,D2:D9)),
    HSTACK(uOD,BYROW(UNIQUE(INDEX(uArray,,1)),LAMBDA(x,AVERAGE(FILTER(INDEX(uArray,,2),x=INDEX(uArray,,1)))))))

    see workbook attached.

    Actually, to make the formula a little easier to edit, you can try this:

    =LET(
    rngA,A2:A9,rngB,B2:B9,rngC,C2:C9,rngD,D2:D9,
    uOD,UNIQUE(rngA&" - "&rngB&"_"&rngC),
    uArray,UNIQUE(HSTACK(rngA&rngB&rngC,rngD)),
    HSTACK(uOD,BYROW(UNIQUE(INDEX(uArray,,1)),LAMBDA(x,AVERAGE(FILTER(INDEX(uArray,,2),x=INDEX(uArray,,1)))))))

    This way if the range of your data-set changes, you just need to change them once.
    Attached Files Attached Files
    Last edited by Gregb11; 01-13-2024 at 08:09 AM.
    Reply With QuoteRegister To Reply
  6. 01-13-2024, 08:27 AM #6
    HansDouwe
    HansDouwe is offline
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,466

    Re: Average if of unique values with conditions

    The whole table in one go:

    Please try:
    Formula: copy to clipboard 
    =LET(u,UNIQUE(E2:E9),HSTACK(u,MAP(u,LAMBDA(x,AVERAGE(UNIQUE(FILTER(D2:D9,E2:E9=x)))))))
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 01-13-2024, 10:52 AM #7
    Gregb11
    Gregb11 is offline
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    3,168

    Re: Average if of unique values with conditions

    See, I knew there'd be a better way :-)
    Nice job Hans.
    Reply With QuoteRegister To Reply
  8. 01-14-2024, 04:06 AM #8
    Czeslaw
    Czeslaw is offline
    Forum Expert
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    1,404

    Re: Average if of unique values with conditions

    Power Query
    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Origin", type text}, {"Destination", type text}, {"Ticket", type text}, {"Fare", type number}, {"OD_Ticket", type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type"),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"OD_Ticket"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Columns",{"Origin", "Destination", "Ticket"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Merged"),
    #"Grouped Rows" = Table.Group(#"Merged Columns", {"Merged"}, {{"Average", each List.Average([Fare]), type number}})
in
    #"Grouped Rows"
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 01-17-2024, 06:11 AM #9
    aurelien_21
    aurelien_21 is offline
    Registered User
    Join Date
    12-17-2019
    Location
    Leeds, UK
    MS-Off Ver
    365
    Posts
    63

    Re: Average if of unique values with conditions

    Thanks Jete, Greg, Hans and Czeslaw, this is all great stuff. Really appreciate all your suggestions. In the interest of simplicity (and knowing nothing about Power Query), Hans' solution looks cleanest, though Jete's is easiest to understand.
    Reply With QuoteRegister To Reply
  10. 01-17-2024, 06:12 AM #10
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,181

    Re: Average if of unique values with conditions

    If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
    Reply With QuoteRegister To Reply
  11. 01-17-2024, 01:46 PM #11
    HansDouwe
    HansDouwe is offline
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,466

    Re: Average if of unique values with conditions

    You are Welcome!

    Thannks for the feedback and rep .

    Glad to have helped. .
    Reply With QuoteRegister To Reply
  12. 01-17-2024, 02:42 PM #12
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    18,815

    Re: Average if of unique values with conditions

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Unique values from array then average adjacent column values
    By tomisthereason in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-24-2021, 09:12 AM
  2. Count unique values with criteria and blanks and an average of maximum consecutive values
    By mrsak87 in forum Excel General
    Replies: 2
    Last Post: 07-30-2018, 02:21 PM
  3. [SOLVED] AVERAGE values if conditions met
    By bjnockle in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-10-2017, 02:06 AM
  4. Count unique values in one column based on conditions and unique values in another column
    By margaretanne in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-19-2015, 10:22 AM
  5. [SOLVED] average for unique values
    By ammartino44 in forum Excel General
    Replies: 7
    Last Post: 09-16-2014, 05:34 PM
  6. [SOLVED] Find average values with conditions
    By bigband1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-14-2012, 05:28 PM
  7. Sum Unique Values with Conditions
    By tdemana in forum Excel General
    Replies: 10
    Last Post: 04-12-2011, 03:40 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1