I have a spreadsheet showing data from electricity invoices.
Sorted by meter number and date
My boss uses this to club the operations team when they havent checked the meters recently.

Col H contains the meter number
Col O contains whether the read on the bill was an estimate or actual

So far, I have managed to check if the bill in question, was an estimate or anactual, and if its an actual mark it as ok, if its an estimate, mark it to be overdue to be checked.

=IF(O14="A","0","overdue")

I then expanded, to check previous bills

=IF(O14="A","3",IF(O13="A","2",IF(O12="A","1","ove rdue")))

Which works, a bit, and provides a countdown of when it next needs to be checked.
The problem being if row 14 is a new meter, it will check rows 13 and 12, which will be a different meter.

So, I added a meter number Check

=IF(O14="A","0",IF(H14=H13,IF(O13="A","1",IF(H14=H 12,IF(O12="A","2",IF(H14=H11,IF(O11="A","3","Overd ue")))))))

This checks that the meter number is the same on both the active row, and the preceeding rows, before checking whether they are actuals or not.

Now, this has prevented the spread sheet checking unreleated bills, but its returning a huge amount of "false" answers, rather than overdue.
Now, this appears to be quite obvious, the only time I'm telling it to return overdue is if it manages to make it through every if statement before finding a false answer.

But it wont let me add anything after the "Overdue"), it just says the formula contains an error until I remove it.

Its not a giant problem, I know both false and overdue require actual meter reads, its just, messy.

Any suggestions?

On Thursday, October 11, 2012 7:47:05 AM UTC-5, DominicJ wrote:
I have a spreadsheet showing data from electricity invoices.

Sorted by meter number and date

My boss uses this to club the operations team when they havent checked

the meters recently.



Col H contains the meter number

Col O contains whether the read on the bill was an estimate or actual



So far, I have managed to check if the bill in question, was an estimate

or anactual, and if its an actual mark it as ok, if its an estimate,

mark it to be overdue to be checked.



=IF(O14="A","0","overdue")



I then expanded, to check previous bills



=IF(O14="A","3",IF(O13="A","2",IF(O12="A","1","ove rdue")))



Which works, a bit, and provides a countdown of when it next needs to be

checked.

The problem being if row 14 is a new meter, it will check rows 13 and

12, which will be a different meter.



So, I added a meter number Check



=IF(O14="A","0",IF(H14=H13,IF(O13="A","1",IF(H14=H 12,IF(O12="A","2",IF(H14=H11,IF(O11="A","3","Overd ue")))))))



This checks that the meter number is the same on both the active row,

and the preceeding rows, before checking whether they are actuals or

not.



Now, this has prevented the spread sheet checking unreleated bills, but

its returning a huge amount of "false" answers, rather than overdue.

Now, this appears to be quite obvious, the only time I'm telling it to

return overdue is if it manages to make it through every if statement

before finding a false answer.



But it wont let me add anything after the "Overdue"), it just says the

formula contains an error until I remove it.



Its not a giant problem, I know both false and overdue require actual

meter reads, its just, messy.



Any suggestions?









--

DominicJ
Along with this msg, provide a file with before/after examples/