News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
• ## Finding the date that has the highest revenue

Posted on Comment on the AskWoody Lounge

Home Forums AskWoody support Productivity software by function MS Excel and spreadsheet help Finding the date that has the highest revenue

• Author
Posts
WSElliotp

Hey guys,

So I’m looking for a formula that calculates the date in which we took the most revenue.

Currently my data looks like:

Column B = List of dates when a job was booked
Column F = The revenue for the job

The other columns have various other data but is not required here.

Ie
Column B l Column F
Date l Revenue
1/03/2017 l £100
1/03/2017 l £150
1/03/2017 l £150
2/03/2017 l £500
2/03/2017 l £350
3/03/2017 l £100
3/03/2017 l £75
3/03/2017 l £75
3/03/2017 l £100

Here, the date required would be 2/03/2017 as £850 in revenue.

I’ve been struggling for a while to get the formula that can work this out, any suggestions would be much appreciated!

zeddy

Hi

Welcome to the Lounge as a new poster.

If your column [F] contains values, and column contains dates, then you could put this formula into a cell, for example, cell [G2]
[G2] =INDEX(B:B,MATCH(MAX(F:F),F:F,0))
..you will need to format the cell [G2] as a date.

zeddy

WSElliotp

Hi Zeddy,

Thanks for the welcome and the fast response!

I believe I may not have explained clearly enough, sorry!

I have already tried this formula but unfortunately this just returns the date that matches the maximum figure.
Ie it would show 02/03/2017 but would do so because £500 is the highest number, not because it’s totaled the two values in that date.

I need the formula to essentially cross check every date and total up the values for each date and show the date with the highest combined values (hopefully that makes sense!)

zeddy

Hi Elliot

oops! I was in a rush to Train station.

So, see attached example file.
I just added a column with a SUMIF formula to total the sales for each date.
I then used the MAX and INDEX/MATCH formulas to return the date that had the highest sales as you asked for.

zeddy

###### Attachments:
Lugh

A variation which might be useful for someone else:

I do something similar for a workbook of monthly overview sheets for my business. Each sheet in that book is a pivot table based on data in other workbooks or Access tables.

For Elliot’s example, the pivot would summarize revenue by date [one line with total revenue for each different date], and the revenue column would be sorted from largest to smallest—has the advantage of showing more than only the highest revenue date, and also not hiding more dates which tied for top spot.

I’m fairly sure you can set a pivot, or an individual column, to display Max or Top 10 results, if you don’t need the whole list.

Lugh.
~
Alienware Aurora R6; Win10 Home x64 1803; Office 365 x32
i7-7700; GeForce GTX 1060; 16GB DDR4 2400; 1TB SSD, 256GB SSD, 4TB HD

WSElliotp

Hey guys,

Thanks to both for the replies.

Lugh, unfortunately I’m not too sure how your solution works there but am sure it will apply to someone who has a better understanding than myself!

Zeddy, I’ve put that setup into practice but I’m actually using Google Sheets (Docs) rather than Excel. On Excel that setup works fine, but on Google it doesn’t work correctly and I cannot work out why?!

Below is a screenshot showing the issue:

47000-example

I’ve copied the formulas below the relevant cells so you can see what I’ve entered. (the sum if one is based on C9 and obviously updates based on the cell its in)

As you can see, the index is looking up the max (850) but instead of displaying 2/03 it’s showing 3/03 and I cannot fathom why?!

###### Attachments:
Lugh

Good job solving it with Zeddy’s always excellent help 🙂

Lugh, unfortunately I’m not too sure how your solution works

If you do regular spreadsheet work, I strongly recommend you have a look at “pivot tables”, which have been in Google Sheets for a few years now. There are loads of intros and tutorials on the web, and it’s a very useful feature of spreadsheets.

Lugh.
~
Alienware Aurora R6; Win10 Home x64 1803; Office 365 x32
i7-7700; GeForce GTX 1060; 16GB DDR4 2400; 1TB SSD, 256GB SSD, 4TB HD

WSElliotp

Solved!

Played around with the index slightly and =index(A:A,MATCH(F1,C:CO,0),1) works!

Thanks for the support and help guys!

zeddy

Hi Elliot

..I always assume we are using Excel unless told otherwise.
..glad you discovered the extra formula bits needed for the Google version.
..I left them out in the Excel version to save typing!

zeddy