Nested If Statement help
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/
|