Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


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:
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

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:


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
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
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 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.

View Replies!   View Related
Creating Charts With Formula Not Data
Can i create a chart in excel just using a formula - but not 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 34-2501 through 34-3500. How can I get excel or VBA to do this automatically?

repeat til

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
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?

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.

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
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: 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

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

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 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:


View Replies!   View Related
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

View Replies!   View Related
Creating A Range After Creating A Different One
I am trying to build a selection for a procedure to use.

View Replies!   View Related
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.

View Replies!   View Related
Creating A Range In VBA
I'm trying to create a range between two text values; for example:

Equity Total

Fixed Total

How can I name a range from "Equity" to "Equity Total" and from "Fixed" to "Fixed Total" in VBA?

View Replies!   View Related
.0 Is Creating #VALUE! Error
I am currently using the following formula:


However, I get the results of #VALUE!

In cell H21, the value is 27.00

How do I force excel to recognize the .00 in the formula?

View Replies!   View Related
Creating Pie Charts With VBA
Now I need to create multiple pie charts based on data in tables on many sheets.

I need to create the same set of charts, for each sheet separately.

I recorded a macro for one sheet, which created following

ActiveChart.ChartType = xlPie
ActiveChart.SeriesCollection(1).Name = "='GALLERY ON 4TH'!$B$2"
ActiveChart.SeriesCollection(1).Values = "='GALLERY ON 4TH'!$D$6:$D$8"
ActiveChart.SeriesCollection(1).XValues = "='GALLERY ON 4TH'!$A$6:$A$8"
ActiveChart.ChartType = xlPie
ActiveChart.SeriesCollection(1).Name = "='GALLERY ON 4TH'!$A$6"
ActiveChart.SeriesCollection(1).Values = "='GALLERY ON 4TH'!$B$6:$C$6"
ActiveChart.SeriesCollection(1).XValues = "='GALLERY ON 4TH'!$B$5:$C$5"
ActiveWindow.SmallScroll Down:=3
ActiveChart.ChartType = xlPie
ActiveChart.SeriesCollection(1).Name = "='GALLERY ON 4TH'!$A$7"
ActiveChart.SeriesCollection(1).Values = "='GALLERY ON 4TH'!$B$7:$C$7"
ActiveChart.SeriesCollection(1).XValues = "='GALLERY ON 4TH'!$B$5:$C$5"

However I cannot automate. I tried different methods for creating the charts, as found on the site [prefer .chartobjects.add(x,y,z,a) since I can immediately position the chart] , but get stuck at seriescollection everytime.

It looks like when the chart is created by XL (with suggested data in it) I cannot address the series data.
I got it right to generate a chartobject that is empty, then added the series data to it, but when opening the new chartobject, it is again populated with suggested data.

How do I generate an empty chartobject everytime, so I can insert seriescollection info with code?

Any other way to generate charts?

View Replies!   View Related
Creating Charts With VBA ..
I've got a table which has additives in Column A and products on Row 1. There are 13 additives and 10 products. Each combination of product and additive has a price. So I have 130 different prices in my table. The prices change monthly and the new prices are added to the sheet so that last months prices will move to left so that the newest price data table is A1:J13 and previous month is K1:T13 and so on.

I need to make an individual chart to every combination which contains the history of the price changes. I have two cells (A20 and B20) where the user can pick a product and an additive (created a menu by using Data|Validation...). These selections are combined in a cell C20 (=A20&B20). Then I have a macro that is linked to the cell C20 and is basically Case Is = 1st combination --> Call Macro1, Case Is = 2nd combination --> Call Macro2 etc.

That macro will work out just fine, BUT is there any way I could make all the 130 charts easier? Now I've recorded a macro that creates the first chart and then I've just copy-pasted it, changed the attributes (cell numbers, names and so on) that define the data to the next charts. This will take ages...

Every combination will have at least 13 changes (12 monthly price cells and the name of the cart) so in total this will mean about 1500 manual changes. In my case this will mean a high danger of typos.

I know, it would be SO much easier if I could give you the Excel file to look at...hopefully you understood what I mean.

I'll only follow the changes within a year so prices older than a year will not be shown in the charts.

