SOLVED
Home

#N/A Error with RANK.EQ

%3CLINGO-SUB%20id%3D%22lingo-sub-753930%22%20slang%3D%22en-US%22%3E%23N%2FA%20Error%20with%20RANK.EQ%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753930%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20working%20on%20an%20Excel%20file%20that%20contains%20roughly%20622%20numbers.%26nbsp%3B%20I'm%20trying%20to%20use%20the%20RANK.EQ%20function%20to%20capture%20the%20rank%20of%20each%20number%20in%20the%20list.%26nbsp%3B%20Duplicate%20numbers%20should%20have%20the%20same%20rank%20(e.g.%2C%20if%20the%20list%20goes%20229%2C%20216%2C%20200%2C%20198%2C%20198%2C%20197%2C%20the%20rank%20should%20be%201%2C%202%2C%203%2C%204%2C%204%2C%206).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20using%20the%20following%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DRANK.EQ(D2%2CD2%3AD623%2C1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ED2%20contains%20the%20first%20number%20in%20the%20list%2C%20D2%3AD623%20is%20the%20range.%26nbsp%3B%20I%20keep%20getting%20the%20%23N%2FA%20error%20and%20can't%20figure%20out%20why%20no%20values%20are%20returned.%26nbsp%3B%20The%20data%20in%20cells%20D2%20to%20D623%20are%20formatted%20as%20numbers.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20anyone%20have%20some%20experience%20with%20this%20formula%20that%20can%20help%20me%20understand%20what%20I'm%20overlooking%3F%26nbsp%3B%20Any%20assistance%20would%20be%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-753930%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-753945%22%20slang%3D%22en-US%22%3ERe%3A%20%23N%2FA%20Error%20with%20RANK.EQ%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753945%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F376079%22%20target%3D%22_blank%22%3E%40MetsMike%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUse%20absolute%20references%20for%20the%20range%3A%3C%2FP%3E%3CPRE%3E%3CSPAN%3E%3DRANK.EQ(D2%2CD%242%3AD%24623%2C1)%3C%2FSPAN%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-753946%22%20slang%3D%22en-US%22%3ERe%3A%20%23N%2FA%20Error%20with%20RANK.EQ%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753946%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F376079%22%20target%3D%22_blank%22%3E%40MetsMike%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eyou%20can%20try%20following%20formula%3C%2FP%3E%3CP%3E%3DRANK.EQ(D2%2C%24D%242%3A%24D%24623%2C1)%3C%2FP%3E%3CP%3EIt%20is%20just%20that%20you%20might%20need%20to%20use%20the%20absolute%20reference%20for%20D2%3AD623%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-753947%22%20slang%3D%22en-US%22%3ERe%3A%20%23N%2FA%20Error%20with%20RANK.EQ%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753947%22%20slang%3D%22en-US%22%3EThanks%20for%20the%20suggestion.%20The%20formula%20still%20returns%20the%20%23N%2FA%20value.%20I%E2%80%99m%20guessing%20it%20has%20to%20be%20something%20in%20the%20source%20data%20that%20needs%20to%20be%20addressed.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-753948%22%20slang%3D%22en-US%22%3ERe%3A%20%23N%2FA%20Error%20with%20RANK.EQ%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753948%22%20slang%3D%22en-US%22%3EThank%20you%20for%20the%20suggestion.%20The%20formula%20still%20returns%20the%20%23N%2FA%20value.%20I%E2%80%99m%20guessing%20it%E2%80%99s%20an%20issue%20with%20the%20source%20data%20that%20I%E2%80%99ll%20need%20to%20review.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-753949%22%20slang%3D%22en-US%22%3ERe%3A%20%23N%2FA%20Error%20with%20RANK.EQ%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753949%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F376079%22%20target%3D%22_blank%22%3E%40MetsMike%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%20your%20numbers%20could%20be%20formatted%20as%20text%2C%20you%20may%20check%20in%20any%20empty%20cell%20as%20%3DISTEXT(D2).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-753950%22%20slang%3D%22en-US%22%3ERe%3A%20%23N%2FA%20Error%20with%20RANK.EQ%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753950%22%20slang%3D%22en-US%22%3EThank%20you!%20That%20was%20indeed%20the%20issue.%20I%20was%20incorrect%20about%20the%20cell%20formatting%20in%20the%20source.%20Converting%20the%20formatting%20did%20the%20trick!%20Much%20appreciated!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-754024%22%20slang%3D%22en-US%22%3ERe%3A%20%23N%2FA%20Error%20with%20RANK.EQ%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-754024%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F376079%22%20target%3D%22_blank%22%3E%40MetsMike%3C%2FA%3E%26nbsp%3Bgood%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
MetsMike
New Contributor

I'm working on an Excel file that contains roughly 622 numbers.  I'm trying to use the RANK.EQ function to capture the rank of each number in the list.  Duplicate numbers should have the same rank (e.g., if the list goes 229, 216, 200, 198, 198, 197, the rank should be 1, 2, 3, 4, 4, 6).

 

I'm using the following formula:

 

=RANK.EQ(D2,D2:D623,1)

 

D2 contains the first number in the list, D2:D623 is the range.  I keep getting the #N/A error and can't figure out why no values are returned.  The data in cells D2 to D623 are formatted as numbers. 

 

Does anyone have some experience with this formula that can help me understand what I'm overlooking?  Any assistance would be appreciated.

7 Replies

@MetsMike 

Use absolute references for the range:

=RANK.EQ(D2,D$2:D$623,1)

@MetsMike 

you can try following formula

=RANK.EQ(D2,$D$2:$D$623,1)

It is just that you might need to use the absolute reference for D2:D623

Thanks for the suggestion. The formula still returns the #N/A value. I’m guessing it has to be something in the source data that needs to be addressed.
Thank you for the suggestion. The formula still returns the #N/A value. I’m guessing it’s an issue with the source data that I’ll need to review.
Solution

@MetsMike 

As variant your numbers could be formatted as text, you may check in any empty cell as =ISTEXT(D2).

Thank you! That was indeed the issue. I was incorrect about the cell formatting in the source. Converting the formatting did the trick! Much appreciated!

@MetsMike good, you are welcome

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies