rynop All American 829 Posts user info edit post |
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 |
Excoriator Suspended 10214 Posts user info edit post |
good call 10/24/2005 3:32:24 PM |
tl All American 8430 Posts user info edit post |
additionally:
=SUM(A1:A10)&" is the sum"
but I can't manage to cut the # of decimal places down
[Edited on October 24, 2005 at 4:01 PM. Reason : ]10/24/2005 4:00:51 PM |
agentlion All American 13936 Posts user info edit post |
=ROUND(SUM(A1:A10),2)&" is the sum" 10/24/2005 4:18:24 PM |
Excoriator Suspended 10214 Posts user info edit post |
good thread 10/24/2005 5:50:21 PM |
YOMAMA Suspended 6218 Posts user info edit post |
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 |
joe17669 All American 22728 Posts user info edit post |
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 |
agentlion All American 13936 Posts user info edit post |
^ I think you meant
=SUMPRODUCT(($J$1:$J$23>=11)*($J$1:$J$23<=20)) 10/25/2005 4:15:04 AM |
joe17669 All American 22728 Posts user info edit post |
I guess you can do it that way also, but array formulas make you look l337 ] 10/25/2005 7:49:47 AM |
agentlion All American 13936 Posts user info edit post |
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 |
Excoriator Suspended 10214 Posts user info edit post |
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 |
qntmfred retired 40810 Posts user info edit post |
bus? 10/25/2005 9:45:22 AM |
Excoriator Suspended 10214 Posts user info edit post |
assuming it was in an excel column 10/25/2005 9:47:02 AM |
agentlion All American 13936 Posts user info edit post |
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 |
Excoriator Suspended 10214 Posts user info edit post |
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 |
agentlion All American 13936 Posts user info edit post |
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 |
Excoriator Suspended 10214 Posts user info edit post |
ohhh right right - i was still thinking it was a text sort, my bad 10/25/2005 10:21:09 AM |
Surfty All American 570 Posts user info edit post |
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 |
rynop All American 829 Posts user info edit post |
damn never knew u could do that either - that is sick. 10/26/2005 12:26:17 PM |