Creating A Formula To Add Lbs+ozs+drams

Mar 6, 2009

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


Convert Grams To Lbs,ozs And Drams

Aug 15, 2009

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

Creating An If Formula

Mar 23, 2007

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

Creating 30 Day Calendar That Will Run Through Formula?

Aug 15, 2014

I'm trying to make my life a little easier at work. Here is what I am trying to do. I'm trying to create a "calculator" that will tell me the date that a client will leave my program. I understand that I can simply do xx/xx/xxxx-15 and it will get me a new date, easy. Here is the kicker...My office only utilizes 30 day calendars. Regardless if the month has 28 or 31 days, in the eyes of my agency it will have 30.

i'm been trying to mess around with it for 3-4 days now and I can't quite figure it out. I have attached a sample spreadsheet what i'm i'm trying to do.

I would eventually like to create this to be a little interactive (like a pop up or something that is more user friendly) but i'm just itching to get it to work!

View 3 Replies View Related

Creating If / Then Formula With Time

Dec 4, 2013

I am trying to create a formula that subtracts 1 hour if the value of cell E3 equals 11:00 PM.

This is what I've tried:

=SUM(E3-D3), IF(D3=11:00 PM, -1:00)

View 9 Replies View Related

Creating A Complex Formula With UDF?

Jul 21, 2014

I am creating a complex formula with UDF, this Hlookup is part of my complete formula. Meanwhile, I am working on this piece, not Sure what I am doing Wrong

I try to conver "MonthCurrent" Variable in a Year because the MonthCurrent is 06/01/2014, so will be Year(6/1/2014) = 2014

My Range called "DailyRates" is based per Year = 2014, 2015 ......


View 2 Replies View Related

Creating A Hyperlink From Formula

Apr 23, 2009

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

Creating A Formula With A Variable

Jul 14, 2008

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

Creating A Formula To Add 30% To Worksheet Figures

Oct 10, 2008

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

Creating Loop With Small Formula?

Mar 13, 2014

I am trying to get the following thing started but my loop doesn't succeed. I've got 3 columns. I need to place a formula in column C if A is empty.


A2 contains data so do nothing.
A3 is empty do: C3=B3+B4.
A4 contains data so do nothing.
A5 contains data so do nothing.
A6 contains data so do nothing.
A7 is empty do: C7=B7+B8.

this is what I've got so far:

[Code] ..........

View 4 Replies View Related

Creating Maximum Limit Formula?

Dec 9, 2011

I am in the process of creating a template in excel with certain restrictions on the information that can be added to it. I've hunted several sites and forums to try and find a solution but find it difficult to see the best ones for my project. The template is roughly made up of say 10 references to large boxes, each box can hold smaller boxes. The smaller boxes vary in size. For example the large box is 60 inches long smaller boxes vary between 20 inches and 40 inches, can excel be made to tell me an error if i try to put 2 x 40 boxes in columns refering to 1 of the larger boxes as it exceed the maximum length of 60 inches?

View 2 Replies View Related

Creating Spaces In Formula Results

Jul 31, 2014

I am using a vlookup formula to link 3 pieces of data together. I would like to insert a space between the 3 pieces. Currently formula result is:


I would like it to view as:

item1 item2 item3

not sure how to alter the formula to create the space....

View 3 Replies View Related

Creating Formula Using Data From Tabs

Nov 17, 2008

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

Creating Charts With Formula Not Data

Jun 28, 2006

Can i create a chart in excel just using a formula - but not data?

View 4 Replies View Related

Creating A Range Of Numbers Through Formula

Jun 29, 2006

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

Creating Complicated Formula With Letters And Number

Apr 8, 2014

I have been trying to create a formula that will save me DAYS of messing around at work.

What I am trying to achieve is to have a sequence of numbers as follows:

BNA01A01 to BNA01A09 then have it change to BNA01B01 to BNA01B09.

This needs to be repeated for all letters to BNA01I09.

Then this sequence needs to be repeated to BNA12.

The last thing is for me to be able to change the formula in order to implement the same sequence on a separate sheet for BNB01A01 - BNB12I09 to BNL01A01 - BNL12I09

View 2 Replies View Related

Creating Formula That Will Lookup For Multiple Arrays?

