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
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Creating Addins
I need some help about creating addins to distribute my macro. I have written a macro and have saved it as an .xla file. What i want is that when this addin 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 addins but am having some trouble in creating a button automatically when addin 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.
View Replies!
View Related
Creating A Custom Button When I Install My Selfmade Addin
Finally after 3 days of intensive studying VBA, my first addin 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).
View Replies!
View Related
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.
View Replies!
View Related
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).
View Replies!
View Related
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
View Replies!
View Related
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.
View Replies!
View Related
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 342501 through 343500. How can I get excel or VBA to do this automatically? 342501 342502 342503 repeat til 343500 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
View Replies!
View Related
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
View Replies!
View Related
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?
View Replies!
View Related
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.
View Replies!
View Related
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?
View Replies!
View Related
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 20080703) 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)
View Replies!
View Related
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....
View Replies!
View Related
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?
View Replies!
View Related
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..
View Replies!
View Related
Add ISNA To This Formula
=IF(ISBLANK(VLOOKUP(1,'DSortR'!A96:$N$450,4,0)),"",VLOOKUP(1,'DSortR'!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.
View Replies!
View Related
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
View Replies!
View Related
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)
View Replies!
View Related
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 ....
View Replies!
View Related
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.
View Replies!
View Related
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
View Replies!
View Related
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.
View Replies!
View Related
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
View Replies!
View Related
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.
View Replies!
View Related
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 inhouse. 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+D3F3) Puts the results in E3. This formula is copied on down the worksheet which results in: =(E3+D4F4), 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.
View Replies!
View Related
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.
View Replies!
View Related
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).
View Replies!
View Related
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)
View Replies!
View Related
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.
View Replies!
View Related
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.
View Replies!
View Related
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.
View Replies!
View Related
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.
View Replies!
View Related
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?
View Replies!
View Related
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.
View Replies!
View Related
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.
View Replies!
View Related
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.
View Replies!
View Related
WORKDAY Formula Replacement W/o Use Of Analysis AddIn
My boss has made it clear I can't require our people to use the addin. 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 addin... but I am not finding it.. can someone help? Using the addin, this is what I have: myCell.FormulaR1C1 = "=WORKDAY(RC[1],RC[2])"
View Replies!
View Related
