if statement

[Steve Squires]

Hi guys, need a little help agian please...

I got a strange situation with Excel. I have set up a multiple
worksheet workbook for my cashflow, nothing complicated, just one
sheet for each month and a summary sheet at the end, summarising each
month's outgoing and ingoing totals by category.

Anyhoo, I have inserted a simple if statement into my monthly summary
totals which compares the final monthly total on the summary sheet
with the final monthly total on the individual month sheet (just as a
validity cross check to catch typos).

Very simple if statement: =IF(J24<>Sept!E64,"CHECK","OK")
J24 being the total on the summary sheet for September.

Now here is the wierd thing, most of the time it works just fine, BUT
every now and again as I adjust the ongoing figures for the month, it
seems to swap the true and false results, so that if the two "total"
cells match it comes up with a "check" warning instead of ok.

Is this because the "total" cells contain formulas instead of values?
If this is the case how can I tell the if statement just to work on
the cell value and to ignore the formula?

Question is, why does this statement work fine one moment, and then
all of a sudden decide that two cells that contain the same value
result as false?

I would really appreciate some input on this one please, I am very
confused...

tia
Steve

 

[paul.robinson]

Hi
How have you rounded the numbers? If you are displaying totals to the
nearest Euro (say) but are working with numbers to the nearest cent,
the comparison will be based on the nearest cent figures.
e.g. The two totals you are comparing might look like 250 euro, but
might actually BE 250.11 and 250.24 (click on the numbers and look in
the formula bar). These are two different numbers in your If, and you
would get the "Check" message.
If this is your problem use

IF(Round(J24,0)<>Round(Sept!E64,0),"CHECK","OK")

to round the numbers to the nearest Euro before checking.

regards
Paul

 

[Steve Squires]

Hi Paul, thanks for the reply. That worked perfectly, thank you. I had
formatted my cells to display currency to two decimal places, but I
had not considered that Excel was using more places for the
comparison. Using the Round function within the formula, as you
suggested, solved the problem perfectly.

Thanks again, much appreciated.
regards
Steve