Age Group

[mithil1]

I have the ages mentioned in the sample file. I am trying to group them. For example f its 3 yrs 9 months then 3-4, 0 years 10 months then 0-1, 0 years 6 months then 0-1, 3 years 6 months then 3-4. so on and so forth.

What i did was copied and pasted that column in another sheet. Manually added the age groups in another column and then did a vlookup to get the age group in the orginal sheet.

What I am looking for is can how do I do IF statment. (IF age is >=0<=1,"0-1",If (<=2,>=3,"2-3)

 

[bosco_yip]

Maybe……….

1] In Column A housed the data

2] B2, formula copy down :

=0+LEFT(A1,2)&-LEFT(A1,2)-1

Regards
Bosco

 

[SirJB7]

Hi, mithil1!
Another option: same as above but not "0+" required.
B2: =IZQUIERDA(A1;1)&(-IZQUIERDA(A1;1)-1) -----> in English: =LEFT(A1,1)&(-LEFT(A1,1)-1)
And a question: 2 Yr 0 Mo should display 2-3 or 1-2?
Regards!

 

[mithil1]

Hi, mithil1!
Another option: same as above but not "0+" required.
B2: =IZQUIERDA(A1;1)&(-IZQUIERDA(A1;1)-1) -----> in English: =LEFT(A1,1)&(-LEFT(A1,1)-1)
And a question: 2 Yr 0 Mo should display 2-3 or 1-2?
Regards!

yes 2 yr 0 months 2-3 is fine. but its not just 1 age group I have several ages and many age groups.

 

[mithil1]

Maybe……….

1] In Column A housed the data

2] B2, formula copy down :

=0+LEFT(A1,2)&-LEFT(A1,2)-1

Regards
Bosco

Thanks Bosco!

 

[mithil1]

This works.

But when I use use the sytanx which you all gave me, for example I get 3-4 which is correct. What I dont understand is where does it get 4 from for age 3 yr 8 months. because Left(A1,1) would be 3 and then were are doing -1 which is 2 so how does it display 3-4. Further, if I remove they symbol - after& then it becomes 32. I know were are concatenating that part.

 

[mithil1]

Oh I think I got it. is it because we are using -ve sign before LEFT and then -ve 1 adds to -4 so it becomes 3-4

 

[bosco_yip]

Hi, mithil1!
Another option: same as above but not "0+" required.
B2: =IZQUIERDA(A1;1)&(-IZQUIERDA(A1;1)-1) -----> in English: =LEFT(A1,1)&(-LEFT(A1,1)-1)
And a question: 2 Yr 0 Mo should display 2-3 or 1-2?
Regards!

Hi SirJB7,

In the OP's attached file, A3: 12 Yr 7 Mo

Your formula result will give: 1-2, I think it should be 12-13

Regards
Bosco

 

[mithil1]

Maybe……….

1] In Column A housed the data

2] B2, formula copy down :

=0+LEFT(A1,2)&-LEFT(A1,2)-1

Regards
Bosco

Yes, Bosco, I checked. You are correct. I didn't realize this earlier. Thanks!!

So my next question is. I got lucky since yourself and Sir JB7 were here, but what if I wanted to figure this out my own? I don't think any excel classes would have helped. Please share how did you learn all about excel. My next goal is also to learn VBA.

 

[SirJB7]

Hi, bosco_yip!

In the OP's attached file, A3: 12 Yr 7 Mo

Your formula result will give: 1-2, I think it should be 12-13

You're right, thanks for the catch.

Hi, mithil1!
Try this, it'll work with any number of years:
=IZQUIERDA(A1;HALLAR(" ";A1))&(-IZQUIERDA(A1;HALLAR(" ";A1))-1) -----> in English: =LEFT(A1,SEARCH(" ",A1))&(-LEFT(A1,SEARCH(" ",A1))-1)
Regards!

 

[rahulshewale1]

Hiii,


Bosso sir formula very shot and simple....

Try ...
=+MID(A1,1,SEARCH("yr",A1)-1)&"-"&MID(A1,1,SEARCH("yr",A1)-1)+1

Thanks
Rahul Shewale

 

[bosco_yip]

Hiii,


Bosso sir formula very shot and simple....

Try ...
=+MID(A1,1,SEARCH("yr",A1)-1)&"-"&MID(A1,1,SEARCH("yr",A1)-1)+1

Thanks
Rahul Shewale

Hi Rahul Shewale,

As per the OP described in Post #1, his data may be in the format: of 3 years 6 months

Your formula will give #VALUE, I make a slightly revised of your formula as below :

=MID(A1,1,SEARCH("y",A1)-2)&"-"&MID(A1,1,SEARCH("y",A1)-2)+1

Regards
Bosco

 

[mithil1]

But why do we need such a complicate formula when Bosco's short works ? Also, for 12 year 7 months If I dont want to use 12-13 instead if I wnt to show as >6 what additions I need to make ?