date problem

[kin]

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

 

[markmzz]

Maybe this:

Layout

Date	Value		Result
01/02/2014	450		7,6000
			*
14/02/2014	700		*
23/02/2014	1500		*
03/03/2014	900		*
			*
09/03/2014	350		*
11/03/2014	550		*
**********	*******	**	********

<tbody>
</tbody>

Formula

In D2 - use Ctrl+Shift+Enter to enter the formula

=IFERROR(AVERAGE(SMALL(IF($A$2:$A$9<>"",$A$2:$A$9),ROW(INDIRECT("2:"&COUNT($A$2:$A$9))))-SMALL(IF($A$2:$A$9<>"",$A$2:$A$9),ROW(INDIRECT("1:"&COUNT($A$2:$A$9)-1)))),0)

Markmzz

 

[tusharm]

The formula you were given will work only when the data are sorted in ascending order by date.

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

 

[kin]

thank you it worked and it gives the same number as the second way.

 

[XOR LX]

If Markmzz's solution worked for you, you can also abbreviate it somewhat, and no need for array-entry either:

=AVERAGE(MMULT(SMALL($A$2:$A$9,ROW(INDIRECT("1:"&COUNT($A$2:$A$9)-1))+{0,1}),{-1;1}))

Regards

 

[pgc01]

Hi

Try also:

=(MAX(A2:A9)-MIN(A2:A9))/(COUNT(A2:A9)-1)

 

[mfexcel]

Apparently you are not familiar with array formula.

Maybe you could consider using a helper column
E.g.
in C3
=A2-A1 'which gives you the days between the order
Copy C3 down to the end of your table
Take the average of Column C

or PGC gives you a very nice solution if compounded average works for you.

 

[tusharm]

LOL! So obvious when it's in front of you.

Hi

Try also:

=(MAX(A2:A9)-MIN(A2:A9))/(COUNT(A2:A9)-1)

 

[kin]

hello after solving the first problem I have yet one to arise.
In column A I have names of customers, In column B dates of visit. How can I find an average (of visits) per month or proportion (of increase /decrease from one month to another.)
It looks like
COLUMN A COLUMN B
NAME DATE
B 3/3/2014
G 3/3/2014
H 3/3/2014
KL 4/4/2014
PLO 4/4/2014
ADD 4/4/2014
AWFS 4/4/2014
FG 4/4/2014

I show my case where in a given date someone visits more or less prospects than another day and so on.

thanks

 

[markmzz]

hello after solving the first problem I have yet one to arise.
In column A I have names of customers, In column B dates of visit. How can I find an average (of visits) per month or proportion (of increase /decrease from one month to another.)
It looks like
COLUMN A COLUMN B
NAME DATE
B 3/3/2014
G 3/3/2014
H 3/3/2014
KL 4/4/2014
PLO 4/4/2014
ADD 4/4/2014
AWFS 4/4/2014
FG 4/4/2014

I show my case where in a given date someone visits more or less prospects than another day and so on.

thanks

Hi Kin,

I'm sorry, but I didn't understand what you want.

Could you post more details?

Markmzz

 

[kin]

hello

I d like to ask if you could help me with finding which customers have not been visited for a given time period (e.g a month)
I have the following data look like:

CUSTOMER NAME DATE OF VISIT
JAMES J. 28/1/2014
KATE L. 28/1/2014
JOHN M. 28/1/2014
JAMES J. 25/3/2014
KATE L. 25/3/2014
JOHN M. 25/4/2014

I wish to retrieve the whole rows that there are for each customer. Hence I would know that one customer has not been visited in the last month or more than a month. Thank you