+ Reply to Thread
Results 1 to 8 of 8

Need a MAX function for sales on a variable date column

  1. #1
    Registered User
    Join Date
    01-15-2018
    Location
    Texas
    MS-Off Ver
    2013
    Posts
    11

    Need a MAX function for sales on a variable date column

    I am working with a table of sales numbers by date and by employee. The date is set up horizontally for the year and the employees are set up vertically. On a separate sheet I have a cell where I pick the date. I want the equations to reference this cell and change the top salesperson and the number of sales they have according to the date I select. I have only gotten it to work for a single column, not for variable columns dependent on the date. I have used INDEX(MATCH(MATCH functions with a MAX function, that's what has worked so far for Jan 1 but not for Jan 2 or later.

    A MAX function that also takes into account a lookup for a specific column would be ideal. Any combinations I can use?

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

    Re: Need a MAX function for sales on a variable date column

    Say you have salespeople in A2:A10 and dates in B1:F1 and data in B2:F10,then for a specific date in H2 you can get the max for that column with this formula in I2

    =MAX(INDEX(B2:F10,0,MATCH(H2,B1:F1,0)))

    Then the first salesperson with the max sales on that date with this

    =INDEX(A2:A10,MATCH(I2,INDEX(B2:F10,0,MATCH(H2,B1:F1,0)),0))
    Audere est facere

  3. #3
    Registered User
    Join Date
    01-15-2018
    Location
    Texas
    MS-Off Ver
    2013
    Posts
    11

    Re: Need a MAX function for sales on a variable date column

    Amazing, that works perfectly. I can infer then that substituting 0 for the row criteria in the INDEX function somehow nullifies that argument? Never thought to do that, or put an INDEX function within an INDEX function.
    Also, I'll need a weekly and monthly top sales per employee, can the max function do that as well?

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

    Re: Need a MAX function for sales on a variable date column

    When you use zero as the ROW or COLUMN argument in INDEX you get the whole column or row, so when you use

    =INDEX(B2:F10,0,MATCH(H2,B1:F1,0))

    If MATCH returns 3, for example, the INDEX function then returns the whole of the 3rd column of B2:F10, i.e. D2:D10 (of course you need to feed that range to a function that can handle a range, like MAX)

    For MAX for a specific date range and employee there are several possible approaches, one is to use an array formula like this:

    =MAX(IF((A2:A10="John")*(B1:Z1>=H2)*(B1:Z1<H2+7),B2:Z10))

    confirm with CTRL+SHIFT+ENTER

    That will get the max for John in the week beginning with date in H2

  5. #5
    Registered User
    Join Date
    01-15-2018
    Location
    Texas
    MS-Off Ver
    2013
    Posts
    11

    Re: Need a MAX function for sales on a variable date column

    I never knew that about the 0's. Unfortunately I just don't understand the equations enough to know how to use them on my own. The week and month equations need to function the same as the daily equations, where it finds and displays the top sale for the date (ending on the chosen date rather than just on the date) and then the name of the person who made the sale. I'm almost understanding how to fit equations inside each other.

    I tried this equation:
    =MAX(INDEX('Yearly Input - AP'!C4:NN26,0,IF(('Yearly Input - AP'!C3:NN3>=(D4-7))*('Yearly Input - AP'!C3:NN3<(D4+1)),MATCH(D4,'Yearly Input - AP'!C3:NN3,0),0)))
    But it's only returning the value of the first equation, or the max per day.
    Last edited by RankSinatra; 01-18-2018 at 08:42 PM.

  6. #6
    Registered User
    Join Date
    01-15-2018
    Location
    Texas
    MS-Off Ver
    2013
    Posts
    11

    Re: Need a MAX function for sales on a variable date column

    BUMP
    Just need to know how to calculate the max of each row's sums within a variable set of columns depending on last day of the week.

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

    Re: Need a MAX function for sales on a variable date column

    I'm assuming that your date row contains all dates in the range, without gaps, so the following simplified example is based on that

    Let's assume names in A2:A11, dates in B1:N1 with sales values in B2:N11

    Now the easiest way to get the maximum sales value for a specific date range, and associated Salesperson, is to use a helper column set up like this:

    Start date in B13, end date in B14 (in Blue) .....now in helper column (column P) you can use the following formula in P2 copied down to get the Sales total for each salesperson within that date range

    =SUMIFS(B2:N2,B$1:N$1,">="&B$13,B$1:N$1,"<="&B$14)

    Now for maximum sales within that range you can use this formula:

    =MAX(P2:P11)

    and for associated salesperson

    =INDEX(A2:A11,MATCH(B15,P2:P11,0))

    Without the helper column you can use these array formulas to get the same results

    =MAX(SUBTOTAL(9,OFFSET(B2:N11,ROW(B2:N11)-MIN(ROW(B2:N11)),B13-B1,1,B14-B13+1)))

    and

    =INDEX(A2:A11,MATCH(C15,SUBTOTAL(9,OFFSET(B2:N11,ROW(B2:N11)-MIN(ROW(B2:N11)),B13-B1,1,B14-B13+1)),0))

    both confirmed with CTRL+SHIFT+ENTER

    See attached

    Press F9 to generate new random example
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    01-15-2018
    Location
    Texas
    MS-Off Ver
    2013
    Posts
    11

    Re: Need a MAX function for sales on a variable date column

    Thank you so much. I know that was complicated to set up but it helped enormously. I'll be back on this forum soon with a vba question.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Need 2 formula regarding daily sales goals & conditional date function
    By THOMHJ in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-12-2016, 10:11 PM
  2. Formula to find sales for fixed event at a certain sales date
    By iantix in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-27-2015, 07:56 AM
  3. Replies: 1
    Last Post: 05-31-2014, 01:38 AM
  4. [SOLVED] SUM column based on two dates, SUM function begins at first date which is a variable
    By moxiepilot in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-11-2013, 06:36 AM
  5. Replies: 7
    Last Post: 07-23-2013, 07:56 AM
  6. [SOLVED] Sales report from sales data sheet if i select the date
    By loki7431 in forum Excel General
    Replies: 4
    Last Post: 02-06-2013, 09:43 PM
  7. Sumifs function based on Date range and sales value
    By Shihab in forum Excel General
    Replies: 7
    Last Post: 02-05-2012, 11:31 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1