Need Help on Calculating work Experience range

[alfred]

hi to all. need help on my project. i don't know what formula to used in calculating the range of experience.

the form is like this:

Years of Experience:
Below 1 yr. _____
1-2 yrs. ____
3-4 yrs. ____
5 yrs. & above. _____

is it possible that when i put the data on the cell like for example in cell A1 "6 months" or " 1 year & 5 months"
it will appear on the tally that i show above?
i try the formula for COUNTIFS($J$14:$J$38,">=1",$J$14:$J$38,"<=2") it is okey but when i in terms of months it will not count.
please help me. it will give great help on my job.
i attached the screen shot on my project...
thanks in advance. sorry for my English. i'm not good. God Bless!

[Sisyphus]

Hello Alfred,
Excel can't process numbers expressed in different units within the same cell, such as 1 year 6 months. You must decide on a single unit, I.e. 1.5 years or 18 months. The third solution would be to offer separate cells for years and months.

Depends, of course, what you want to do with the entries. If you don't want to add them up or use them for some other calculations it doesn't matter what you put in the cells. However, your column caption says "years", and therefore entering months would appear wrong.

[alfred]

Hi Sisyphus,

thanks for the response on my post.i know it is not too much to ask. but i really need help on this, i don't know what formula what i'm going to use.

can you give me an example of formula or what formula to calculate single unit for years and months.
thank you so much.

Have a good and nice day ahead Sisyphus,

[Sisyphus]

Hello alfred,
I could and I will, but you need to be more precise.
You have posted a workbook. Please indicate the cell reference where you want a formula.
Then describe exactly what the formula should calculate.
I will then provide the formula, or some one else here will be equally glad to do that.

[alfred]

Hi Sisyphus,

Thank you, i attached my sample work in excel. Please see. The only problem in my Sample format is the years of experience.

Example the data i entry on the cell m14 is 1.5 years and it will automatically count on the cell L9 = 1 and if another entry is the same it will add and become to 2.

hehe. please see my attached file in order you too understand what i mean because my english is so limited.
Please forgive me, i delete some details on my sample program.

thank you so much and have a good day

[Sisyphus]

Hello Alfred,
I see. If you wish to have a calculated result in M14 you should change the cell's format to take a number. Once you have a number there you can evaluate the result as numbers and your formulas in L7:L11 will work correctly.

Format/Cells -> Number => Custom. Enter this formula in the Type field:

Code: Select all

[=1]0.0 "year";0.0 "years"

The effect of this format is that, if you enter 1 (just the number!) in the cell it will be displayed as 1.0 year, but if you enter any value other than 1 you will see the plural s, like 1.5 years.

[alfred]

Good day Sisyphus,

Thank you so much for your help and sharing your knowledge, i'ts one way of Gloryfiying God. Please continue helping others that needed help.

I'ts Works perfectly.

Once again thank you so much Sisyphus GodBless!