tough cookie
 

Hi everyone

I have readings (hourmeters of water pumps) in random days. In column A i
have all days of the year, which means i have holes in my table. column B has
the hours when the readings were made and column C has the readings.

In column D i have the reading of a certain day minus the previous reading.
D169=IF(C169=0;"";(C169-INDEX($C$6:C168;MATCH(9,99999999999999E+307;$C$6:C 168))))

My problems are in column E. This column is the average per day since last
reading.
It should have this formula: E169=(D169/(2+(B169-B167)))
2 is the number of days between readings in this example.

My real problem is to get a formula that counts the number of gaps between
readings.

I´ve tried this to find the number of days between readings:

=IF(C169="";"";(MATCH(C169;$C$6:C169))-MATCH((INDEX($C$6:C168;MATCH(9,99999999999999E+307 ;$C$6:C168)));$C$6:C168))

Unfortunately, when a pump doesn´t work, i have readings with the same value
and the function above will be equal to zero altough many days have passed.
Thats because it found an exact match above.

HELP!
Thanks everyone

tough cookie
 

Hi Vincent

If the appearance of consecutive numbers of the same value in column C
always mean that it is a failure of the pump to give a true reading, then
you could use a formula in column D

=IF(C7="","",IF(C7=LOOKUP(9,99999999999999E+307,$C $6:C6),"",C7))
and copy down.
Carry out your Average function using column D instead of column C.
--
Regards
Roger Govier



"Vincent" wrote in message
...
Hi everyone

I have readings (hourmeters of water pumps) in random days. In column A i
have all days of the year, which means i have holes in my table. column B
has
the hours when the readings were made and column C has the readings.

In column D i have the reading of a certain day minus the previous
reading.
D169=IF(C169=0;"";(C169-INDEX($C$6:C168;MATCH(9,99999999999999E+307;$C$6:C 168))))

My problems are in column E. This column is the average per day since last
reading.
It should have this formula: E169=(D169/(2+(B169-B167)))
2 is the number of days between readings in this example.

My real problem is to get a formula that counts the number of gaps between
readings.

I´ve tried this to find the number of days between readings:

=IF(C169="";"";(MATCH(C169;$C$6:C169))-MATCH((INDEX($C$6:C168;MATCH(9,99999999999999E+307 ;$C$6:C168)));$C$6:C168))

Unfortunately, when a pump doesn´t work, i have readings with the same
value
and the function above will be equal to zero altough many days have
passed.
Thats because it found an exact match above.

HELP!
Thanks everyone

tough cookie
 

Hi Roger,

the appearance of consecutive numbers of the same value in column C
doesn´t mean that it is a failure of the pump to give a true reading. Those
are REAL values. The failure occurs in the formula (it freaks out :)).

"Roger Govier" escreveu:

Hi Vincent

If the appearance of consecutive numbers of the same value in column C
always mean that it is a failure of the pump to give a true reading, then
you could use a formula in column D

=IF(C7="","",IF(C7=LOOKUP(9,99999999999999E+307,$C $6:C6),"",C7))
and copy down.
Carry out your Average function using column D instead of column C.
--
Regards
Roger Govier

tough cookie
 

Hi Vincent

If you want to mail me a copy of the workbook, and an explanation of where
the error is occurring, I will take a look for you.

To mail direct use
roger at technology4u dot co dot uk
Do the obvious with at and dot.

--
Regards
Roger Govier



"Vincent" wrote in message
...
Hi Roger,

the appearance of consecutive numbers of the same value in column C
doesn´t mean that it is a failure of the pump to give a true reading.
Those
are REAL values. The failure occurs in the formula (it freaks out :)).

"Roger Govier" escreveu:

Hi Vincent

If the appearance of consecutive numbers of the same value in column C
always mean that it is a failure of the pump to give a true reading, then
you could use a formula in column D

=IF(C7="","",IF(C7=LOOKUP(9,99999999999999E+307,$C $6:C6),"",C7))
and copy down.
Carry out your Average function using column D instead of column C.
--
Regards
Roger Govier