Converting From Kilos To Stones And Pounds

Jul 19, 2007

I'm trying to write a spreadsheet that compares body weights for older people in the UK who are not at ease with metric figures. I've searched the board and have come up with several answers, but they don't seem to work for me.

For instance, cell A1 is kilos, and I'd like that weight in stones and pounds in cell A2. What I've come up with for cell A2 is this:

=ROUND((A1*2.2046)/14,)&"st " &ROUND(MOD((A1*2.2046),14),0)&"lbs"

This works for, let's say, 90.8kgs in A1 (answer: 14st 4lbs), but if I enter 87.6kgs, for example, I get an answer of 14st 11lbs. I think it's something to do with the "ROUND" function but I'm struggling. Any help would be much appreciated. Thanks.

By the way, for non-Imperial weight people, multiplying by 2.2046 converts kilos to pounds, and there are 14 pounds (lbs) in a stone.

Converting Euros To Pounds

Jan 27, 2010

I have a large spreadsheet that I need to convert the euros amount to the equivalant in pounds in various places throughout the spreadsheet.

Calculate Total Amount Of Kilos For A Specific Date With Given Time Range

Mar 26, 2014

I am trying to calculate the total amount of kilos for a specific date with a given time range.

As well as the average time they have been handled with in the same specifications.

Attached is a sample sheet of the info I am working with but I cant seem to get the formulas to work.

Book1.xlsx

Using GBP And Pounds / Shillings As Currencies?

Dec 29, 2010

I am creating a simple spreadsheet to catalog some magazines. One of the columns shows the cover price which is okay for post 1970 magazines as it is in Pounds Sterling, but is there anyway I can get Excel to recognize Pounds/Shillings/Pence as currency. So put in "8d" for example without setting the field to a text format?

I want to be able to add up the total cost of some items based on their cover price but if I set the required cells to text format I cannot do this automatically using the Sum forumla.

Is it possible to have both Pounds Sterling and Pounds/Sillings/Pence as currencies in the same table or is using a text field for the latter the only option?

Pounds To Kg Separation In Combined Data

Aug 19, 2013

I receive a monthly spreadsheet from our trucking company which tracks the package and weight of that package. However I need to pull the weight out and run a report on total weight. However it is a mix of pounds and kilograms. Please see my example. What I want is a final column of all weight converted to kilograms. Note as well the spacing in the data as sometimes the "lbs" is right next to the number and other times there is a space between them.

Raw Data

package 1
10.2kgs

package 2
5 kgs

[Code] ........

Sum Of Pounds And Ounces For Fishing Tournament

Jan 7, 2009

I need a spreadsheet to calculate the sum of pounds and ounces. Example
3 fish caught weighing 5lb 8oz plus 3lb 8oz plus 2lb 8 oz = 11lb 8Oz.

This calculation is when the weigher has a scale that measures in OZ.
I also need a spreadsheet that will take a scale that measures in 10ths to add lb and 10th and give a result in lb and oz?

Formula To Subtract Pounds & Ounces

Jun 29, 2009

I need to build a spreadsheet that subtracts from a cumulative weight recorded in pounds and ounces. For example, in a fishing tournament, a person has 5 fish to weigh. They place their fish one at a time in a bag hanging from a digital scale that reads in pounds and ounces, and record the cumulative weight as the fish are added. We want to know what the total weight is of all 5 fish, but also what the individual weight is of each of the 5 fish.

The easy way to do it is to place the first fish in the bag and record the weight, then place the second fish in the bag with the first fish and record the cumulative weight of the 2 fish together. Continue this until all 5 fish are in the bag, and record the final total weight.

Then, to get the weights of each individual fish, the first weight is as it is, the second fish weight would be Cumulative Weight recorded for Fish 2 minus the weight of Fish 1. To get the weight of Fish 3, you would subtract the Cumulative Weight for the first 3 fish from the Cumulative Weight of the first 2 fish, and so on.

For example:

Weight of the Bag with:
1 Fish: 1-lb 2-oz
2 Fish: 2-lb 14-oz
3 Fish: 5-lb 6-oz
4 Fish: 9-lb 7-oz
5 Fish: 11-lb 9-oz.

We can subtract the cumulative weights to determine the individual weights of each fish added to the bag and know that:

Fish 1 is 1-lb 2-oz
Fish 2 is 1-lb 12-oz
Fish 3 is 2-lb 8-oz
Fish 4 is 4-lb 1-oz
Fish 5 is 2-lb 2-oz

Easy to do in my head, or on paper, but not so easy to do in Excel because it's pounds and ounces, which is how the digital scale reads out. But, when you're doing this for 20-30 fishermen, it's not that easy to do it on paper.

Can Split Currency Into Two Separate Columns Showing Pounds And Pence?

Dec 1, 2012

