Nested If Statement help

[DominicJ]

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 checke
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 estimat
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","overdue")))

Which works, a bit, and provides a countdown of when it next needs to b
checked.
The problem being if row 14 is a new meter, it will check rows 13 an
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=H12,IF(O12="A","2",IF(H14=H11,IF(O11="A","3","Overdue")))))))

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

Now, this has prevented the spread sheet checking unreleated bills, bu
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 t
return overdue is if it manages to make it through every if statemen
before finding a false answer.

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

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

Any suggestions

 

[Don Guillett]

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","overdue")))



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=H12,IF(O12="A","2",IF(H14=H11,IF(O11="A","3","Overdue")))))))



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?

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