Excel 2007 Identify bad meter readings

[clarkpaul]

I have a series of meter readings sent in by field techs that tend to have finger checks & provide absolutely bizarre readings. Do you have any idea how I can identify the "Bad" meter readings using excel? .... Thanks!

Meter # <colgroup><col width="71"></colgroup><tbody> </tbody> <colgroup><col width="71"></colgroup><tbody> </tbody>	Meter Reading <colgroup><col width="99"></colgroup><tbody> </tbody>	Reading Date <colgroup><col width="89"></colgroup><tbody> </tbody>	Reading good or bad? <colgroup><col width="142"></colgroup><tbody> </tbody>
35397220 <colgroup><col width="71"></colgroup><tbody> </tbody>	108,825 <colgroup><col></colgroup><tbody> </tbody>	02/05/2015 <colgroup><col width="89"></colgroup><tbody> </tbody>	Good <colgroup><col width="142"></colgroup><tbody> </tbody>
35397220 <colgroup><col width="71"></colgroup><tbody> </tbody>	116,583 <colgroup><col></colgroup><tbody> </tbody>	02/10/2015 <colgroup><col width="89"></colgroup><tbody> </tbody>	Good <colgroup><col width="142"></colgroup><tbody> </tbody>
35397220 <colgroup><col width="71"></colgroup><tbody> </tbody>	156,354 <colgroup><col></colgroup><tbody> </tbody>	02/13/2015 <colgroup><col width="89"></colgroup><tbody> </tbody>	Good <colgroup><col width="142"></colgroup><tbody> </tbody>
35397220 <colgroup><col width="71"></colgroup><tbody> </tbody>	145,000 <colgroup><col></colgroup><tbody> </tbody>	02/24/2015 <colgroup><col width="89"></colgroup><tbody> </tbody>	Bad <colgroup><col width="142"></colgroup><tbody> </tbody>
35397220 <colgroup><col width="71"></colgroup><tbody> </tbody>	145,000 <colgroup><col></colgroup><tbody> </tbody>	02/27/2015 <colgroup><col width="89"></colgroup><tbody> </tbody>	Bad <colgroup><col width="142"></colgroup><tbody> </tbody>
35397220 <colgroup><col width="71"></colgroup><tbody> </tbody>	217,705 <colgroup><col></colgroup><tbody> </tbody>	03/02/2015 <colgroup><col width="89"></colgroup><tbody> </tbody>	Good <colgroup><col width="142"></colgroup><tbody> </tbody>
35397220 <colgroup><col width="71"></colgroup><tbody> </tbody>	221,014 <colgroup><col></colgroup><tbody> </tbody>	03/03/2015 <colgroup><col width="89"></colgroup><tbody> </tbody>	Good <colgroup><col width="142"></colgroup><tbody> </tbody>
35397220 <colgroup><col width="71"></colgroup><tbody> </tbody>	2,232,372 <colgroup><col></colgroup><tbody> </tbody>	03/04/2015 <colgroup><col width="89"></colgroup><tbody> </tbody>	Bad <colgroup><col width="142"></colgroup><tbody> </tbody>
35397220 <colgroup><col width="71"></colgroup><tbody> </tbody>	259,394 <colgroup><col></colgroup><tbody> </tbody>	03/10/2015 <colgroup><col width="89"></colgroup><tbody> </tbody>	Good <colgroup><col width="142"></colgroup><tbody> </tbody>
35397220 <colgroup><col width="71"></colgroup><tbody> </tbody>	288,093 <colgroup><col></colgroup><tbody> </tbody>	03/11/2015 <colgroup><col width="89"></colgroup><tbody> </tbody>	Good <colgroup><col width="142"></colgroup><tbody> </tbody>
35397220 <colgroup><col width="71"></colgroup><tbody> </tbody>	332,660 <colgroup><col></colgroup><tbody> </tbody>	03/18/2015 <colgroup><col width="89"></colgroup><tbody> </tbody>	Good <colgroup><col width="142"></colgroup><tbody> </tbody>
35397220 <colgroup><col width="71"></colgroup><tbody> </tbody>	2,334,216 <colgroup><col></colgroup><tbody> </tbody>	03/19/2015 <colgroup><col width="89"></colgroup><tbody> </tbody>	Bad <colgroup><col width="142"></colgroup><tbody> </tbody>

<tbody>
</tbody>

<colgroup><col width="71"></colgroup><tbody>
</tbody>

 

[JoeMo]

You could improve your chances of getting some help by providing the reason(s) why the readings you marked "bad" are bad.

 

[krausr79]

What makes them bad? No/Negative increase or much higher? You could do an if check to see if it's not higher than before
=if(B2<=B1,"Not higher","")
and you could do a check to see if it's more than double
=if(B2>(B1*2),"Over Double","Good")
and you could combine these checks
=if(B2<=B1,"Not higher",)if(B2>(B1*2),"Over Double","Good")
Although with a month that's too high, the month after will believe it's 'Not Higher'.

 

[clarkpaul]

You could improve your chances of getting some help by providing the reason(s) why the readings you marked "bad" are bad.

The readings must sequential but not too big. The readings over 2 million are bad since they don't follow the data stream:

108,825 <colgroup><col></colgroup><tbody> </tbody>

116,583 <colgroup><col></colgroup><tbody> </tbody>

156,354 <colgroup><col></colgroup><tbody> </tbody>

217,705 <colgroup><col></colgroup><tbody> </tbody>

221,014 <colgroup><col></colgroup><tbody> </tbody>

259,394 <colgroup><col></colgroup><tbody> </tbody>

288,093 <colgroup><col></colgroup><tbody> </tbody>

332,660 <colgroup><col></colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[Michael M]

Have you tried testing with the formulae provided by krausr79 ??
I think they would be a good starting point.

 

[JoeMo]

Can you quantify what "not too big" is. For example if 108,625 is a good reading, what is the maximum percent increase over that for the next reading to maintain a good rating?