I have a mileage expenses form from work that I am trying to fill in using Excel, instead of filling it in by hand.

Column R shows the miles travelled.

Column S shows the Rate per Mile.

Column T (should) show the pounds.

Column U (should) show the pence.

Searching has led to T11, for example, using the formula below to show the pound value.

Code:
=INT(R11*(S11/100))

However, I am having less luck on finding a way to display the pence amount in U11.

Ideally, I would like the cells in column U to show the pence without a leading zero or decimal point and just the pence value only.

Converting XLS To CSV

Jul 29, 2014

I have an excel table .xls : 1.jpg

And I have to convert it to .csv (as the only applicable format for a database mass upload) , but when I do this , all the 0 values (numbers) from the NEW ZIP POSTAL table disappear.

If I save it : 2.jpg

Converting 10.27M To 10,270,000...

Jan 12, 2009

The issue is that there are six types of "numbers" in the table that I am trying to turn into all numbers. Obviously, I'd like to turn the Ks in 1,000s and the Ms into 1,000,000s:

#.##K
#.#K
#K
#.##M
#.#M
#M

If there weren't all six, I think I could fix this pretty easily, but as it is I'm pretty stumped (other than a manual fix).

HH:MM:SS Converting..

Mar 24, 2009

I am currently exporting data into excel. The data i am exporting comes in the form of hh:mm:ss. When i export it into excel...everything appears fine. I am also using the data to generate charts. I have multiplied the values by 1440 in order to show as minutes for the charts.

When i import the data to excel and the hours value is bigger than 24, for some reason excel converts it into totally different figures. The minutes caluation (*1440) still works fine, but i need the actual hh:mm:ss to be correct, not amended by excel. Is there a way to do this. Ex. Data imported: 74:51:03 in hh:mm:ss - excel coverts this to 02:51:03.

Converting XLS To Zip?

Nov 29, 2011

I have put VBA code which will winzip all the excel files to .zip files in a folder. But problem is when i run the code it zips the file as .xls.zip.

For example:-

Sub Zipping()
'
Dim file As Variant
Const SOURCE = "E:Output"

[Code]....

So the output is :- abc.xls gets converted to abc.xls.zip

Converting 50% To 1.5

Aug 29, 2007

I have some code where it prompts you to enter a %, and then it calculates if the Lst Wk Sales > Avg Wkly Sales + %.

box2 = InputBox("Enter % increased by Sales")
ActiveCell.FormulaR1C1 = "=IF(RC[-111]>RC[-121]+" & box2 & ",""YES"",""NO"")"

The problem I have is that excel treats the formula as +50% rather +1.5. Can anyone help me around this issue?

Converting Hh:mm:ss To AM

Mar 29, 2009

I am working within Excel and am hoping to convert from A LOT of columns containing hh:mm:ss to AM at the end, however I am unsure of a fast / efficient way to do this. I am using MS Office Excel 07....

Converting To .Exe

Sep 15, 2006

I was wondering if you can convert a program you have made on excel into an EXE. I then wanted to give trial period evaluation copies out, plus secure the program to a specific PC.

Converting Sumif To VBA

Nov 26, 2013

I know this is a dumb question but I can't get a sumif to work in vba. I want the actual formula in the cell, not just the output value.

This is my most recent attempt:

VB: Range("C2").Formula = "=SUMIF(Z7:Z12000, >0,N7:N12000)"

I get Run-time error 1004, application defined or object defined error.

Why doesn't this work?

Formula Converting Hh:mm To 00:mm?

Jun 9, 2014

I need a formula that converts time in the format of hh:mm to 00:mm. I only want the minutes.

Converting Date Getting #VALUE

Nov 14, 2008

I have an export from a database application in which I have thousands of dates. The dates are not in a date format (normal problem) so I wrote a formula to convert them as follows.

Original Date in A1 : 11/13/2008 This is mm/dd/yyyy

Formula : =VALUE(MID(A1,LEN(A1)-6,2)&"/"&LEFT(A1,LEN(A1)-8)&"/"&RIGHT(A1,4))

Result : 13/11/2008

This seems to work at first, until I looked more closely. If I change the date I am converting so that the date in cell A1 is 11/12/2008, ie. the dd value is less than 13, then the formula no longer works. It is fine for all values over 12.

All I get in these cases is #VALUE!

Converting A Database?

Dec 12, 2012

Using an existing database with nonfixed boundaries I am creating a new one with uniform boundaries.
The simple spreadsheet I will use in this comment isn't what the database actually looks like but represents what I am trying to achieve.

StartFinishAssigned #
0151
15252
25403
40552
55602
60804
80953
951002

Here I have the old data, organised into sections that have the same assigned number. (Note: the assigned number may be the same as the previous cell). I wish to change it into this structure:

StartFinish#
020
2040
4060
6080
80100