May 28, 2014

I need creating a formula for the following situation.

I need to link multiple arrays from different sheets. But am having a hard time concocting a formula that will work.

I have 2 columns of data on sheet1. For example:



Now, I need a formula that will sum the entire column B, by looking up the array of column A on another sheet and only summing the numbers on sheet 1 that also have a value of "West" on sheet 2. Sheet 2 looks like this:


This needs to be functional as a template to be used each month - the entire point of the formula is to save time and eliminate the current (and lengthy method that is in use). I can tweak the formulas as needed. But am trying to avoid having to concatenate anything or do any modifications to the format of the sheets.

View 1 Replies View Related

Creating A Formula Form Text In Different Cells?

Dec 10, 2013

I have a formula which looks in a folder for a file and returns the cell I want from that folder and work book

='H:NCHOCall CentreCall Centre ResourcingStats[NHC_Telephony_Daily_2012-11-27.xls]NHC Daily'!$E$55

as you can see I have a date in the middle, I want it to reference to a cell with a date in and return the information from that document with the change in date. So all I have to do is drag the formula down and it will return the info i want out of each report.I think it might be an add on that I need to download?

View 1 Replies View Related

Creating Report - Using DATEDIF Formula With Condition

Mar 24, 2014

I am creating a report and I am using the following Formula with condition.

(IN Q2 in the file attached)
=IF(P2="","Enter New to IMP check Date",DATEDIF(P2,C2,"d"))

Where in P2 is the START Date and C2End date.
P2 = 01 Jan 13
C2 = 10 Mar 14

When I apply the DATEIF formula its ignoring the year differ ace and give a result of 8 days not sure whats wrong here as the "Y" & "M" function works correctly and give proper result.

Sample attached : Book1.xlsx

View 7 Replies View Related

Remove Formula From Creating Duplicate Values?

Dec 14, 2009

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

Creating Macro Which Replicates Excel Formula

Apr 1, 2014

I have created the following excel attached which gives me the results I require,

However I am wondering is there any possibility I can get the results produced via VBA as it takes to long to calculate with formulas

View 1 Replies View Related

Formula To Automate Creating Break Times

Jan 15, 2012


First rule is that no breaks can overlap.

1st Break is 40 Mins.

2nd Break is 20 Mins.

1st Break is at least 2 Hours After Time-In.

2nd Break is at least 2 Hours Before from Time-Out.

Each Employee might have a unavailable time for breaks.

Not sure if it is possbile to create a formula like this, or I might need an Excel Wizard to do this.

View 3 Replies View Related

Creating Formula To Lookup Rate Using Ranges And Other Criteria?

Mar 26, 2013

I need to know if its possible, and if it is, what the formula would be to get the correct "Rate" to pull based on the criteria given:

User would input the following information:

Zip Code: 56559
Pallets: 3
Weight: 1200

The formula needs to use the following table to use the criteria listed above, to fine the correct "Rate". The "Zip Code" and "Weight" both need to fall between the correct ranges and then match the "Pallet" to find the correct "Rate".

From Zip Code
To Zip Code
Weight From
Weight To


View 1 Replies View Related

Creating Formula That Would Generate Perfect And Full Circle?

Jan 9, 2014

Creating a formula that would generate a perfect and full circle - see attached picture example. The circle needs to remain centered in the X,Y axis. Also the circle diameter will either be 1, 2, 3, or 4 units large depending on user selection.

View 1 Replies View Related

Excel 2008 :: Creating Formula To Convert Information?

May 7, 2014

I am trying to get a grip on my gas n electric bills. My problem is in creating a formula to convert the info I have into the final bill.

The info I have is -: days of use, amount of G n E used, standing charge, price/unit and discounts applicable. For simplicity I have converted them all into a rate per day and £GB. I tried the following formula

:-(standing charge per day * days used)+(cost per KWH * No.of KWH used) - (discounts * days used) / (100) * (94)

This last one is to account for a direct debit 6% discount. I used cell address for things like No of days usage and KWHs used in the period. The values in these cells have themselves been calculated via a formula from other info. The results I get seem to be a constant variable which indicates that I am doing it wrong.

View 3 Replies View Related

Offset Formula - Creating Dynamic Line Chart

