date problem

kin

Well-known Member
Joined
Jun 26, 2007
Messages
648
hello

I d like to ask you if you could help me find a solution. I have two columns one called "date of order" and another "value of order" .The rows in These 2 columns are not always filled. Only when there is an order there is a date too. Now I want to find how many days on average it takes me until I have a new order. What is the date difference between each order and then the average (in days). How can I find it?Thanks
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Could you post sample data showing how the answer relates to the original data. That should help us get a clearer picture
 
Upvote 0
Could you post sample data showing how the answer relates to the original data. That should help us get a clearer picture

1/2/2014 450
6/2/2014 300
14/2/2014 700
23/2/2014 1500
3/3/2014 900
5/3/2014 100
9/3/2014 350
11/3/2014 550

if we take the above dates of the first column, on average how long (days) does it take for the next order to occur?
 
Upvote 0
Perhaps this array formula**, based on data in A1:A8:

=AVERAGE(IF(A2:A9<>"",A2:A9-A1:A8))

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
</SPAN></SPAN>
 
Upvote 0
This formula =AVERAGE(IF(G2:G35<>"";G2:G35-G1:G34)) brings #VALUE! error
 
Upvote 0
Did you correctly follow the instructions I gave re array formulas?

Regards
 
Upvote 0
My instructions don't say simply "with CTRL+SHIFT". Please re-read and ensure you have done it correctly.

Regards
 
Upvote 0
An alternative to using a formula to get a single number is to calculate the time since the previous order. This will give you multiple values which you can use for a variety of purposes including checking for any possible trend.

Suppose your data are in A1:B{n} with row 1 as the header row. Then, in C1 enter the literal "Time Since Last Order" and in C2 enter the formula =A2-A1. If your data are in a table (as Excel understands it), Excel will automatically copy C2 down the table. If not, copy C2 as far down C as you have data in A:B.

Now, you can plot C vs. A to see if there is a trend over time. Or if there is a pattern based on order amount. Of course, you cancalculate the AVERAGE of C to get the single number you want. And, if you have additional information such as customerID and or SalesPerson you can do even more interesting and useful analysis.
1/2/2014 450
6/2/2014 300
14/2/2014 700
23/2/2014 1500
3/3/2014 900
5/3/2014 100
9/3/2014 350
11/3/2014 550

if we take the above dates of the first column, on average how long (days) does it take for the next order to occur?
 
Upvote 0
I held CTRL+SHIFT and then pressed enter but still the same error appears. Although the other method gives me an average which I think it is true once I sorted them in ascending order. Still I d like a formula to be used
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top