How do I add age range
I am trying to take a list of all employees that includes their info like department, name, and age and then create a age range (like 16-24, 25-34, etc...) so I can then count how many employees fall under each age range.
I have tried two ways and neither work.
FIRST ATTEMPT:
-The first and i think easiest is to have a helper sheet that contains my age range i want to use. That way on my main sheet i can use a vlookup formula to pull in the range result.
-here is my main sheet with the helper column called 'age group'. and the formula being used:
=VLOOKUP(Age@row, {HR DASH - HEADCOUNT (METRICS) Range 2}, 2, true
-and in that formula, here is the cross reference being used with my age range
SECOND ATTEMPT:
The other idea I had, was to pull in counts on a helper sheet. But the formula keeps coming up with 0:
same main sheet as shown above
here is the helper/metrics sheet and the formula being used:
=COUNTIFS({Hr Age Range}, AND(@cell >= 16, @cell <= 24), {Hr Headcount by line of business}, [Line of Business]@row)
here are the cross references:
Answers
-
Hi
Your first attempt looks good. You're missing a closing parenthesis but I think that is from pasting into here and not in your formula otherwise you'd see a different error. And your first row in the look-up should be 0-15 which is entirely unrelated and irrelevant but shows the level of scrutiny I have put your formula through. 😉
I am wondering if the parenthesis in the cross-sheet reference is an issue. I have had problems before. It seems to be OK if you name a reference without parentheses and then rename it to include them, but if you go straight in with the parentheses in there it falls apart. I just try to avoid them.
I tested it out, and it works for me.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!