• 100% Satisfaction Guarantee
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

# 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
That way I know my formula works in accordance with the formatting you have.
Customer: replied 1 year ago.
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.