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
Home icon Home icon Home icon Email icon RSS icon
  • Finding the date that has the highest revenue

    Posted on WSElliotp 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

    This topic contains 8 replies, has 3 voices, and was last updated by  Lugh 2 years, 6 months ago.

    • Author
      Posts
    • #508418 Reply

      WSElliotp
      AskWoody Lounger

      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!

    • #1593587 Reply

      zeddy
      AskWoody_MVP

      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

    • #1593588 Reply

      WSElliotp
      AskWoody Lounger

      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!)

      • #1593610 Reply

        zeddy
        AskWoody_MVP

        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:
    • #1593947 Reply

      Lugh
      AskWoody_MVP

      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

    • #1593957 Reply

      WSElliotp
      AskWoody Lounger

      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?!

      Any advice be much appreciated!

      Attachments:
      • #1594106 Reply

        Lugh
        AskWoody_MVP

        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

    • #1593961 Reply

      WSElliotp
      AskWoody Lounger

      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!

      • #1594036 Reply

        zeddy
        AskWoody_MVP

        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

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: Finding the date that has the highest revenue

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.