View Replies!   View Related
Creating New Sheets And Name Itself With Vba
i have a named range that is one column wide and changing in lenght, rows. I want a sheet to create and name itself the same as the value in the cell for each value in the named range. if a name in the range should be removed then so should the sheet.

View Replies!   View Related
Creating A Log File
I've got numerous vba-coded spreadsheets in use with lots of different users, and have found that some users try to hide errors of their own making by blaming the spreadsheets (I know - what is the world coming to!)

I've decided to add a script which creates a log file each time one of my spreadsheets is used - opening a log which relates to the user's spreadsheet and recording username, date/time, path & file, and the vba being used.

I'll probably add an "on error" script to save copies of all open workbooks too.

The issue I have at the moment is how to record the name of the macro being run?

Here's my code so far: ....

View Replies!   View Related
Creating A New Worksheet
i want to do 160 seperate sheets for each employee
i have there attendance for a full month

D2-AI2 are the employees names
D4:AI230 is the attendance stats

i want to create a worksheet for each employee
taking the info from my main page

View Replies!   View Related
Creating A Timesheet ....
Not sure where the best to ask this is so i'll do it here.

I have a h:mm time which i need to get converted into days/hours/minutes, creating an on the fly phrase of something like "2 days, 4 hours, 32 mins" for example.

eg: 26:45 (hours/minuts) to be converted to "1 day(s), 2 hours, 45 minutes"...

View Replies!   View Related
Creating A Schedule
I am Cuttently creating a schedule in excel for work. the schedule will look like this:


etc etc

the thing is though this is simple enough but i am trying to use a bar chart to help me show how many people i will have on shift at anytime e.g ...

View Replies!   View Related
Creating All Possible Combinations
I am trying to create a combination list. I have 48 items and I want to create all possible comibinations of 3 items from this group of 48 items.

I did something similar to create all possible combinations of 2 items from the group of 48 items in the attached spreadsheet. This was done manually and was time consumning. I want to do something similar for all possible combinations of 3 items from this group of 48 items.

All my 48 items are listed in rows 4 and 5. I want to enter the number "1" in each column to indicate my selection. Right now I have 2 ones per row. But I want to do 3 ones per row.

View Replies!   View Related
Creating Reports
I am trying to create a report that takes two user inputs and looks them up on another worksheet in the workbook and returns the right value. As an example I have the user input a date and a job location and on another sheet there is also a date and a job location that has a population associated with it. Is there a way to return this information so that it changes whenever the user changes these two criteria

View Replies!   View Related
Creating N-tuples Of 1's And 0's
Is there an easy way in Excel to generate all of the 512 possible combinations of 1 and 0 in a 9-tuple (9 consecutive numbers with each number either being 1 or 0)?

For example:
1, 0, 1, 0, 1, 1, 1, 0, 0
1, 1, 1, 0, 1, 1, 1, 0, 0

View Replies!   View Related
Creating A Schedule ..
which is row D on my excel sheet. I need to fill into another form who works shift D, shift E and shift N. Is there a formula I can use to do this? I have given a small sample of my schedule below.

AB CD 252627ShaunD DDNicoleD EDEmilyE DECathyENEJohnNEN

View Replies!   View Related
Creating Percentiles
I am going to be gathering some raw test results soon, and I need to create a percentile for this particular group of results. way to do this in Excel? Or at least point me in the right direction? :o

View Replies!   View Related
Creating Hyperlink From Value
I have created this Function. It works fine but currently it is only returning text into the cell. I would like it to return a hyperlink.

Heres the code

Private Function PDFLOOKUP(pdfname As String)
Dim sFil As String
Dim sPath As String
ChDirAPI "\path o"
sFil = Dir("*" & pdfname & "*.pdf")
If sFil = "" Then PDFLOOKUP = "" Else
If sFil "" Then PDFLOOKUP = CurDir & "" & sFil
End Function
I'd like this

If sFil "" Then PDFLOOKUP = CurDir & "" & sFil
To display a hyperlink with

Link = CurDir & "" & sFil
Text To Display = S
Font Size = 12

View Replies!   View Related
Creating New Workbook ...
Is it possible to create new workbook which contains sheet1 datas only from master workbook by clicking command button,and the name of workbook will be asking by inputbox and create accordingly?

