V
Vincent
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:C168))))
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
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:C168))))
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