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
556tim - Jan 31, 2016 at 04:59 PM
Related:
- Excel Query
- Excel date format dd.mm.yyyy - Guide
- Excel free download - Download - Spreadsheets
- Number to words in excel - Guide
- Excel marksheet - Guide
- How to take screenshot in excel - Guide
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
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()))
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
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
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
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.
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.
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!
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!
