Display sales data in eye catching way

[TimClayton]

Hi all



I have extracted sales data into 2 columns as below:

15/06/2019 12:55 £15.00
15/06/2019 13:03 £105.00
16/06/2019 10:56 £185.00
etc. (about 800 sales over 6 months)

I'd like to display this data (can be several charts/tables) to show the following information:


Busiest day of the week (no. of sales)
Busiest day of the week (highest turnover)
Busiest time of day (no. of sales)
Busiest time of day (highest turnover)



There's probably several ways of doing this, but I'm looking for the one that's most visually pleasing/easy to read.

Just to clarify, I'd like the DAY of the week from the date, not the busiest dates. i.e. 12-1pm on Wednesday was the busiest day of week 1, 3-4pm on Tuesday of week 2 etc... Thursday is the busiest day of the week on average in the last 6 months...

Basically as much information as possible in an easy to read format.


Thanks in advance!

[Olly]

So you want someone to design and build an entire dashboard for you??

[BillyD21]

So you want someone to design and build an entire dashboard for you??

Nope, a chart, table or something simple will do.

I don't know how to convert the data into a useful format. But, seeing as I found this Excel help forum, I thought I'd ask for help using Excel...

[Pepe Le Mokko]

@BillyD21
Hi and welcome
what does your answer have to do with a thread started by another member?

[BillyD21]

Sorry, I'm actually the original poster, but I'm logged into the wrong account. Whoops!

[TimClayton]

Sorry, I'm actually the original poster, but I'm logged into the wrong account. Whoops!

Logged in with original account to confirm!

[Olly]

I'd like to display this data (can be several charts/tables) to show the following information:

Busiest day of the week (no. of sales)
Busiest day of the week (highest turnover)
Busiest time of day (no. of sales)
Busiest time of day (highest turnover)

...

Basically as much information as possible in an easy to read format.

Nope, a chart, table or something simple will do.

There's a big difference between helping with a specific problem you're having, and doing all your work for you...

What have you tried so far? What problem(s) did you encounter?

[TimClayton]

Olly


So I managed to separate the dates and times and used custom formats to get 4 columns: 03/01 | Thursday | 9:43 AM | £925.00

I converted the range to a table, I don't know why, I just thought it might help.

From here I'm stuck. I want a section next to it with some sort of formula that does "count the number of transactions for each day of the week and display the most common one here"

Then I'd want one to do something like "count the total transaction amounts for each day of the week and display the highest earning one here"

Again, because I have no idea how to do it, I don't know if it's a lot of work or not. There might be some pivot table or something that does it all for you, but I don't know.


Thanks

[TimClayton]

SO I think I made some progress

Capture.PNG

Formulas next to days are =SUM(IF($B$2:$B$1000=G2, $D$2:$D$1000, 0))

Formulas next to hours are =COUNTIFS($C$2:$C$1000,">="&G9,$C$2:$C$1000,"<"&H9)

This gives me something pretty close to what I wanted. It's nor particularly attractive, but it works!

[TimClayton]

I'm not sure if anyone is still following this but... I'd like to add some more functionality.

I have the above, which shows total sales for certain days of the week and certain times of day. Now what I would like to do is add certain times of certain days.

I'd like to see total number of sales/value of sales on a Monday between 9-10am. Is this possible?

Thanks

[Sam Capricci]

I think the problem Tim is that you won't find many on here who want to create a workbook from scratch guessing what you have and how to display it. Maybe if you posted a sample workbook with sample data and what kind of things you'd like to see you might get some help.

[Pepe Le Mokko]

Administrative Note:

We don't expect you to bump threads more than once in 24 hours - it has been only 6-7 hours since you posted. Please remember that those who help here do so voluntarily and of their own goodwill. They live in many different time zones and many will be out at work right now if they are not asleep on the other side of the globe. This is not a paid 'service': members will help when they are ready and able to do so, and not 'on demand'. Please do not try to put pressure of time on anyone here: if you get the help you need today, that's fine, but you really should not count on it. Thanks for your understanding and patience.

[Sam Capricci]

Tim, what Pepe wrote is of course true but you can attach a workbook and that is not seen as bumping the post or impatience.