It will be organised by the start and finish instead of by the assigned number. Instead I would like the number to reflect the lowest assigned number that occupies a part of that area.Is it possible to create a forumla which would achieve this?

Converting Value To A Currency?

Oct 10, 2013

How to produce invoices in excel as opposed to Sage because it works better for them, with this in mind I will have to use excel for everything......

My invoice layout is basically like this.

Travel Hours Site Hours Rate Additional Material Total

2.00 2.00 1 £180.00
1.00 1.00 1.5 £135.00

I have different rates for different customers......then I have 3 rates for each of those customer. So If I charge £45p/h to one customer (normal rate) it then becomes 67.50p/h (1.5) and £90.00p/h (2).....depending on the time of call out. You will see above that one call out could incorperate two different rates, depending on arrival and departure time etc.

1) How do I get a value (travel and site hours) converted into £
2) How do I set a specific value for each rate customer, and rate? Can I have a rate saved so i can then select that rate relevant to that customer when inputting?

Converting Time With VBA

Jul 8, 2009

I have a spreadsheet that automatically inserts the colon into time entries (2123 converts to 21:23), my problem is that the VBA doesn't recognize single minutes (i.e. when when a time is entered from 12:01am (0001 or 01) until 12:09am (0009 or 09)). When entered I get a "Run-Time error '5': Invalid procedure call or argument"

Converting To 2007

Oct 6, 2009

My office converted to 2007 last night. I had created my own macro toolbar in 2003 with frequently used macros on it. How do I recreate this in 2007?

Converting Excel To Pdf

Dec 6, 2009

Is it possible to convert an excel file to pdf format using vba code?

Converting Raw Data From Hh:mm To Hh:mm:ss

Mar 21, 2012

I am trying to calculate the entire amount of time taken by each person in my team to complete a number of dictation files. The program we use exports the data in this format:

MacroLab 15:3029/02/2012 17:561/03/2012 8:00MacroLab 15:2129/02/2012 15:411/03/2012 8:29

Where it says 5:30, excel shows it in the formula bar as 5:30:00 AM.

What I've been trying to figure out (with very little understanding of how excel works) is how to format that column so that it shows 00:05:30 (as in 5 minutes, 30 seconds) so that I can sum the entire column for each typist and get the total amount of time spent typing per day.

I started changing it manually, but there's rows and rows of the data, for a few months now and its taking me forever. I've been googling my self ragged, but I either don't understand what people say, or I'm not searching the right way (probably a combination of both).

Converting LBS To Grams

Jul 20, 2014

I have an excel workbook that has a column called Weight in LBS. I already have about 7000 products all shown in pounds. Im switching to a new program that requires the weight be shown in grams! Is there any way of converting all of my existing rows to show as grams? They are all different weights, some less than a pound all the way up to 20 pounds!

Converting T To A Number Value

Oct 12, 2007

I am trying to convert a number which uses T to signify Thousand, and M for Million.

How would I program excel (or a VB Macro) to recognize and convert those letters to 0's?

Converting From Access

Jun 2, 2008

I am trying to convert An Access macro/function programs to Excel and I am having trouble processing the following in Excel:

Set rsbuildinforce = CurrentDb.OpenRecordset(InfTable)

(InfTable) is defined as an Access linked table name. The values of columns in rsbuildinforce drive the logic of the macro/function

The object of the program is to read in data from excel, do some manipulations, reformatting, etc... and output .csv files.

Converting Formula To UDF

Jul 10, 2008

creating a formula that would convert a string like 63008 (Mdyy format) to a format that Excel can read as a date

=IF(LEN(I2)=5,DATE(20&RIGHT(I2,2),LEFT(I2,1),MID(I2,2,2)),DATE(20&RIGHT(I2,2),LEFT(I2,2),MID(I2,2,2)))

In other words IF(the length of the cell value is 5, parse the date this way, otherwise parse it another way)

If I2=63008, this would output 6/30/2008

I want to convert this to a function since that formula is rather tedious to type out, but I am new to Excel programming

Converting Date To Day

Jan 16, 2009

I am currently converting a date in column "A" to a day in Column "B"

Columns("B:B").NumberFormat = "dddd"
Range("B3:B" & lrow).Formula = "=A3"
can someone please advise what code I need to make the day stay as text only in column "B"
If I do a copy / paste special for column "B" and change it to text it reverts to the number of the date.
I would like it to stay as the day,eg, Monday, Tuesday, etc.

Converting Many Tabs To One!

Feb 23, 2009

I have a PDF document that is one single table but it is long so it takes up 30 pages. When i convert from PDF to Excel (Using a converter) it gives me one tab per page I.E. 30 tabs.

I need to do calculation on the data, and need them on one page. Is there a way to merge tabs, or a macro that will do it for me?