It depends. See the example below:
17/02/06 7.9 (@ 1300 hrs)
17/02/06 5.4 (@ 1500 hrs)
17/02/06 9.9 (@ 1700 hrs)
18/02/06 6.7 (@ 1300 hrs)
18/02/06 8.0 (@ 1500 hrs)
18/02/06 7.1 (@ 1700 hrs)
20/02/06 4.4 (@ 1300 hrs)
20/02/06 6.7 (@ 1450 hrs)
20/02/06 5.2 (@ 1800 hrs)
Can this happen?
And if it does, is this 3 or 4 days?
If you consider this to be 4 days, and those dates are located at A1 to
A9, then this formula will give you the correct result:
=MAX(A1:A9)-MIN(A1:A9)+1
That is if those dates are formatted as dates.
If not, I have another solution:
Insert another column somewhere (which you can hide later), like this,for
example:
0 17/02/06 7.9 (@ 1300 hrs)
0 17/02/06 5.4 (@ 1500 hrs)
1 17/02/06 9.9 (@ 1700 hrs)
0 18/02/06 6.7 (@ 1300 hrs)
0 18/02/06 8.0 (@ 1500 hrs)
1 18/02/06 7.1 (@ 1700 hrs)
0 20/02/06 4.4 (@ 1300 hrs)
0 20/02/06 6.7 (@ 1450 hrs)
1 20/02/06 5.2 (@ 1800 hrs)
Now dates are in column B, and if the first line in this example is row 1,
then A1 looks like this:
=if(B2=B1;0;1)
Now you only have to sum the a column.
You can also, if you don't want to insert another column, write your own
cell function (as a macro).
Maybe there are other ways to do this but this is all I know.
Johnny
Den 2006-02-19 08:22:11 skrev James Elliott
<[EMAIL PROTECTED]>:
I have a Calc spreadsheet (OOo 2.0; Win XP) which has a column of dates
and a column of data readings. Sometimes there are three readings on
the same day. This is what it looks like:
17/02/06 7.9 (@ 1300 hrs)
17/02/06 5.4 (@ 1500 hrs)
17/02/06 9.9 (@ 1700 hrs)
18/02/06 6.7 (@ 1300 hrs)
18/02/06 8.0 (@ 1500 hrs)
18/02/06 7.1 (@ 1700 hrs)
19/02/06 4.4 (@ 1300 hrs)
19/02/06 6.7 (@ 1450 hrs)
19/02/06 5.2 (@ 1800 hrs)
I want to:
1. average these results
2. report the number of data points (readings)
3. report the number of days over which readings were taken.
I can get the average by using the AVERAGE function.
I can get the number of readings by using the COUNT function.
... but, how do I get the number of days?
If I use the CountA function it returns 9 days when readings were
recorded clearly over only 3 days.
What I need is something like a COUNT-DISTINCT function.
Can anyone help with a solution to this question.
Many thanks, James Elliott
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
