Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
ISNA function
Hi all,
I have a spreadsheet that I'm using to compare financial information (reconcile bank statements). I am using a formula that looks at a number and then searches another column to see if that number matches. Example =ISNA(MATCH(A1, B1:B50, FALSE)) If the result is FALSE, it tells me that the number is in the next column. If it's TRUE, the number doesn't match. The number that is in A1 is coming from another sheet. The cell on the other sheet contains a simple formula which is just adding the cash and checks together to come up with the total deposit. The numbers that are in B1:B50 are copied and pasted from another spreadsheet that was downloaded off the banks website. There is one deposit that is not matching up to the bank list. This number is actually there though. If I change the cell that A1 is pulling from (change it from a formula to a manually entered value) the ISNA formula works. Also, if I change the cash or the check numbers by 1 cent (so the deposit is one cent different), and change the bank number by one cent, the formula works. The formula just doesn't seem to like that one number. Has anyone ever seen this before? Thanks for any help you can offer! Jennifer |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
ISNA function
Try rounding the number. Sometimes - I still don't know why - Excel
adds insignificant digits to a number. If you round the number on your linked spreadsheet (the one where you add up all the bank deposits / checks) then this should solve your problem. the formula would be =ROUND(sum(b1:b50),2) On Aug 15, 9:37 am, wrote: Hi all, I have a spreadsheet that I'm using to compare financial information (reconcile bank statements). I am using a formula that looks at a number and then searches another column to see if that number matches. Example =ISNA(MATCH(A1, B1:B50, FALSE)) If the result is FALSE, it tells me that the number is in the next column. If it's TRUE, the number doesn't match. The number that is in A1 is coming from another sheet. The cell on the other sheet contains a simple formula which is just adding the cash and checks together to come up with the total deposit. The numbers that are in B1:B50 are copied and pasted from another spreadsheet that was downloaded off the banks website. There is one deposit that is not matching up to the bank list. This number is actually there though. If I change the cell that A1 is pulling from (change it from a formula to a manually entered value) the ISNA formula works. Also, if I change the cash or the check numbers by 1 cent (so the deposit is one cent different), and change the bank number by one cent, the formula works. The formula just doesn't seem to like that one number. Has anyone ever seen this before? Thanks for any help you can offer! Jennifer |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
ISNA function
It might be that the number is not exactly what it seems - for
example, it might look like a value of 12.67, but if you increase the number of decimal places then you might see 12.6666667. You could think about using ROUND in your formula, rather than trying to match exactly where you might have small discrepancies like this to contend with. Hope this helps. Pete On Aug 15, 2:37 pm, wrote: Hi all, I have a spreadsheet that I'm using to compare financial information (reconcile bank statements). I am using a formula that looks at a number and then searches another column to see if that number matches. Example =ISNA(MATCH(A1, B1:B50, FALSE)) If the result is FALSE, it tells me that the number is in the next column. If it's TRUE, the number doesn't match. The number that is in A1 is coming from another sheet. The cell on the other sheet contains a simple formula which is just adding the cash and checks together to come up with the total deposit. The numbers that are in B1:B50 are copied and pasted from another spreadsheet that was downloaded off the banks website. There is one deposit that is not matching up to the bank list. This number is actually there though. If I change the cell that A1 is pulling from (change it from a formula to a manually entered value) the ISNA formula works. Also, if I change the cash or the check numbers by 1 cent (so the deposit is one cent different), and change the bank number by one cent, the formula works. The formula just doesn't seem to like that one number. Has anyone ever seen this before? Thanks for any help you can offer! Jennifer |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
ISNA function
You're welcome - thanks for feeding back.
Pete Thanks so much!!! It worked! On Aug 15, 2:50 pm, Pete_UK wrote: It might be that the number is not exactly what it seems - for example, it might look like a value of 12.67, but if you increase the number of decimal places then you might see 12.6666667. You could think about using ROUND in your formula, rather than trying to match exactly where you might have small discrepancies like this to contend with. Hope this helps. Pete On Aug 15, 2:37 pm, wrote: Hi all, I have a spreadsheet that I'm using to compare financial information (reconcile bank statements). I am using a formula that looks at a number and then searches another column to see if that number matches. Example =ISNA(MATCH(A1, B1:B50, FALSE)) If the result is FALSE, it tells me that the number is in the next column. If it's TRUE, the number doesn't match. The number that is in A1 is coming from another sheet. The cell on the other sheet contains a simple formula which is just adding the cash and checks together to come up with the total deposit. The numbers that are in B1:B50 are copied and pasted from another spreadsheet that was downloaded off the banks website. There is one deposit that is not matching up to the bank list. This number is actually there though. If I change the cell that A1 is pulling from (change it from a formula to a manually entered value) the ISNA formula works. Also, if I change the cash or the check numbers by 1 cent (so the deposit is one cent different), and change the bank number by one cent, the formula works. The formula just doesn't seem to like that one number. Has anyone ever seen this before? Thanks for any help you can offer! Jennifer- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF(ISNA.... | Excel Discussion (Misc queries) | |||
ISNA Help | Excel Worksheet Functions | |||
ISNA help | Excel Worksheet Functions | |||
Using ISNA with OR | Excel Worksheet Functions | |||
example if IF(ISNA()) function | Excel Worksheet Functions |