Large and Small

[matthewlouis]

I have a column of numbers I want to rank large to small. Large(E4:E37,1), etc..... but I'm getting a #NUM error. The cells I am ranking are linked to another tab in the workbook. In other words, it says 28% in Cell C3 . . . but what's in the cell is not 28% but the cell in another tab referenced as "XLB Cube'!$C$3 (that's where 28% is)

I even tried to name the cell range using the NAME function and then tried again . . . same thing, #NUM . . . I would like to have them rank as they change, too.

Does anyone have any suggestions?

Thanks in advance!!

 

[Fluff]

Check that the values in XLB Cube C3 etc are numbers
=isnumber(C3)

 

[matthewlouis]

OK, I went back to 'that cell and put in =ISNUMBER('XLB Cube'!$C$3) and it says FALSE (?)

Also, the 'XLB Cube'!$C$3 cell is a link to a number that is not a link or formula in another tab

 

[Fluff]

In that case C3 does not contain a number, which is why the Large function doesn't work.
You will need to convert the cells to numbers.
One option is to use the text to columns feature on the data tab.
Select the cells > text to columns > delimited > next > uncheck all boxes > Finish

 

[matthewlouis]

OK, that didn't fix it. I took out all the checks and I have same #NUM error.

I don;t understand, the source is a number . . . so the link isn't?

 

[pgc01]

If the the values in the cells are text values you can try:

=LARGE(INDEX(E4:E37+0,),1)

 

[matthewlouis]

Ahhhhh . . . . that did it, pgc01, thank you!!

And thanks Fluff for your help as well.

 

[matthewlouis]

Well . . . on 2nd thought, I still need to be able to covert cell C3 to a number. Ideas?

 

[Fluff]

In that case on the XLB Cube sheet select C3 downwards & use text to columns

 

[matthewlouis]

Well, that didn't work either. Hmmmmm. Still won't even rank with a Top 10 Conditional formula . .. not recognizing them as numbers still.