Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: Changing all numbers in excel?


(!)

ajrobson's Avatar
ajrobson ajrobson is offline
Member with 277 posts.
THREAD STARTER
 
Join Date: Aug 2006
27-Dec-2008, 06:44 PM #1
Question Solved: Changing all numbers in excel?
Can anybody help with this question? I have a massive spread sheet with numerical data in it. on paper I have associated a number to different ranges.

For example in columb A any number that is between 25-30 needs to be converted to 0.375. Can anyone tell me a quick way to convert these and other numbers in one go?
Rollin_Again's Avatar
Member with 4,698 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Advanced
27-Dec-2008, 06:55 PM #2
You will need a macro to do this. I can write it for you if you can post a sample workbook with detailed instructions on what you want. If you prefer not to post it in the forum you can email to Rollin_Again at hotmail dot com

Another option is to use a helper column with a formula consisting of a series of nested IF functions. The macro is probably a better option since there is a limit to the number of nested IF functions you can use.

Regards,
Rollin

Last edited by Rollin_Again; 27-Dec-2008 at 07:20 PM..
ajrobson's Avatar
ajrobson ajrobson is offline
Member with 277 posts.
THREAD STARTER
 
Join Date: Aug 2006
27-Dec-2008, 07:12 PM #3
There is quite a bit I still need to do, I assume if I saw one macro I could chnage the numbers to work with the others?

If you can could you write a macro that would look at all the numbers in columb B (which are betwwen 15.17-76.75) and chnage them on this criteria

Between Chnage to
15- 17... 0
18-20... 0.125
21-23... 0.25
24-29... 0.375
30-33... 0.5
34-43... 0.625
44-59... 0.75
60-70... 0.875
71+ ... 1
Rollin_Again's Avatar
Member with 4,698 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Advanced
27-Dec-2008, 07:23 PM #4
Give me a few minutes to put some basic code together. It would probably be best to put the new values in a "helper" column instead of changing the original data so that you can compare the values to verify that the macro processed the data correctly. You can then just delete the original column and keep the "helper" column instead.

Regards,
Rollin
ajrobson's Avatar
ajrobson ajrobson is offline
Member with 277 posts.
THREAD STARTER
 
Join Date: Aug 2006
27-Dec-2008, 07:28 PM #5
Thank you
Rollin_Again's Avatar
Member with 4,698 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Advanced
27-Dec-2008, 07:38 PM #6
Here is the macro code. It should be pretty easy for you to understand. I've also included a sample workbook so you can see the code in action. Just save the workbook and then run the macro called ConvertNumbers

Code:
Sub ConvertNumbers()

Dim vRange As Range

'Set your range of cells to process
'You can also set the range dynamically using code 
Set vRange = Range("B1:B50")

For Each vValue In vRange

Select Case vValue

Case 15 To 17
vNewValue = 0

Case 18 To 20
vNewValue = 0.125

Case 21 To 23
vNewValue = 0.25

Case 24 To 29
vNewValue = 0.375

Case 30 To 33
vNewValue = 0.5

Case 34 To 43
vNewValue = 0.625

Case 44 To 59
vNewValue = 0.75

Case 60 To 70
vNewValue = 0.875

Case Is > 71
vNewValue = 1

End Select

'Place new value in adjacent cell - column C
'If you want to replace the original value change to column B
Range("C" & vValue.Row).Value = vNewValue

Next vValue

End Sub
Regards,
Rollin
Attached Files
File Type: xls ConvertNumbers.xls (24.0 KB, 157 views)
ajrobson's Avatar
ajrobson ajrobson is offline
Member with 277 posts.
THREAD STARTER
 
Join Date: Aug 2006
27-Dec-2008, 07:56 PM #7
Thank you I will mess around with that in the morning and hopefully get it sorted. Thanks again!
ajrobson's Avatar
ajrobson ajrobson is offline
Member with 277 posts.
THREAD STARTER
 
Join Date: Aug 2006
29-Dec-2008, 08:14 PM #8
Thank you Rollin_Again your macro worked perfectly
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


Tags
convert, excel, formula, numbers, range

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