# Excel Query

Closed
tim556 Posts 2 Registration date Sunday January 31, 2016 Status Member Last seen January 31, 2016 - Jan 31, 2016 at 12:32 PM
556tim - Jan 31, 2016 at 04:59 PM
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.

Related:

## 3 responses

RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016 26
Jan 31, 2016 at 12:43 PM
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()))
tim556 Posts 2 Registration date Sunday January 31, 2016 Status Member Last seen January 31, 2016
Jan 31, 2016 at 12:51 PM
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
RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016 26
Jan 31, 2016 at 04:10 PM
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.

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!