Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
if statement
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
if statement
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 wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
if statement
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 Paul wrote: 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 wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF statement inside a SUMIF statement.... or alternative method | Excel Worksheet Functions | |||
Reconcile Bank statement & Credit card statement & accounting data | Excel Worksheet Functions | |||
Embedding an OR statement in an IF statement efficiently | Excel Discussion (Misc queries) | |||
If statement or lookup statement not sure | Excel Worksheet Functions | |||
appending and IF statement to an existing IF statement | Excel Worksheet Functions |