+ Reply to Thread
Results 1 to 5 of 5

Formula to tell me which day tends to have the highest number

  1. 01-21-2015, 01:28 PM #1
    GigaFluxx
    GigaFluxx is offline
    Registered User
    Join Date
    11-19-2014
    Location
    Toronto, Ontario
    MS-Off Ver
    Office 2010
    Posts
    18

    Question Formula to tell me which day tends to have the highest number

    Hi there, so I have some data that looks similar to this
    range.jpg
    Looking at it, for week 1 and 2, Friday has the highest number. Week 3, 4, and 5 are Monday, Sunday and Tuesday. This means the number tends to be higher on Fridays even just by a little bit. Is there a formula than can extrapolate this information? To give you an idea, the data I have is 500k+ lines

    I also have a follow up question to a previous thread I posted. User mcmahobt was kind enough to provide me with this formula "=INDEX(A1:A20,MATCH(MAX(B1:B20),B1:B20,0))" to add up the total numbers for all the days and tell me which day was the biggest. I was wondering if there is a way to edit it and have it also tell me 2nd and 3rd place?
    Reply With QuoteRegister To Reply
  2. 01-21-2015, 01:38 PM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053

    Re: Formula to tell me which day tends to have the highest number

    Take a look at this. I don't think it's entirely what you want - but maybe it's a start
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  3. 01-21-2015, 01:46 PM #3
    GigaFluxx
    GigaFluxx is offline
    Registered User
    Join Date
    11-19-2014
    Location
    Toronto, Ontario
    MS-Off Ver
    Office 2010
    Posts
    18

    Re: Formula to tell me which day tends to have the highest number

    Thats more of that mcmahobt gave me, where it added all the numbers together. I guess what I'm looking for is that since friday is the highest day for week 1, friday gets a point. Week 2, friday gets a point. Week 3, Monday... etc. Then I want to look at who has the highest points and report that name, but all in a single cell if possible.
    Reply With QuoteRegister To Reply
  4. 01-21-2015, 02:07 PM #4
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Formula to tell me which day tends to have the highest number

    Maybe ...

    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    1
    Week
    Day
    Value
    Rank
    2
    		 Week1 Sun
    16
    1
    		 D2: =SUMPRODUCT(($A$2:$A$29=A2) * ($C$2:$C$29 > C2)) + 1
    3
    		 Week1 Mon
    8
    4
    Day
    Avg Rank
    4
    		 Week1 Tue
    8
    4
    		 Sun
    3.00
    		 G4: =AVERAGEIF($B$2:$B$29, F4, $D$2:$D$29)
    5
    		 Week1 Wed
    6
    6
    		 Mon
    3.00
    6
    		 Week1 Thu
    12
    3
    		 Tue
    4.00
    7
    		 Week1 Fri
    16
    1
    		 Wed
    4.25
    8
    		 Week1 Sat
    5
    7
    		 Thu
    4.00
    9
    		 Week2 Sun
    25
    2
    		 Fri
    2.50
    10
    		 Week2 Mon
    22
    3
    		 Sat
    5.00
    11
    		 Week2 Tue
    22
    3
    12
    		 Week2 Wed
    15
    7
    13
    		 Week2 Thu
    22
    3
    14
    		 Week2 Fri
    17
    6
    15
    		 Week2 Sat
    28
    1
    16
    		 Week3 Sun
    15
    4
    17
    		 Week3 Mon
    19
    1
    18
    		 Week3 Tue
    10
    6
    19
    		 Week3 Wed
    17
    2
    20
    		 Week3 Thu
    14
    5
    21
    		 Week3 Fri
    17
    2
    22
    		 Week3 Sat
    5
    7
    23
    		 Week4 Sun
    5
    5
    24
    		 Week4 Mon
    13
    4
    25
    		 Week4 Tue
    19
    3
    26
    		 Week4 Wed
    23
    2
    27
    		 Week4 Thu
    5
    5
    28
    		 Week4 Fri
    30
    1
    29
    		 Week4 Sat
    5
    5
    Last edited by shg; 01-21-2015 at 02:10 PM.
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  5. 01-21-2015, 02:08 PM #5
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Formula to tell me which day tends to have the highest number

    duplicate post ...
    Last edited by shg; 01-21-2015 at 02:11 PM.
    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. Formula for Highest Score from number but display Name
    By wariodemon in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-12-2013, 08:30 PM
  2. Formula finding highest number out of five and deleting that row
    By taichi56 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-12-2012, 10:35 AM
  3. VBA code to find highest and 2nd highest number based in criteria
    By Michael007 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-25-2011, 08:38 AM
  4. highest number formula
    By maestro_uk in forum Excel General
    Replies: 4
    Last Post: 08-28-2007, 10:36 AM
  5. [SOLVED] 2 rows, highest No in row 1, then highest number in row 2 relating to that column, possible duplicates
    By John in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-10-2006, 11:40 PM

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