Excel Query [Closed]

Report
Posts
2
Registration date
Sunday January 31, 2016
Status
Member
Last seen
January 31, 2016
-
 556tim -
Hi

I looking for a formula that will calculate an average order spend by day without having to do this manually(1, 2, 3 ).

So for example it takes the running total £10,000 and averages against how many days of the month (say day 10) have passed. So answer would be £1000.

Please help!

3 replies

Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
24
Is this what you are looking for?

Where A1 is the monetary value and A2 is the Date
=A1/DAY(A5)

or
=A1/DAY(TODAY()))
Posts
2
Registration date
Sunday January 31, 2016
Status
Member
Last seen
January 31, 2016

Hi Ray,

Not exactly, sorry.

What i need is a formula that essentially recognizes the date or days in the month that have passed so lets say Jan 31 days and then calculates and average. So say i have done £25k in sales and its day 8 whats the average order spend £3125). But on each day I open the file lets say at day 4, 13 and 25, the cell takes the date and divides the sales total by the relevant day in the month.

Does that make sense?

Tim
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
24
yes.
That's what the formulas I gave actually do.

The DAY function returns the DAY of the month from the date in the cell.
So if A2=31/1/2016 then DAY will return 31.
Therefore the formula =A1/DAY(A2) (I actually made a typo in the formula before A5 should have been A2) is translated by Excel as 25000/31 which gives you your answer.

If you just want to use the current date then =A1/DAY(TODAY())) will give you what you need and will always give the answer no matter what date you open it.

Of course this will only work until the month changes.

So if your sales period spans month ends then we'll need a way to determine how many days pave passed in the period in which case :
CurrentDate-PeriodStartDate will give the number of days passed in that period.

So, =A1/CurrentDate-PeriodStartDate should give your answer.

If these scenarios are not what you mean then please give more explanations along with examples or sample spreadsheets.
2126 £1,225.90 £1,306.35
2127 £1,018.50 £1,034.50
2128 £2,457.49 £2,492.49
2129 £489.60 £503.50
2130 £686.05 £695.05
£95,933.87 £97,852.55


Stage 1 £85,000.00
Stage 2 £100,000.00
Average Order Total £968.84
Daily Target £2,741.94
Daily Actual £3,156.53
Over / Under Target £12,852.55


This is a snap shot of the end of the month. But lets imagine but i make further sales and there are 10 days left in the month. On day 20 i want to know what average is for those 20 days. Note no date is entered and the ref is just a number, . As you can see i have the daily actual (average) set up and the cell currently takes the £97,852 and divides by 31 days (manual entry). I have tried the formulas and cant get it to work!