# Need a MAX function for sales on a variable date column

1. ## 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?  Register To Reply

2. ## 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))  Register To Reply

3. ## 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?  Register To Reply

4. ## 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  Register To Reply

5. ## 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.  Register To Reply

6. ## 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.  Register To Reply

7. ## 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  Register To Reply

8. ## 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.  Register To Reply

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