Advanced Excel help requested

[Mobbs]

Hi folks,

Here's a new Excel dilemma!

I want to format cells so that a 1 will appear as 1st, a 2 as 2nd, etc etc

Anyone know the cell format code? Can't seem to find it in the custom options.

Thanks in advance!

Kev

 

[GOALden Hawk]

You can type in 1st and 2nd then do an autofill. But I don't think Excel actually considers it a number format as there is text.

 

[Mobbs]

Originally posted by GOALden Hawk
You can type in 1st and 2nd then do an autofill. But I don't think Excel actually considers it a number format as there is text.

Yeah I knew it would autofill, was sort of hoping that because of that, it might do it for me.

Shame, because Excel includes a 'rank' statistical formula function. 1st/2nd/3rd would come in handy.

 

[rfctigerarmy]

Umm I dont know if this is what your talking about but:

- Click top left of your spreadsheet (where theres no number or letter) eg between 1 and A

- Right Click ----> Format Cells

Yea is it there?

 

[Mobbs]

Originally posted by rfctigerarmy
Umm I dont know if this is what your talking about but:

- Click top left of your spreadsheet (where theres no number or letter) eg between 1 and A

- Right Click ----> Format Cells

Yea is it there?

Yep the format function is what I'm talking about ... but there isn't an option IN the formatting for having it list the way I want it.

 

[Malibu#27]

Originally posted by Mobbenfuhrer
Yeah I knew it would autofill, was sort of hoping that because of that, it might do it for me.

Shame, because Excel includes a 'rank' statistical formula function. 1st/2nd/3rd would come in handy.

Thats not to say you cant use a second column (hidden if you like), that has the cells containing the following function:

Assuming Column "A" contains your 1st,2nd,3rd etc, the following formula will return 1,2,3,4,5,6 in a second column:

=IF(LEN(A1)>0, LEFT(A1, LEN(A1)-2), "")

add this to row 1, and then paste it down the column. THen use this column when you want to use the numerical representation of 1st,2nd etc.


Altewrnatively, you can use a similar function to trim the trailing characters everytime you use a value from your column.

 

[Mobbs]

Thanks Malibu, I didn't even look at what you were talking about, BUT you inspired me to bite the bullet and just write a freakin' long-winded formula to handle it

=IF(RIGHT(A1,1)="1",IF(A1=11,A1&"th",A1&"st"),IF(RIGHT(A1,1)="2",IF(A1=12,A1&"th",A1&"nd"),IF(RIGHT(A1,1)="3",IF(A1=13,A1&"th",A1&"rd"),A1&"th")))

(Yay!)

 

[Mobbs]

Beautiful! Check this out :

2003	Ho	K	M	Ha	P	T	FF	FA	G	B
DE	7th	10th	10th	10th	10th	8th	10th	8th	10th	10th
Dingoes										
Dragons										
Furies	3rd	6th	4th	1st	3rd	8th	2nd	5th	2nd	4th
H'canes	8th	7th	8th	9th	9th	3rd	7th	10th	4th	5th
Titans	3rd	4th	6th	8th	6th	4th	5th	2nd	6th	7th
Junkyard	1st	3rd	2nd	6th	5th	1st	4th	7th	1st	2nd
Mt Buller	6th	5th	5th	4th	4th	1st	6th	4th	4th	6th
Razors	9th	8th	7th	7th	8th	7th	8th	9th	3rd	9th
Roys	2nd	1st	1st	3rd	1st	6th	1st	1st	6th	1st
Timmy										
Wonders	5th	2nd	3rd	2nd	2nd	8th	3rd	3rd	8th	3rd
WCM	9th	9th	9th	5th	7th	4th	9th	5th	9th	8th

( The stats function now requires two spreadsheets, totalling just over 10mb in size ... but what the hey )

 

[jim boy]

If you really want to get to the nitty gritty of it, have a look at http://www.cpearson.com/excel/ordinal.htm

 

[Mobbs]

Originally posted by Jim Boy
If you really want to get to the nitty gritty of it, have a look at http://www.cpearson.com/excel/ordinal.htm

That's off its nut! Jim yer a legend. That AND function is the best thing I've ever seen! EVER! Always been looking for something that does that so I didn't have to go if(if(if(if(if(if(if(if(if(if(if(if(if(if(if(if(if(if(if(if(if(if(if(if(if(if(if(if(if(if(if(if(if(if(if(if(if(if(if(

The MOD one looks pretty nifty too, don't understand it yet but I'll have a decko shortly.

I'm poffing my original attempt and replacing it with this one.

I've even learnt what ths and nds and rds are ... apparently they're called Ordinal Numbers. Never learnt that in HSC!

Maaaaaaaaaaaaaaaaaaate. I've got to go owwwwwwwwwwwwwwwwwwwww. Hello Jim (Heeeeellooo Jiiiiiiiiiiim). Thank you very much!

 

[Mobbs]

Check this out : http://www.primeconsulting.com/faqs/faq4650.html

One of the niftiest things I've ever found!

Onlyproblem is, I want it to dynamically calculate the number of SERIE, not the number of values IN the serie!

Got it this close to working, except have to retype the formula name into the Data Range of the chart every time. If I can just work out how to make it retain the reference to the formula name!