[Solved] Changing a range of cell contents

Discuss the spreadsheet application

[Solved] Changing a range of cell contents

Postby coops1959 » Tue Apr 15, 2008 2:30 pm

Hi
I do hope someone can help me here.
I organise a running race, with the entrants details kept on spreadsheet.
All information in the spreadsheet comes directly via e-mail from each competitor, (cut and pasted into a .csv file).
My spreadsheet contains a column of data, named "Age Category".
Its contents are, M(male), or F(female), followed by their age, eg M47, F36, M52 etc
I want to change the information in this column into the recognised age groups for runners, which are:-
M = males under 40
M40 = males 40-44
M45 = males 45-49 etc, etc (multiples of 5 years)
F = females under 35
F35 = females 35-39
F40 = females 40-44 etc, etc

I hope I've explained that ok, and hope someone can help.
Kind regards
Last edited by Hagar Delest on Tue Jun 10, 2008 2:18 pm, edited 2 times in total.
Reason: tagged the thread as Solved.
coops1959
 
Posts: 11
Joined: Tue Apr 15, 2008 1:57 pm

Re: Changing a range of cell contents

Postby Villeroy » Tue Apr 15, 2008 2:33 pm

Are there any birth dates? If so, which format? 1999-12-31, 12/31/99, 31/12/99, 31.12.99, ...
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28667
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Changing a range of cell contents

Postby coops1959 » Tue Apr 15, 2008 2:41 pm

Hi Villeroy

I do get full information from the competitors, with a DoB, in dd/mm/yyyy format, but I do not, (at the moment), use this on the spreadsheet.
At present on the spreadsheet, I have the minimum info required, Name, Club, Age Category, e-mail (in case of race cancellation).
I could, if there was no easy way to do what I want with my current info, import the DoB as well.

Thanks for the speedy reply
coops1959
 
Posts: 11
Joined: Tue Apr 15, 2008 1:57 pm

Re: Changing a range of cell contents

Postby Villeroy » Tue Apr 15, 2008 2:44 pm

A1: F
B1: 47
X1: =$A1&FLOOR($B1;5)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28667
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Changing a range of cell contents

Postby coops1959 » Tue Apr 15, 2008 3:06 pm

Hi Villeroy

That works, brilliant thanks.
I did look at the floor function, but couldn't get the syntax right.
I originally had sex and age as two different columns, as per your example.
Before I had posted this question, I had combined the two cell columns and was trying to get "floor" to work with that.
Can that be done?
Apart from using "find and replace", can I change the results for all under male 40, eg M35 to M, M25 to M , (and females), F20 to F, etc?

Cheers
coops1959
 
Posts: 11
Joined: Tue Apr 15, 2008 1:57 pm

Re: Changing a range of cell contents

Postby Villeroy » Tue Apr 15, 2008 3:25 pm

As a general rule of thumb you shall not merge separate fields of information such as sex and age. You can calculate all kinds of aggregations and merges from separated values, but once you lost the separate fields, you can not get the tooth paste back into the tube easily.

=LEFT(X1;1) returns the first char of X1 as text value.
=VALUE(MID(X1;2;LEN(X1))) returns the numeric value from the chars behind the first one.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28667
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Changing a range of cell contents

Postby coops1959 » Tue Apr 15, 2008 3:35 pm

Yep I understand what you are saying.

The reason the two cells are merged into one, eg M45, is for the prizes in each age category.
I can then obviously sort, and pick out each of the category prizes for each age group.

Thanks a lot for your help.

Kind regards

I wrote the above, before I saw the extra formula in your last post, I'll read and digest now. ;)
coops1959
 
Posts: 11
Joined: Tue Apr 15, 2008 1:57 pm

Re: Changing a range of cell contents

Postby Villeroy » Tue Apr 15, 2008 3:38 pm

coops1959 wrote:Yep I understand what you are saying.

The reason the two cells are merged into one, eg M45, is for the prizes in each age category.
I can then obviously sort, and pick out each of the category prizes for each age group.

Thanks a lot for your help.

Kind regards

