Analysis Question

[EnigmaMatter]

Hello,

I am not sure if the following question is appropriate for Excel Forum, but here goes:

If I have a table of Transaction Dates, Revenues for that Transaction Date, and the Day of the Week, how would I use Excel to determine what my best day of the week is?

[mikeTRON]

You would have to being by defining what the "best" represent. I would assume you are looking to find the day of the week with the highest average revenue, so you should make a pivot table showing day of the week and revenue , and bam done, in under 30 seconds.

Post a sample data set if you would like more help.

[Speshul]

Sounds like a job for a Pivot table, I would have to see a sample of the data though, can you upload a de-sensitized example?

[ben_hensel]

Best how?

Well, probably what I would do is throw the table into a Pivot Table with "Day of the Week" in the Row Labels and then "Revenues for that Transation Date" in the Value field.

That should show by default the total sum of revenues for each day of the week, right? I mean, unless there's something else going on here.

You could do something similar with a table of SUMIF(weekday_range, each_day, revenues_range) with seven different "each_day" ranges, but I think a PivotTable would be faster.

[EnigmaMatter]

Well, I suppose I am wondering how I would do it for either Revenue or # of Transaction.

I have done your method. But what if there was a couple outlier days that made Monday much higher, where as in point of fact, without those outliers, it wouldn't be so spectacular?

[Speshul]

There are days marked as Mondays which are not actually Mondays? What is an outlier day?

[EnigmaMatter]

I figured this out by doing the following -- I believe it may have been suggested.

I made a PivotTable with the Days of the Weeks as Columns, and the Weeks as Rows, and how many/much Transactions/Revenue occurred that day in that week. I then formulaically determined the winning day for each week, and then counted how many times each day of the week was the best of that week. The highest total won.