Tracker
HOME    TRACKER    Excel
 Advertisements:

# Creating A Formula To Add Lbs+ozs+drams

## I have the records of a local angling club going back to 1932 and these records include the results of fishing matches matches held through that period. I want to analyze the catches of those matches in order to identify any trends that may exist on the river that those matches were fished and although I am reasonably computer literate I have never needed to use spread sheets. If the data from those records were in metric measurement it would be no problem but they are in what I would refer to as Imperial measurement. In other words they use measures in weight based on Pounds, Ounces and Drams. There are 16 Drams in an Ounce and 16 Ounces in a Pound. The software that is on my computer is Microsoft Excel Version 5.0c

Sponsored Links:

Related Forum Messages:
Convert Grams To Lbs,ozs And Drams
Need formula that would convert grams to lbs,ozs and drams. I know that grams to drams is *0.564383391193 then divide by 256 to lbs but don't know now how to deal with the non full numbers

Creating A Formula To Add 30% To Worksheet Figures
I have a late report due @ work.... I have people who have worked for 4 days to get me numbers i need and they are not right.....

Anyways I have a spreadsheet with a whole lot of numbers on it... I need to add 30% to each number. What is the easiest way to set this up.

Creating Add-ins
I need some help about creating add-ins to distribute my macro.

I have written a macro and have saved it as an .xla file.

What i want is that when this add-in is installed an extra button appear on the menu bar and when users click the button the macro should run.

I tried to install the add-ins but am having some trouble in creating a button automatically when add-in is installed and assigning a macro to it?

I tried to look through some books and on some internet websites but really cant figure out.

Add Horizontal Line To Chart Without Creating New Series
I have a line Chart and want to create a static horizontal/indicator line.

I cannot create a new series to plot this horizontal line as I'm using an external data source (OLAP cube) as source data for the chart.

Creating A Custom Button When I Install My Self-made Add-in
Finally after 3 days of intensive studying VBA, my first add-in pack is ready to be installed in the office. I've got my custom form all ready, now all I need is to have excel create a custom button on the toolbar that calls up my custom form when clicked.

The idea of assigning a shortcut key doesn't really appeal to me. I'll be packaging it up in .xla format. Would be greatful if anyone can direct me on how to add a button to call up my form (only one form in fact).

Creating An If Formula
I am trying to creat an IF formula for a spread sheet I am working on with information from Bloomberg. I am referencing sheet one to sheet two so I have that forumula in, but in some of the cells this symbol #N/A N.A. appears because some of the information is not available on Bloomberg. How can keep the referenc formula but also include a formula that basically says if #N/A/N.A. symbol appears to replace it with just NA.

Creating A Formula With A Variable
Column A will be completely empty.

When an * is inserted into a single cell in column A, that Row number will become the variable in this equation:

=Sheet1!\$G\$'variable'

i.e. if i put a * in A14, then the formula should read (=Sheet1!\$G\$14).

Creating A Hyperlink From Formula
Basically I have a web page that I want to access with a command button http://www.wunderground.com/history/airport/KVTN/2009/3/28/DailyHistory.html
The only problem is that the address is date specific - the blue numbers and probably every time that I use the command, it will be a different date.

I used this formula
="http://www.wunderground.com/history/airport/KVTN/"&TEXT(R2,"yyyy/mm/dd")&"/DailyHistory.html"
and it gives me the right web address, I just need to figure out how to make it hyperlink to that web page and then use the command button to activate the hyperlink
This is the first time I have tried to hyperlink to a web page and am lost

Creating Formula Using Data From Tabs
I am trying to create a formula by comparing data in columns on a separate tab.

Basically, I want to take the value of one cell in the primary tab, find the matching value in a column on another tab, then once it is matched, pull in the data from another cell in the second tab on the same row of the matched data.

Creating Charts With Formula Not Data
Can i create a chart in excel just using a formula - but not data?

Creating A Range Of Numbers Through Formula
I cannot find the right catagorty I need to use to word this. I know there is a formula out there. I basically have 1k numbers I need to add into Collumn A of a worksheet - the numbers are from range 34-2501 through 34-3500. How can I get excel or VBA to do this automatically?

34-2501
34-2502
34-2503
repeat til
34-3500

Also, how to make it user friendly to create the same process over and over with a different set of numbers... meaning make it so I onlt have to input the range or numbers over and over. if it is an easy task I do this allot

