IF function not working properly (but only sometimes)

[pipervinwa17]

I work in a retail setting, and I have a spreadsheet set up to represent each day of sales. Columns are set up to represent my cash registers, and formulas are in place to tell me what my cash deposit should be after I enter all relevant information.


Then, on another tab, I have a column set up to enter how much cash I actually have in my possession. I have an IF function set up to tell me if the amount of cash in hand matches what my deposit should be for each of the registers.


About 5 times out of 10 the formula works properly and tells me Match or No Match if/when my deposit/cash matches.


Here is the issue: Why does the formula not work every time? I use the exact same spreadsheet every single day, and I have not touched the formula. Yet, like today, my numbers clearly match but the formula is returning "NO MATCH"


Help!


Here is the formula I am using:


=IF(J7=E13,"MATCH","NO MATCH")


Please let me know if I am entering something incorrectly. I have even tried deleting the formula out, closing the sheet, re-opening, re-typing in different cells, and it still is not working properly.


Assistance is appreciated!

(And yes, I clearly understand that I do not need this formula to work to tell me how much cash I have; I am just trying to figure out why the formula picks and chooses when to work)

 

[mercatog]

What do you have in J7 and E13? text or numbers?

 

[Jai9]

Hi,


As far as I understood from the post, J7 and E13 are on different tabs/sheets.


Try,


=IF(sheet1!J7=Sheet2!E13,"MATCH","NO MATCH")


change the sheet no. to match those on your file.


Jai

 

[Debraj]

Hi Pipervinwa17!

Welcome to the forum..


about your question..


I have lots of blind Guess..


* Can you please try the below Formula's

=IF(VALUE(TRIM(J7))=VALUE(TRIM(E13)),"MATCH","NO MATCH")


* Can you Please select put below two formula in any cell in Excel and check both are same or not..

[code]=CELL("type",J7)

=CELL("type",E13)

* Can you please change the formula to..

=IF(EXACT(E13,J7),"Match","No Match&#34[/code]


As you can see, we have only option available is GUESS...


So, it will be better if you can upload a sample file.

Refer : http://chandoo.org/forums/topic/posting-a-sample-workbook


Regards,

Deb

 

[ninad7]

@OP: can you post a sample file.

 

[SirJB7]

Hi, pipervinwa17!


A few questions:


What format have the cells J7 and E13?

Are those values inputted manually or calculated by formulas? If formulas, which ones?

Tried something like?:

=IF(ROUND(J7,2)=ROUND(E13,2),"MATCH","NO MATCH")


Regards!

 

[pipervinwa17]

Thank you all for the suggestions!!


The one that ended up working was @SirJB7. Using the Round function did it. Even though the cells are formatted the same, I am guessing because one is a formula and one is manually typed, it was causing an error.


For those that asked, the format of the cells on J7 and E13 are: Currency, 2 decimal places


Also, to @Jai9, J7 and E13 are in the same sheet. I guess I explained it sort of weird. What I was trying to say is that the value in J7 comes from sheet 1, from a formula. J7 exactly is: ='Daily Cash Report'!N15.

The value from E13 is a sum of numbers I manually enter in E5:E12

 

[SirJB7]

Hi, pipervinwa17!

Glad you solved it. Thanks for your feedback. And welcome back whenever needed or wanted.

Regards!