How the f do you do it? Google'd w/ no success, dont have time to search in depth.

'TEXT:'&=SUM(A1:A2)... somethign like that, thx

Fig'd it out: ="The Sum Is: "& SUM(A1:A10)

[Edited on October 24, 2005 at 2:20 PM. Reason : got it]

10/24/2005 2:17:10 PM

good call

10/24/2005 3:32:24 PM

additionally:

=SUM(A1:A10)&" is the sum"

10/24/2005 4:00:51 PM

=ROUND(SUM(A1:A10),2)&" is the sum"

10/24/2005 4:18:24 PM

good thread

10/24/2005 5:50:21 PM

my fav:

="Clinical Management ("&TEXT(IF(G$4=0,0,G5/(G$4+G$5)),"0.0%")&" of Clinical)"

10/24/2005 11:35:13 PM

I love array formulas. This quickly counts the number of cells whose values are between 11 and 20

{=SUM(($J$1:$J$23>=11)*($J$1:$J$23<=20))}

10/24/2005 11:38:52 PM

^ I think you meant

=SUMPRODUCT(($J$1:$J$23>=11)*($J$1:$J$23<=20))

10/25/2005 4:15:04 AM

I guess you can do it that way also, but array formulas make you look l337

10/25/2005 7:49:47 AM

oh, i'll be damned. I wasn't aware of "array fomulas". I put in the fomula you provided and got a #VALUE error. Then I read
http://www.cpearson.com/excel/array.htm
and saw that you enter a normal formula using an array instead of a single value (which would normally give you an error), then press CTRL+SHIFT+ENTER instead of just ENTER, then it will do the operation on the whole array and enclose the formula in curly braces.

i really really wish I had figured that out earlier.

10/25/2005 9:13:59 AM

does anyone know how to sort a bus like this:

bus<0>
bus<1>
...
bus<8>
bus<9>
bus<10>
bus<11>

instead of this:

bus<0>
bus<1>
bus<10>
bus<11>
bus<2>
bus<3>
...

10/25/2005 9:22:53 AM

bus?

10/25/2005 9:45:22 AM

assuming it was in an excel column

10/25/2005 9:47:02 AM

you could extract the numbers into an adjascent column, then sort the two columns based on the numbers column, then hide/delete the numbers columns.

A formula like this would extract any number of digits between the < > and turn it into a sortable number

=VALUE(MID(A1,FIND("<",A1)+1,FIND(">",A1)-FIND("<",A1)-1))

10/25/2005 9:49:57 AM

but even if i had the column of sortable numbers, how would i sort them the way i described?

10/25/2005 9:50:45 AM

you would highlight both columns and do a normal sort because the way you describe how you want it is how a normal numerical sort would do it.

.... unless you're asking how to do any sort? In that case, highlight the columns and select Data > Sort > Sort by.... etc.

[Edited on October 25, 2005 at 10:05 AM. Reason : .]

10/25/2005 10:05:06 AM

ohhh right right - i was still thinking it was a text sort, my bad

10/25/2005 10:21:09 AM

Quote :

"oh, i'll be damned. I wasn't aware of "array fomulas". I put in the fomula you provided and got a #VALUE error. Then I read http://www.cpearson.com/excel/array.htm and saw that you enter a normal formula using an array instead of a single value (which would normally give you an error), then press CTRL+SHIFT+ENTER instead of just ENTER, then it will do the operation on the whole array and enclose the formula in curly braces. i really really wish I had figured that out earlier. "

Using this right now with some data manipulation stuff, it's awesome.

10/25/2005 1:07:40 PM

damn never knew u could do that either - that is sick.

10/26/2005 12:26:17 PM