Remove Formula From Creating Duplicate Values?
In worksheet named, " My Overview", if the total sales values are a zero in C47:C59 the consultant name is duplicated in B47:B59 because of the values being a zero in C47:C59. How can i have the formula not duplicate this?

Creating A Formula Based On Dimensions In A Single Cell
In my business I deal with a ton of different dimensions for my products. A lot of times the dimension will be within the cell as follows, "Product A 17x27 Brown", is in a single cell.

Is it even possible to write a code or formula that will find the "17x27" in the description, recognize it as a "dimension" and then run the formula that I need which is

Creating Summation Formula Based On User Input.
I'm trying to create a formula similar to this: =sum(B7:B & 8*A2+3). What I want the formula to do is start in B7 and go to B###. The cell number is based on user input. So if the user enters "24" in cell A2, then 8*24 = 192, 192 + 3 = 195, so the formula would be: =sum(B7:B195). Can I do this in a cell, or do I need to run it in a macro?

Creating A Formula To Calculate A Result That Depends On 3/4 Criterias Been Met
Firstly its probably best you download the file from below and open it up.

http://www.mediafire.com/?un3smhmdyzt

I need a formula to put in cells in column S on sheet March.

It needs to match up the correct price from sheet 'Container Price List'.

The price that should be displayed depends firstly on the account number, container and waste stream all matching to decipher which line the price is on. Then the price displayed should be the price in the column called 'exchange charge' however if this is 0 it should be the price in the column called 'rental charge'.

So for example, if you look at row 27 in sheet 'March' the price displayed would be £80.39. The formula will look at the account number 'c028' on sheet 'March' and match it to the any of 'c028' on sheet 'Container Price List'. It will then look at the container in sheet 'March' which is RL14 and match it with the ones that are RL14 in sheet 'Container Price List'. Then it will look at the Waste Stream in sheet 'March' which is GEN.C and match it with the GEN.C in sheet 'Container Price List'. The result given should be the exchange charge in sheet 'Container Price List' which in this case is £80.39.

If for example the exchange charge displayed £0.00 then the result given should be the rental charge in sheet 'Container Price List' instead.

Creating A Formula: Verify If My Actual Value Falls Within A Specific Range
i need to make a formula for excel which will verify if my actual value falls within a specific range. for instance if my value is 0.15 and the allowed range is 0.145 to 0.155 then i want to display the number zero in the cell however if the actual number is above or below the range i want to calculate the amount of deviation from the range

Formula To Handle 5 Fields Of Data To Avoid Creating Extra Columns?
I am inputting 5 variables in columns A through E:

Family (Y/N)
Adult (1/0)
Child (1/0)
Date of Birth
Gender

I am attempting to generate:

Age Males in families sorted into age brackets
Age Females in families sorted into age brackets
Age Males single sorted into age brackets
Age Females single sorted into age brackets

I did this rather simplistically generating 4 columns (F through I) and then using COUNTIF (although I know there is a better way than the lame way I used COUNTIF but that is not the point right now).

However, my question is, can I generate the data described above (located in D13:I22 on my spreadsheet) without going through the messy, cumbersome step of creating extra columns?

Creating A Customized Accumulative Code Week (modified WEEKNUM) Formula
My company uses a customized accumulative code week system, as in the year 2000 to today is about 442 weeks with the year 2008 starting around week 416.

Here is how my formula works(or how I would like it to work), if the date (in cell F8) is blank, nothing is displayed, if there is a date (format 2008-07-03) and it falls within 2008, WEEKNUM+416(416 is a fixed reference in J4), if the date falls within 2009, WEEKNUM+416(J4)+52, if the date falls within 2010, WEEKNUM+416(J4)+104. I only need it to go from 2008 to 2010.

This way J4 is a fixed code week reference where 416 is added to each week number, but it does not work for years 2009 and 2010 as it does not add the weeks from the starting reference.

Here is a copy of my formulas:

