Sorting With dynamic Fields

[man4mac]

Hi everyone,

I don't understand why my sort will not work. Let me show you what I have:

2 Columns, 1 Text and 1 Numbers, I want it to sort by the numbers, the text is static, and the numbers are dynamic wtih this formula:

{=SUM(IF('Simple View'!$I$2:$I$999="Powered by "&'Fortune 50 Stats'!A24,IF('Simple View'!$J$2:$J$999="Fortune 50",'Simple View'!$E$2:$E$999,0),0))/(SUM(IF('Simple View'!$J$2:$J$999="Fortune 50",IF('Simple View'!$I$2:$I$999="Powered By "&'Fortune 50 Stats'!A24,1,0),0)))}

(Sorry I know its kinda daunting)

But basically it when I sort, eveything messes up, the numbers don't even sort correctly:
17
9
9
3.5
27.25
10
9.7
12.41666667

After I sort by column B (total time) I get this, and my text doesn't match up with the number it should be with.

9.7
27.25
3.5
9
9
17
12.41666667
10

If anyone knows something I don't it would be appreciated

[Otto Moehrbach]

I would say that some of the numbers are text and some are numbers. Try
multiplying by 1 within your formula. That forces a text number to a number.
HTH Otto
"man4mac" <[email protected]> wrote in
message news:[email protected]...
>
> Hi everyone,
>
> I don't understand why my sort will not work. Let me show you what I
> have:
>
> 2 Columns, 1 Text and 1 Numbers, I want it to sort by the numbers, the
> text is static, and the numbers are dynamic wtih this formula:
>
> {=SUM(IF('Simple View'!$I$2:$I$999="Powered by "&'Fortune 50
> Stats'!A24,IF('Simple View'!$J$2:$J$999="Fortune 50",'Simple
> View'!$E$2:$E$999,0),0))/(SUM(IF('Simple View'!$J$2:$J$999="Fortune
> 50",IF('Simple View'!$I$2:$I$999="Powered By "&'Fortune 50
> Stats'!A24,1,0),0)))}
>
> (Sorry I know its kinda daunting)
>
> But basically it when I sort, eveything messes up, the numbers don't
> even sort correctly:
> 17
> 9
> 9
> 3.5
> 27.25
> 10
> 9.7
> 12.41666667
>
> After I sort by column B (total time) I get this, and my text doesn't
> match up with the number it should be with.
>
> 9.7
> 27.25
> 3.5
> 9
> 9
> 17
> 12.41666667
> 10
>
> If anyone knows something I don't it would be appreciated
>
>
> --
> man4mac
> ------------------------------------------------------------------------
> man4mac's Profile:
> http://www.excelforum.com/member.php...o&userid=36535
> View this thread: http://www.excelforum.com/showthread...hreadid=565215
>