May 28, 2014

I am trying to create a dynamic Excel line chart.

All is well, except for my x-axis (which is a text axis). It currently looks like this: Dynamic axis.jpg

I am using the Offset formula. The formula for my x-axis is =OFFSET(Worksheet1!$D$4,0,-3,COUNT(Worksheet1!$D$4:$D$36)). This is to show the axis if cells in Column D are filled up appropriately.

However, in the cells containing the axis name itself, this formula is used =IF(D9=0,NA(),'Worksheet2'!A6). I've tried removing the formula from these cell, but that didn't solve the problem.

View 5 Replies View Related

Creating Formula - Adding Values (best 7 Of 10) With 4 Mandatory Fields

Jun 22, 2014

i need to create a formula to calculate rankings for a race series. there are 10 events in the series, only the best 7 individual results count. and there are 4 events which are mandatory and must be included in the rankings. sample attached.

View 3 Replies View Related

Creating Summation Formula Based On User Input.

Jan 8, 2009

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

Creating Formula To Change Details On A Sheet Using Userform?

Feb 21, 2014

On the attached spreadsheet, ormula on 'UserForm2'.

When I hit the 'Submit Changes' button I want a formula so that it will look up the product row in Product List (Sheet 1) that is select in the ComboBox1.

Once this row has been found I want the values in TextBox1 to be copy and pasted into Column D of that row and value of TextBox2 to be copy and pasted into Column G of that products row.

View 10 Replies View Related

Creating A Formula Based On Dimensions In A Single Cell

Dec 10, 2009

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

Creating A Formula To Calculate A Result That Depends On 3/4 Criterias Been Met

Apr 28, 2008

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

Creating Formula To Rank Certain Cells In Reverse Order

Aug 23, 2012

creating a formula to rank certain cells in reverse order.


Cell Total Rank
B1 = 2456 3
B15 = 2369 2
B30 = 2547 5
B45 = 2141 1
B60 = 2471 4

Rank every 15 cells basically in reverse order.

View 9 Replies View Related

Creating A Formula: Verify If My Actual Value Falls Within A Specific Range

Jun 6, 2006

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

Formula To Handle 5 Fields Of Data To Avoid Creating Extra Columns?

Nov 27, 2009

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

Finding Unique Values And Creating Shorter Table (sublist) Using Formula

Apr 25, 2013

I have a spreadsheet with some calculations and the part i am interested in is data in R4 : V58

In this R4:R58, S4:S58 and T4:T58 have some rows that have False because of IF calculations that put False in some rows of R and hence in S and Hence in T.

For example, R15="FALSE" so will S15 and T15 be. Col U is based on whether the corresponding row in R is False or a number. If a Rown in R is false, the row in U= 0 else the row in U is the same as in the same row of a different column - Col I

Col R, T and U are numbers. Col S is text

For example:"
False False False 0
False False False 0
False False False 0
55 DEF 3 15
46 XYZ 2 67
False False False 0
False False False 0
23 GEF 4 43

I want a table in another worksheet that will sort through this using a formula and print

Col1 Col2 Col3 Col4
55 DEF 3 15
46 XYZ 2 67
23 GEF 4 43

If I can define things like where the table will be placed in the new worksheet etc, that will be a bonus..But It appears I cannot understand how to do this at all..

View 3 Replies View Related

Creating A Customized Accumulative Code Week (modified WEEKNUM) Formula

Jul 4, 2008

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

Circular Reference: Cell References In The Formula Refer To The Formula's Result, Creating A Circular Reference

Aug 14, 2006

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

Creating A Range After Creating A Different One

Dec 22, 2008

I am trying to build a selection for a procedure to use.

View 2 Replies View Related

Creating Add-ins

Nov 12, 2008

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

Creating PDF Of More Than One Sheet?

Mar 28, 2014

I have an issue creating a PDF of more than one sheet. Only top left corner of sheets are shown in PDF. Creating a PDF of a single sheet works perfectly. I have attached the result (2 pdf-files) and the original Excelfile.

Code for one sheets (Result OK):

[Code] ......

Code for multi sheets (Result not OK):

[Code] .........

Attached Files


View 2 Replies View Related

Copyrights 2005-15, All rights reserved