counting days

[rantnrave]

I have a spreadsheet that in column A I input a date when we complete "X" kilometers in column B
What I would like to do is count the days and provide an expected number of days to complete when I have a total Km we have days where no work is achieved so I can't deduct first date from last as that would be give a false average

 

[rantnrave]

Now I feel even worse
The count(a:a) provides a total number of entries into that column, as we can produce quite a few lines in a day its adding all the same dates. what I need is a way to recognise just how many different days worked
the formula you kindly provided gives me a 9.77 days to completion, and a completion date of 28/7/14
once again I appreciate your time

 

[BrianMH]

Here is a solution that should work for you. Specifically to find the number of unique days this is the formula being used. It is an array formula so enter it with ctrl+shift+enter

=SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""),IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""))>0,1))

Sheet1

*	A	B	C	D	E	F	G
1	Worked Days	Kilometres	*	Goal	Remaining	Average per Day	Estimated Date Goal Met
2	20/02/2014	25	*	1000	550	150	28/02/2014
3	22/02/2014	250	*	*	*	*	*
4	22/02/2014	25	*	*	*	*	*
5	24/02/2014	100	*	*	*	*	*
6	24/02/2014	50	*	*	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:88px;"><col style="width:75px;"><col style="width:91px;"><col style="width:35px;"><col style="width:73px;"><col style="width:109px;"><col style="width:163px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula E2 =D2-SUM(B2:B10) F2 {=SUM(B2:B10)/SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""), IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""))>0,1))} G2 =MAX(A:A)+ROUND(E2/F2,0) <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[rantnrave]

ok now you have gone way over my head I have produced the little example and put in the formulas but these long formula do I remove the two outer brackets before Cntrl+Shft+enter or not either way It just sits ther looking at me and remains a formula I will keep trying as I appreciate you put in a lot of time already and I hate to be such a dunce
Les

 

[BrianMH]

Where the brackets exist you enter them without the brackets (but do include the = sign) but instead of hitting enter you hit ctrl+shift+enter.

 

[rantnrave]

Ok your brill I have it !!

many thanks for your help

 

[BrianMH]

Glad we got there in the end! Good luck with your future projects!

 

[barry houdini]

Another way to find the number of different dates in A2:A10

=SUM(IF(FREQUENCY(A2:A10,A2:A10),1))

confirmed with CTRL+SHIFT+ENTER

Note: that works assuming A2:A10 has simple dates or blanks - if you have times with the dates then this will count the number of different days

=SUM(IF(FREQUENCY(A2:A10,INT(A2:A10)),1))

 

[BrianMH]

Nice one Barry. I used the unique count solution posted on Microsoft's own help site. Considering how much more efficient and short your solution is I wonder why they didn't post that!

rantnRave to clarify the formula in F2 would change to:

=SUM(B2:B10)/SUM(IF(FREQUENCY(A2:A10,A2:A10),1))

 

[rantnrave]

Cheers Brian that also works my thanks to you and barry for his input

 

[barry houdini]

...I used the unique count solution posted on Microsoft's own help site....

Hello Brian,

That approach is more general, it works with any type of data, including text, numbers or mixed. The solution I suggested will only work with numbers (taking into account that dates are simply formatted numbers in excel).