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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,628
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
You can change the format in text to what you want I used on decimal place

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">90-300 pounds</td><td style="text-align: right;;"></td><td style=";">40.8-136.1 kg</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C1</th><td style="text-align:left">=TEXT(<font color="Blue">CONVERT(<font color="Red">LEFT(<font color="Green">A1,SEARCH(<font color="Purple">"-",A1</font>)-1</font>),"lbm","kg"</font>),"0.0"</font>)&"-"&TEXT(<font color="Blue">CONVERT(<font color="Red">MID(<font color="Green">SUBSTITUTE(<font color="Purple">A1," ",REPT(<font color="Teal">" ",99</font>)</font>),SEARCH(<font color="Purple">"-",SUBSTITUTE(<font color="Teal">A1," ",REPT(<font color="#FF00FF">" ",99</font>)</font>)</font>)+1,99</font>)+0,"lbm","kg"</font>),"0.0"</font>)&" kg"</td></tr></tbody></table></td></tr></table><br />
 

FMash

New Member
Joined
Jan 26, 2018
Messages
2
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
 

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,628
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Forum statistics

Threads
1,143,677
Messages
5,720,257
Members
422,273
Latest member
linds75

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
Top