Excel help please

Incredibly I had to do this a few days ago.

as usual, it's a botch job that others will improve on.

There's a function "Isnumber" that allows you to get rid of the NA stuff.....

So I somehow built a new column so I only had numbers or blanks =IF(isnumber(a1),a1,0) or something like that which gave a list of numbers with 0s, so the rank put them all to the bottom.

You might want to change the last number to -10000 or something to make sure it's at the bottom, then hide the column and rank that column

Sure you get the idea.

Ah Lori : you'll have realised maybe that I tried to put in some symbols in my original post that weren't recognised by the forum programme, just noticed it

Ok : i'll work out some usage of Isnumber. Tried your trick of adding .0001 to the source data but that didnt work too.

Thanks.

Try
Assuming numbers to rank are in Cells A4:A20

=IF(ISNUMBER(A4),RANK(A4,$A$4:$A$20,1),"")

starfish and coffee

I may be barking up the wrong tree but maybe your query is similar to one I had myself a while back. Fellow forumite Compound Magic came to my rescue. I have posted my original query and the eventual solution from Compoung Magic below.

Question

I have columns of data for several races each day , e.g.

Cell A1 'Race', Cell B1 'Rating', Cell C1 'Horse'

Cell A2 '1.45Bangor', Cell B2 '105', Cell C2 'Seven Is My Number'
Cell A3 '1.45Bangor', Cell B3 '101', Cell C3 'Kings Forest'
etc, etc.

I want to rank my ratings for each individual race. I have experimented with Rank formulas but they keep ranking from all the races in the spreadsheet and not the individual races.

I believe an array formula may be appropriate but I'm unsure as to how to construct it.

Thanks in advance.

Solution

Been testing in lab chat think I have it!!!

= SUMPRODUCT(--(A$2:A$100=A2),--(B2 ˂ B$2:B$100))+1

You will have to type it in. Copy and paste may not work

You can also use =ISNA(...) to test whether a returned value is #N/A or not.

I agree with bushy - you should use ISNA in the formula where the #N/A is being returned to convert it to a value or blanks.

Thanks very much guys : nothing like that was coming up on google

........Compoung Magic........

Sorry C M; more haste less speed required on my part methinks.