How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Richard Your Own Question
Richard
Richard, Software Specialist
Category: Microsoft Office
Satisfied Customers: 35380
Experience:  Over 15 year experience resolving Microsoft Office Issues
32989067
Type Your Microsoft Office Question Here...
Richard is online now
A new question is answered every 9 seconds

How do i convert a series of numbers in a column into another

Customer Question

How do i convert a series of numbers in a column into another column with ranges. for example
6.1 to 5-10
1.4 to 0-5
12.2 to 10-15
35 to >30
Submitted: 1 year ago.
Category: Microsoft Office
Expert:  Richard replied 1 year ago.
Thank you for your question, my name is ***** ***** I look forward to assisting you.
Please note, click on Reply to Expert to reply to me, a rating is only needed when we are finished.
So you mean if it has 6.1 then in the column it should show 5-10 as the text?
Customer: replied 1 year ago.
yes
Expert:  Richard replied 1 year ago.
ok, can I have your excel file to write the formula correctly for you please?
Go to http://ge.tt
Upload the file and provide me the download link
That way I know my formula works in accordance with the formatting you have.
Customer: replied 1 year ago.
I'm uploading the file. Do I need your email address?
Customer: replied 1 year ago.
http://ge.tt/2kAJiYK2/v/0?c
Expert:  Richard replied 1 year ago.
thanks, ***** ***** it now for you
Expert:  Richard replied 1 year ago.
ok, I get the idea and it can be done, just need clarity on the rules.
you say
6.1 to 5-10
1.4 to 0-5
12.2 to 10-15
35 to >30
But you do not mean only 6.1 right? As you have many other numbers
Is it not like 0 to 6.1 then make in the column 5-10?
Like a range?
Can you specify the ranges please
Customer: replied 1 year ago.
I will have a bunch of numbers ranging from 0 to 100 in column c. In column D I want to have the range. My ranges are as follows:0 - 5 yrs
5 - 10 yrs
10 - 15 yrs
15 - 20 yrs
20 - 25 yrs
25 - 30 yrs
> 30 yrsSo in column D beside each value in C will be the corresponding range.Does that make sense?
Expert:  Richard replied 1 year ago.
yes, just need to know how to match, you missed that so eg
Column C is between 1 to 5 then columne D has 0 - 5 yrs
5 - 10 yrs
10 - 15 yrs
15 - 20 yrs
20 - 25 yrs
25 - 30 yrs
> 30 yrs
I need to know when to display the values
Customer: replied 1 year ago.
yes, that's correct. if value is between 0.1 and 5 the 0-5 yrs. if 6.1 the 5- 10 yrs. if NA then NAthis is part of a much larger spreadsheet so I am hoping you can avoid nested ifs that slow down the spreadsheet calc.
Customer: replied 1 year ago.
Also need to add NA to the list of ranges
Expert:  Richard replied 1 year ago.
No, it will have to be nested ifs, as you have a nested lookup.
Or, I can make it a Macro, but then you need to click a button for it to run, and do this each time values change. They will be faster.
Which solution would you like?
Customer: replied 1 year ago.
no other solution? will the nested ifs running in each cell in a very lengthy column be taxing on the spreadsheet?
Expert:  Richard replied 1 year ago.
No, only a formula or code.
No, they wont be very taxing, its not a really big formula
Customer: replied 1 year ago.
OK. lets go with nested ifs
Expert:  Richard replied 1 year ago.
ok, give me some time to develop please
Expert:  Richard replied 1 year ago.
Hi Andrew,
I have the sheet for you here
http://ge.tt/9jtstYK2/v/0?c
If you have any problems, or would like any additional information or assistance, please do not hesitate to let me know.
Else if you can take a second to rate my service by clicking one of the stars at the top of the screen then submit, that would be greatly appreciated.
Thank you
Customer: replied 1 year ago.
thanks
Expert:  Richard replied 1 year ago.
Your welcome.
Let me know if there are any problems, else please remember to do the service rating.
Thank you Andrew
Richard

Related Microsoft Office Questions