I wrote the above, before I saw the extra formula in your last post, I'll read and digest now. ;)

Keep the two separate constant values and derive a concatenation by means of formulas: =A1&B1
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28667
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Changing a range of cell contents

Postby coops1959 » Tue Apr 15, 2008 3:45 pm

Brilliant - two more formulas for my "little black book".

Thanks
coops1959
 
Posts: 11
Joined: Tue Apr 15, 2008 1:57 pm

Re: Changing a range of cell contents

Postby keme » Tue Apr 15, 2008 3:50 pm

Separating information of different kinds, like Villeroy suggests, is often useful.

Even so, if you want to keep the entered base data for classification in one single cell, here's an alternative to Villeroy's formula.
Assuming the classification code entered is in column A, and columns W through Z are free:
Extract male/female to column W, ensure capital letter:
W1: =UPPER(LEFT(A1;1))

Determine starting age for class division in column X:
X1: =IF(W1="M";40;35)
or if you want to catch entry errors:
X1: =CHOOSE(FIND(W1;"FM");35;40)

Extract entered age in column Y: (catching 3 digits in case you have some really senior participants...)
Y1:= N(MID(A1;2;3))

Assemble class specification in column Z:
Z1: =W1&IF(Y1<X1;"";FLOOR(Y1;5))

You could pull this together to one or two columns, but spreading it out like this saves a little work. (The results of W1 and Y1 are both used twice.) I also find it easier to trace the logic when formulas are segmented like this, but that may be a matter of taste.

Edit: I see there's been a bit of conversation while I was typing. My reference to Villeroy refers to his first suggestion. Although there's a solution above, you may find this useful too, so I leave it as is.
Last edited by keme on Tue Apr 15, 2008 3:58 pm, edited 1 time in total.
Apache OO 3.4.1/4.1.2/4.1.3, on Ms Windows 7/10 and Mac OS-X 10.8.5 and 10.11.
LibreOffice 4.3.3.2 on Mepis Antix MX 14
User avatar
keme
Volunteer
 
Posts: 3383
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Changing a range of cell contents

Postby coops1959 » Tue Apr 15, 2008 3:55 pm

keme

Thanks for that.
I'll try that out a bit later - I must do some paid work now
Damn work always gets in the way :lol:

I'll probably post tomorrow, out for a run tonight, club training night

Cheers
coops1959
 
Posts: 11
Joined: Tue Apr 15, 2008 1:57 pm

Re: Changing a range of cell contents

Postby coops1959 » Wed Apr 16, 2008 4:04 pm

Cheers keme, that works a treat.
Thanks also to Villeroy for all your help.

All formulas about to be added to my "little black book", if you don't mind, I'll never remember them.

Regards
coops1959
 
Posts: 11
Joined: Tue Apr 15, 2008 1:57 pm

Re: Changing a range of cell contents

Postby keme » Thu Apr 17, 2008 12:56 am

coops1959 wrote:Cheers keme, that works a treat.
[ ... ]
You're welcome to it. Glad I could help (and I'm sure that I can say the same for V. too)
coops1959 wrote:All formulas about to be added to my "little black book", if you don't mind, I'll never remember them. [ ... ]
Keep your black book if you must, but I can't say I don't mind. Not for copyright reasons, but because the cookbook approach is not very useful. You're not likely to need that exact set of formulas ever again.

Rather than regarding the formulas as "magic spells", try to understand what they really mean. You can use the help within the application to get an explanation of each function used in the formulas. The moment you figure out what that FIND() is doing inside the CHOOSE(), and why Villeroy used MID() when there's a RIGHT() function with a simpler syntax, you'll be throwing the book away. Add to that Villeroy's sound advice on data separation and toothpaste, and you'll soon be a proficient Calc user.
Apache OO 3.4.1/4.1.2/4.1.3, on Ms Windows 7/10 and Mac OS-X 10.8.5 and 10.11.
LibreOffice 4.3.3.2 on Mepis Antix MX 14
User avatar
keme
Volunteer
 
Posts: 3383
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway


Return to Calc

Who is online

Users browsing this forum: No registered users and 25 guests