Sales figures by day from multiple tabs averaged in to a summary?

Question

Hi there,&nbsp;I analyse ticket sales data and want to be able to see, from historical sales-by-day data, the average % of total sales on any given number of days prior to the show.&nbsp;&nbsp;I've put the sales data into one workbook as different sheets, formatted identically. Say column I is the 'days prior' and column J is the % of total sales. E.g. I150 (value 130) corresponds to J150 (value 5.0%).&nbsp;&nbsp;On a summary page I want to see the avg % of total ticket sales (averaged from every sheet) for that specific 'day prior'.&nbsp;&nbsp;Later I'll want to see by year, by location, etc. but for now that's where I'm stuck.&nbsp;&nbsp;Advice appreciated.&nbsp;

riny_van_eekelen · Answer

kf233&nbsp;What exactly and why are you putting data in separate sheets?&nbsp;Data analysis becomes much easier if you collect all data in one data table/list. Can you share a file with some example data, by posting a link giving full access to it on OneDrive or similar? Remove any private and confidential information though and specify the results you want to achieve.

kf233 · Answer

Thanks. The data exports from my ticketing system by event, which is why it's in separate sheets. The system doesn't allow me to pull daily sales reports for all events combined.

Link: https://1drv.ms/x/s!AulXG6vfEpU6_BjjWYnBYSRK3sqY?e=oXGVCH

Overview tab gives an idea of the results I want to achieve. There is likely a cleaner and more efficient way to present the data.

We hold the same events every year. I want to be able to look at my spreadsheet overview and say "we are X days out from the event. From previous years' sales data we've confidently sold X% of the total ticket sales, which means we can expect X total sales" to help with the event planning and budgeting.

riny_van_eekelen · Answer

kf233&nbsp;OK, I understand. Assuming that you can add two columns to the event tables (Event number and Year), you can easily combine all events in one larger table. Automate it with Power Query or Copy/Paste manually if there aren't that many events. Whatever is easier for you.Anyhow, you'll create a table like in the attached file which you can then analyze with a pivot table. Note that I just copied Event 1 three times into a new table numbering them 1, 2 and 3.&nbsp;

kf233 · Answer

Riny_van_Eekelen&nbsp;that is fantastic, thank you!!

peterbartholomew1 · Answer

kf233&nbsp;If you prefer a formula to copy/paste, something like= VSTACK(
    EXPAND("",30,2,""), 
    HSTACK(Event1tbl[% Sold], Event2tbl[% Sold])
  )would bring across a couple of columns and move them down to align the days out.

Forum Discussion

Sales figures by day from multiple tabs averaged in to a summary?

5 Replies

Resources