Converting range in pounds to kilograms

FMash

New Member
Joined
Jan 26, 2018
Messages
2
Hi,
I am trying to convert pounds to kilograms in a column. I know how to do that for a single number =CONVERT(1,"lbm","kg"). My question is there a way to do a range of number? From 90-300 pounds.
The sheet I am creating is for medical purposes and individuals weight varies.


Thank you
FMash
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
You can change the format in text to what you want I used on decimal place


Excel 2010
ABC
190-300 pounds40.8-136.1 kg
Sheet1
Cell Formulas
RangeFormula
C1=TEXT(CONVERT(LEFT(A1,SEARCH("-",A1)-1),"lbm","kg"),"0.0")&"-"&TEXT(CONVERT(MID(SUBSTITUTE(A1," ",REPT(" ",99)),SEARCH("-",SUBSTITUTE(A1," ",REPT(" ",99)))+1,99)+0,"lbm","kg"),"0.0")&" kg"
 
Upvote 0
So that converts the range yes. However, I am looking for something that can set up a column. Each cell within that column would automatically change the number entered(in pounds) to kilograms. So if I entered 150 (lbs) it would change to 68.03 automatically. Is there a way to set up a column to automatically multiply by 0.45? 1lb=0.4535
 
Upvote 0
To change the value in the cell that it was entered into you would need VBA.

Right click on the tab you want the code to run on and select view code and past the code below into the VBA editor.
change Target.Column = 1 to the column you want A=1, B=2....
I assume row one has headers so no number entered in row 1 is changed
you will need to save the file as a macro enabled file type like .XLSM



Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Column = 1 And Target.Row <> 1 And IsNumeric(Target)) Then
Application.EnableEvents = False
Target = Round(Target * 0.4535, 2)
End If
Application.EnableEvents = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top