View Replies!   View Related
Creating A Query
I have a table that contains data for a region broken down into North South East and West areas.

New data is added to one spreadsheet (Region).

What I would like to do is have information for the North team automatically moved (or copied via a link) to the North sheet for printing off.

View Replies!   View Related
Creating Sub Menu's
I am using the following code to build and remove a menu on the menu bar. how to modify this to create sub menu's instead of a single drop down list

View Replies!   View Related
Creating A Login Box
I want it so that when the workbook opens it prompts for a username and password. You can also have a button for "register" etc. Which will send you to a website.

View Replies!   View Related
Creating Two Menus
I have a workbook which uses the code below to create a new menu upon opening. I have now used this same code in a second workbook to create a different menu. both work OK on their own However if by chance both workbooks are opened at the same time only one menu is shown. Is there a way I can change one of these to create a second menu if the first one is already there?

View Replies!   View Related
Creating A Search Box
I need some help in creating a search box in excel. I have 4 rows of data, and want the user to enter a 4 digit number in a text box, which is then searched in the spreadsheet, pulling up the results.

So far I have the following code..

Sub Button1_Click()

'Opens box and ask what do they want to search
searchthis = InputBox("Type in a location keyword.", "Property Search")
'Tells where to search
'and then search in them whatever the user entered:
Selection.Find(What:=searchthis, After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _

End Sub
It only highlights the results, leaving all other data on the spreadsheet. Ideally id want to see only the results of the search. Is there anyway to do this?.

Also, if the search returns no results, I get a debugger message. I want some sort of error message to come up.

View Replies!   View Related
Creating Buttons Using VBA
I'm trying to make a macro that creates a button (to run another macro). I'm also trying to make sure this has a specific name, instead of just "Button#". The code I get from recording this is:

View Replies!   View Related
Creating A List..
I thought this would be a simple task, but i cant seem to figure out how to do this.
I have a list with names and its a fixed number of rows, say A1:A200. In the next column, B1:B200, there is either TRUE or FALSE. I want a make a new list with all names with "TRUE" on the same row, without any blanks. How can I do this?

View Replies!   View Related
Creating A Userform With VBA
On a worksheet i have a list of names in Column A the list length can vary this is why i want to create a userform using VBA. The form just wants a checkbox with name next to it for each name in column A. If the checkbox is ticked I want a 'Y' in Column B next to the corresponding name.

View Replies!   View Related
Creating Tab Rows
I have worksheets with many Tabs, I wanted to know if it was possible to create Rows of Tabs? I have included a picture of what I am talking about.

View Replies!   View Related
Creating A Chart ..
I have creating a chart. All the information in the source data appears to me to be ok but the chart isn't been displayed.

View Replies!   View Related
Creating A VBA Chart
I have wriiten some simple code to create a chart which works fine. I have a problem applying formatting to my chart. I am using the With construct to apply the formatting, but the problem is that the chart name keeps on incrementing from "Chart 1" to Chart 23" etc. each time I run the chart and I get an error message before the With code?

View Replies!   View Related
Creating An If Then Macro
I am creating a macro that when cell range H7:H78 = x then the cell in the same row but column A changes to r. This is what I have so far.

View Replies!   View Related
Creating New Tab Syntax
if I copy and Add it created a new workbook not the current on im in. What am I doing wrong?

View Replies!   View Related
Creating Pdf Files
I have a looping macro in Excel using

"For Each Cell in Range ()"

where the range is a list of department names which produces a report
for each department. Instead of hard copy, I would like to produce
individual PDF files one per department, but can't find way of
changing the output file.

View Replies!   View Related
Creating Histogram From VBA
I am writing a macro that finds the prime numbers between to numbers chosen by the user. I want to create a histogram showing the distribution of the primes. I was going to use the Histogram tool in the Data Analysis add-in. My problem is in setting the input range. I do not know how many entries there will be in the input range. How can I write the argument so that it can be "dynamic" I can determine how many entries there are after I determine the prime numbers but I am not sure how to put that info into the Histogram argument.

View Replies!   View Related
Creating If Statements
I am trying to write an if statement for example if the dollar amount is between $5.01 and 150, then * by 3.5 and I cannot seem to get the statement correct.

View Replies!   View Related
Copyright 2005-08, All rights reserved