=IF(AND(F8)=0,""*(IF(AND(F8)

Add Addition If Condition To Existing Formula: Long Formula
This task joins a string together based on a number of characters per cell in the range.

I want to isolate one range, Col N, and add an IF condition to it.

There may be other issues preventing this from happening, e.g. the number of IF that exist in the complete formula. I will isolate the current cell and its requirements and then post the entire formula at the end for reference....

Date Formula & Add If Statement To The Existing Formula
I currently use the following formula: =workday(H21,H23,J20:J34) which calculates my date based the date entered in cell H21. While testing I realized that I needed to add another variable to the formula for a specific scenario. When my Date in H21 is less than todays date I require another workday formula to be calculated. How can I add this if statement to the existing formula?

Circular Reference: Cell References In The Formula Refer To The Formula's Result, Creating A Circular Reference
I have the following formula in cell L51 of all sheets calculating the volume depending on the monthly index that is chosen from the drop down menu in a particular sheet. =If(MIndex=0, SUM(D33:L50),If(MIndex=1,SUM(D34:L50),If(MIndex=2,SUM(D35:L50), 0))). I am getting the following message and I do not understand what it is about.

Microsoft Office Excel cannot calculate a formula. Cell references in the formula refer to the formula's result, creating a circular reference. Try one of the following

Formula- If Between Add
I am having a few issues stringing along a formula.

A1 5

I need A2 to say "If A1 is between 0 and 2 add 0, if greater than 2 but less than 4 add .30, if greater than 4 but less than 6 add .70 and so on.

A2 looking at A1
if 0 through 2 add 0
if 2 through 4 add .30
if 4 through 6 add .70
if 6 through 8 add 1
if 8 through 10 add 1.3
if 10 through 12 add 1.6

Basically these are employee break times that I need to automatically figure into our labor hours. Every 2 hours they get "x" amount of break time. So if they have over 2 hours of work they will get 1 break, if they work over 4 hours then I factor in a lunch and the previous break ect..

Add ISNA To This Formula
=IF(ISBLANK(VLOOKUP(1,'D-Sort-R'!A96:\$N\$450,4,0)),"",VLOOKUP(1,'D-Sort-R'!A96:\$N\$450,4,0))

I've almost got what I need for a mind bending series of sorts that go from worksheet to worksheet. I need to add ISNA along with the ISBLANK above.

Add Formula To Macro
I have this macro which runs great, but I would like to add a date formula to it before it auto fits the columns. I need cell E5 to Increase its date by 1 month to the last day of month from G5. For examply if g5 reads 2/28/07 I want a formula that will make e5 3/31/07 for every sheet.

Maybe something like this =DATE(YEAR(g5),MONTH(g5)+2,0). but I don't know how to put it in the macro to run on every sheet (except the ones I excluded.)

Sub PayrollAnalysisMacro()
Dim wrkSheet As Variant

For Each wrkSheet In ThisWorkbook.Sheets
If wrkSheet.Name <> "Download" And wrkSheet.Name <> "Recap by DC MTD" And wrkSheet.Name <> "Recap by DC YTD" Then
With wrkSheet
.Range("G5:H71").Insert Shift:=xlToRight
.Range("E5:F71").Copy .Range("G5:H71")
.Range("G5:H71").Value = .Range("G5:H71").Value
.Columns("G:BE").EntireColumn.AutoFit
End With
End If

Do Not Want To Add An IF Formula If Value Is Blank
I have an IF formula that says if there is no data on an specific cell, then blank the cell. My problem is that when I try to count cells with data on the cells that have the formula, it is counting the blanks too. How can I make those blanks cells not to count?

Formula To Make Every 50 Add 1
If i wanted to tack on something to the end of a formula so that it would make every 50 add 1 (example: 200 would convert to 204, because 200 divided by 50 is 4).

Formula To Add The Entries
If i have the following chart, what formula do i need to add all the entries EXCEPT and entry OS. i have the formula to add the entries but i need to exclude any "OS" entries. Like the following, but i want it to total 6. (minus the 2 OC entries)

=COUNTA(D6:D15)

TS
TS
VR
IS
IS
OS
VR
OS
8 (total)

How To Add Formula To Existing Macro
I have a data input worksheet, which uses the following code to fill in the missing zeros when cells are empty.

Insert A Row And Add A Formula
I have a spread sheet that I need to insert rows into and then in those same rows, add formulas that display percentages compared to the grand total row. To make it a little more complicated, this spread sheet changes from day to day, so the rows and columns will be different each time. So the code will have to recognize text, then insert the row, then do the formulas in the blank row. The only constant will be that it will always start on Row 5. I have no idea how to start or begin to do this, so I attached a file with a page showing where I am at as an example, and then anther page showing what I want ....

Add Clicked Cell Value To Formula
The idea is that once macro is started it will add the value of any clicked cell into the formula of the original cell.
ie.
1.start macro
2.click cell a5 (value=36)
3.type '+'
4.click cell b7 (value=21)
5.click enter to end macro
Result will be '=36+21' in the formula bar. The cell will then show the answer 57.

Formula To Add Leading Zeros
I need a formula that will make a 4 digit number by adding leading zeros. If the number is already 4 digits then don't add leading zeros, do nothing. all numbers will be 1,2,3,4 digits long.

For Example,

if number is 1 then make it 0001
if number is 11 then make it 0011
if number is 111 then make it 0111
if number is 1111 then leave it 1111

Formula To Add Within A Range Of Cells
I am trying to utilize a formula to add, within a range of cells, any cells that contain P/8 or P/5 or so on. The "P" would stand for PTO and the number behind the slash would designate the number of hours used for PTO. Is there a way to do this. In my chart I need to distinguish time between, PTO, Legacy, Holiday, etc.

Formula To Copy Down But Add Rows To It
Is there a way to create a formula to add for instance 4 rows to it. I am brining figures from a worksheet titled std inventory to another worksheet. The std inventory worksheet is set up by weeks with 4 lables. So the figures I want to bring in for weeks ending are in increments of 4 rows. For instance I am working on a sheet which I am inputting formulas which read:
= 'std inventory'!M50

My next formula down I want to input would be
= 'std inventory'!M54

Is there a way to copy and paste special this formula but have it add the 4 rows on easily?
So the next formula would be

= 'std inventory'!M58

Add Formula To Range In Macro
I'm working with 4 sheets:

Map1: contains a mapping table from old values to new values ( Name)
Map1: contains a mapping table from old values to new values (Location)
Input: contains a table with 3 columns, Amount/Old Name/Old Location
Output: contains a table with 3 columns, Amount/New Name/New Location

I need to create the sheet called Output, which will have the same Amount column as Input sheet but will do a vlookup to get New Name and Location values using the Map1 and Map2 sheet.

Add Formula To A Text Box
I am trying to insert a formula into a text box but I having problems with the "wording portion" of the formula.

I am trying to pull total sales from cell A1

Am I using this formula in the textbox

Add Condition To Simple Formula
Using Excel 2003. I have a very basic SUM formula I am using to show how many forms are still in-house. Pretty simple.

Column D is how many came in.
Column F is how many were completed.
Column E is what is left.

To get the daily count I take the previous day's Column E, add to today's Column D and subtract today's Column F.

=(E2+D3-F3)
Puts the results in E3.

This formula is copied on down the worksheet which results in:
=(E3+D4-F4), with the results in E4. And so on down Column E.

The problem is that if there is no data entered in Column D or F, the results from E are carried (copied?) as far down as the forumla. i.e., if today's column E results in 4, then that is displayed for as far down as I have copied the formula. What I'd like to do is set the formula so that if there is no data in columns E or F, then there is nothing displayed in Column E.

Using Wildcards Within Formula (add Together Will Change)
=SUMPRODUCT(--('Trial Balance'!A1:A100="6120"),--(ISNUMBER(MATCH('Trial Balance'!B1:B100,{"00.00.01","00.01.01"},0))),'Trial Balance'!G1:G100)

Is there a way to use this formula, but instead of looking in Column B, and having to list 00.00.01, 00.01.01, that it only looks at the last two digits of the ##.##.##.

Here is the issue. Each month, the numbers that make up what I need to add together will change. However, all I am really doing, is combining any accounts that have the same sub account (Column B) ending in the same digits of either 00, 01, 02, or 08. So I could have 01.01.01, 01.00.01, 01.02.01, etc. and I am just focusing on the .01 on the end. I would like to say, look at all the sub accounts in B, and only add (G:G) on those that the last two digits equal .01.

Formula To Add A Number To A Cell
I need a formula to add the number 1 into cell J9 when cell d9 is no longer blank.
I'm sure it's really easy- but I can't figure it out.

Formula To Add Unique Values
here's the situation and what i'm trying to do:

each row/record is as follows:

column A contains zip code
column B contains zip code population
column C contains city name
columns D contains state name

there are multiple duplicates in each column.

i am trying to calculate the population for each city and place that data in each row in column E.

in order to do so, i need to add the populations (column B) of all UNIQUE zip codes (column A) for each city (column B) / state (column D).

Sumif Formula: Three Ranges Add Together
The section I have highlighted in orange has three ranges added together how would I show this in a simplified formula?

=sumif(DailySalesAnalysis!\$C\$1:\$GA\$1,A25,DailySalesAnalysis!\$C\$85:\$GA\$85)+sumif(DailySalesAnalysis!\$C\$1:\$GA\$1,A25,DailySalesAnalysis!\$C\$44:\$G A\$44)+sumif(DailySalesAnalysis!\$C\$1:\$GA\$1,A25,DailySalesAnalysis!\$C\$41:\$GA\$41)

Add Indirect Formula Via Macro
I need to create a formula for a series of ranges that have a variable sheet name (which is located on sheet Backend!E15) and when it creates the formula will reference the exact same cell on the variable sheet.
this is what i have so far...

Option Explicit

Sub formulaset()
Dim Cell As Range
Dim target As String
For Each Cell In Range("b4:al132")
Application. ScreenUpdating = False
target = Cell.Address
Cell.FormulaR1C1 = "=INDIRECT(CONCATENATE(BackEnd!E15,""!"",target))"
Application.ScreenUpdating = True
Next Cell
End Sub

but this is the answer I am getting in the first cell of the range...

=INDIRECT(CONCATENATE(BackEnd! 'E15',"!",target))

as you can see I am having trouble getting the target address to lock in. To make things worse, its needs to be in " " so the concatenate creates the corect address link.

Multiple Plus Formula: Add Scores Of Which There Are None
I have a grid set up to add scores of which there are none. The boxes each have a formula, ie: =IF(FIXTURES!D1="","",FIXTURES!D1 ) this produces a blank square until a score is registered. At the end of each column, down and across there is a formula for adding up the scores. Downwards is: = SUM(C2:C25), this works fine.

Across however is: =B3+D3+F3+H3+J3+L3+N3+P3+R3+T3+V3+X3+Z3+AB3+AD3+AF3+AH3+AJ3+AL3+AN3+AP3+AR3+AT3+AV3 This doesn't work, just gives the answer TRUE. It did work before the original formula was added.

Add Formula Reference To Another Worksheet
I have a worksheet that changes monthly but has the same fields always. I have created a few macros, and one that is a 'RunAll' for ease of use. Sometimes there is an error and I have to go back and fix some data, So I am looking for a way to change the "SelectBzzAgent" Macro to link data to a new sheet instead of copying. I have attached my spreadsheet.

Add Formula To Added Rows
How do I apply a formula to every cell in a given column?

I'd like my workbook to store this formula:
=c<current_row> + d<current_row>
in every cell of column P as a default.

This means that even if I do "insert row", I wouldn't have to copy this formula into the new cell.

Formula To Add Alternate Cells
I can't remember any formula to do the task. I have a time series with quarterly sales. The dates are in format given below.

01/01/1990 - first quarter of 1990
02/01/1990 - second quarter of 1990
03/01/1990 - Third quarter of 1990
04/01/1990 - fourth quarter of 1990

I need to add the sales of all first quarters , all second quarters..and so on.
Is there any formula that an elegant way or I have to do it manually?

Add Exception To Sumif Formula
I have a workbook setup to give me the nutritional break down of all of the foods I consume. I can get the subtotal of each day. What I need is to also get the subtotal of each meal. I can't seem to get this meal subtotal without adding it to the daily total twice (once in the listing and once in the subtotal). I was thinking of trying to add an exception to the sumif formula in columns I, K & M but I don't know if that is possible. I have included a cut down version of my workbook to illistrate the problem. I will gladly supply a full vertion to anyone who would like a copy for thier own use.

Code To Add Formula To Cell
I tried putting a formula into the below code but without any success.

mylogoff. Offset(0, 3).FormulaR1C1 = "=IF(WEEKDAY(RC[-1]=""6"",""17:30:00""+0,""18:00:00""+0)

It says application defined or object defined error.

Add Row In Sum Formula
I want to sum values in A1 to A6, so i use =Sum(A1:A6), i add rows aftere A6 what i shall put in the sum formula to include newly the added rows

Create Formula To Add 0.2 To Cell Value If Its Greater Then 0
I want to create a formula which will add 0.20 to the value of a cell if its greater then 0.
Basically i have a list of cells some of which will have a value and i want to sum this list and add 0.20 if there is a number in the cell.
I am trying to create a quoting system and basically its a list of rooms etc with the wall lengths and i am adding 0.20 to each wall to allow for excess but for example if there are no measurements in bedroom5 i dont want the 0.20 added.

WORKDAY Formula Replacement W/o Use Of Analysis Add-In
My boss has made it clear I can't require our people to use the add-in.

I have searched under WORKDAY as well as under Barry Houdini, since someone said he has come up with plenty of replacements for the Analysis add-in... but I am not finding it.. can someone help?

Using the add-in, this is what I have:

myCell.FormulaR1C1 = "=WORKDAY(RC[1],-RC[2])"

Copyright © 2005-08 www.BigResource.com, All rights reserved