Same formula, different results

[indzara]

I have two cells with the following formulas.


Cell 1: SMALL(Tbl_Project_List[[#Data],[Priority]],H7)


Cell 2: SMALL(Tbl_Project_List[[#Data],[Priority]],H8)


Both H7 and H8 have the value 1. So I expect the same result in Cells 1 and 2. However, I get a correct value in Cell 1 and a #NUM error in Cell 2.


Any suggestions? Thanks in advance.

 

[Jai9]

Hi,


Most likely reason is H7 and H8 do not hold the same value.


H8 might have an extra space or a character.


Try H7=H8 in any other cell and see the result. If they are same then result would be TRUE else FALSE.


Jai

 

[Faseeh]

@ indzara,


Suggestions?? Upload the sample file!! I just tried it, not giving error at my end.


Regards,

 

[SirJB7]

Hi, indzara!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


If you already checked that calculation mode is set to automatic, you're in trouble, otherwise firstly check it. Then additionally to what Jai9 correctly stated, you can try to manually enter a value of 1 in both H7:H8 if that cell uses a formula. Another thing might be testing if both cells have same format and data type (numeric, etc.). But all these just in case H7=H8 is false; if it's true, please follow Faseeh suggestion (give a look at the green sticky posts at this forums main page for uploading guidelines).


Regards!

 

[indzara]

Thanks for the instant reply. I was amazed at how quickly the community responded. I am sorry that my response is a delayed one.


I traced the problem to a rounding error. The two cells H7 and H8 in my example had formulas that were counting number of unique values in a range with different parameters.


SUMPRODUCT(1/COUNTIF($A$9:A20,$A$9:A20))


They both seemed to return the same value since my H7=H8 was returning TRUE. That's when I posted the question in the forum. However, when I later applied the ROUND function, the error went away. ROUND(SUMPRODUCT(1/COUNTIF($A$9:A20,$A$9:A20)),0)


I used this in a Project planning template to extract and sort project task lists in the order of project priorities. The file is available here. http://sdrv.ms/XRV7Z6