LinkBack URL
About LinkBacks
Hi all you helpful people!
Can anyone give me a formula to sort ages into the following bands please;
0-17, 18-64, 65-74, 75-84, 85+
Have loads of data and can get 3 to work but not 5 !
waiting patiently !
Thanks for your help in advance
Hi all you helpful people!
Can anyone give me a formula to sort ages into the following bands please;
0-17, 18-64, 65-74, 75-84, 85+
Have loads of data and can get 3 to work but not 5 !
waiting patiently !
Thanks for your help in advance
Hi
Could you upload small worksheet with arrangement of your data and expected results?
Regards
tom1977
If You are satisfied with my solution click the small star icon on the left to say thanks.
Current Required output for each row
Age age band
62 18-64
78 75-84
48 18-64
69 65-74
85 85+
Last edited by Help999; 05-11-2012 at 06:34 AM. Reason: clarity
Sorry I still do not follow your problem Could You made excel sample with before and after results?
Help999 - it is obviously too much trouble and you would be "wasting too much time" to explain your problem fully so why do you expect others to drag it out of you? I'm sure you will have read the rules but just in case you missed this part here is an extract.
"Ensure your question is not too vague. Don't assume anyone is familiar with your problem. While you can upload small attachments, describe your problem in the body of the post. We are fortunate to have several Excel gurus, but few mind-readers".
If I helped, please don't forget to add to my reputation. (click on the star below the post)
If the problem is solved, please: Select Thread Tools (on top of your 1st post) -> Mark this thread as Solved.
Failure is not falling down but refusing to get up.
Sorry all I wanted was a bit of quick help, shall unsubscribe now if it caused a problem
If you need quick help, you need to help others help you. By providing a sample workbook and explaining your question well, you would have received the best of help in the least of time.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
I have found an almost identical request and the answer from a posting in 2008 by searching the internet with the same question that was posted. Oddly enough the solution was from this forum.
http://www.excelforum.com/excel-gene...d-formula.html
OP should be able to make necessary adjustments to suit without wasting too much time.
Posted this despite the unsubscribe threat/promise to enable others to resolve the same question.
I think you guys unnecessarily jumped on the OP on this. The question was not vague and the sample set was good. I am not sure why you badgered the new OP, especially coming from a Moderator!
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
NBVC, i only pitched in at the end. Didnt intend to drive him away.
It was me - Arlette was pouring oil on troubled waters. They were asked twice for a workbook. Anyway, I gave the solution. Tomorrow I will get out of the other side of the bed!
Not everything needs a sample workbook! We get to used to seeing sample workbooks here that we don't even bother to try to read the question clearly first to see if it can be solved without necessarily seeing a sample workbook.
Sample workbooks are required sometimes, when the issue is truly unclear... but not always!
....Or, if our English are not enough to understand what OP, asked for...!
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Maybe you're right, Fotis
Wouldn't it have been simple to just offer:
=IF(A2<18,"0-17",IF(A2<65,"18-64",IF(A2<75,"65-74",IF(A2<85,"75-84","85+")))) or the LOOKUP() alternative
and let the OP accept it or provide clarification?
Since this thread has gone somewhere in a hand basket I have to ask Cutter: How does your solution work? When I look at it you have a whole lot of <'s and yet every number I type in is put into the proper range. How does your formula know that: A2 that is less than 18 doesn't go into the 75-84 range when that statement says As is less than 85, which could be any number. Blacsh, sheet side formulae makes my eyes water, and your solution isn't even as crazy as some of the schtick I have seen pop up here in the last couple of years.
If you're happy with someone's help, click that little star at the bottom left of their post to give them Reps.
---Keep on Coding in the Free World---
With the nested IF's, the formula "stops" evaluating after the first TRUE it encounters... and only goes to the end if no TRUEs for found on the way...
I haven't been on the forum today, but I do this sort of thing all of the time (the joys of market research data).
Anyway, I use:
=CHOOSE(MATCH(A1,{0,18,65,75,85},1),"0-17","18-64","65-74","75-84","85+")
Mordred, use the Formula Evaluation tool and you will see. The short answer is that the IF function is a branch in logic. IF this, then that, else the other. If the condition is true, it doesn't even look at the Else side of thigs. So, because of the way Cutter sorted the possible results, you cannot have a number that is less than 18 that reaches the test to see if it is less than 84.
because the IF tests stop as soon as one of them is met. (that's why you have to test the lowest band first)
daddylonglegs' lookup formula seems easiest to me
Josie
if at first you don't succeed try doing it the way your wife told you to
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks