# Automating % Weight Calculation

Mar 16, 2008

I have 1 spreadsheet in which I need to calculate the % weight of some numbers. Columns A to G have data I do not need for the calculation.

Column H has the numbers of whose % weight I need to calculate. The problem is that the no. of rows containing the numbers can change from week to week and I am trying to automate the calculation of the % weights.

So I want try to use column G (as a reference) (like go at the bottom of it so I can know how many rows of numbers there are) and then calculate the % weights.

## Box Weight Average

Feb 14, 2013

A
B
C
D
E

9
et Boxes
Gross Weight
Ice Weight
Box Weight
Net Weight

10
Box 1
60
5.5
4.5
50
d10=IF(B10>=1,4.5,0)

[Code] .......

## Weight Chart

Aug 31, 2007

I am trying to create a weight chart, which works in stones, pounds and ounces. My first query is that each time I enter the weight in for each day, I need it to deduct 1ib 4oz from the weight I enter. At present I have seperate columns for stones, pounds and ounces. However, if I try to do a simple lbs minus 4 it doesn't work, because sometimes the pounds are less than four - so instead of taking the stone column down by one and then deducting the remaining pounds, it just puts the pounds columns to -3 or similar.

Also, I would like my table to work out weight lost over a period of time - so, I have my starting weight at the top and then I want my most recent weight to be taken from the starting weight, leaving weight lost - I can't get this to work either. Is there a way to make it so that the calculation uses the most recent weight entry and does so automatically?

If anyone has any solutions to these queries, I would be very grateful...as I have to caculate this for a number of people and it would be much quicker if it did it all automatically upon entering the most recent weight.

## How To Weight An Average

Nov 21, 2008

I have some aged data, as follows

325days - \$225
20days - \$2,725
16days - \$27,250
11days - \$8,175

Now, if I just take the average number of days ((365 + 20 + 16 + 11) /4) I get 93.0days

However, I want to high value days to carry more weight, than the lower value days.

I gave each set of data a weighted score by dividing the value by the total value, i.e.
\$225 / \$38,375 = 0.006
\$2725 / \$38,375 = 0.071
\$27,250 / \$38375 = 0.710
\$8175 / \$38375 = 0.213

## Dimensional Weight Formula

Mar 17, 2009

I have attached a sheet that contains a pretty simple formula that helps me figure out the dimensional weight of a shipment. It is just a simple vlookup that returns the proper rate (Column H) depending on DIM Weight and Zone. It works great.

The issue I'm running into is with various exceptions that I need to put into the formula. The criterias that I need to put into the rate formula (Column H) are:

1-If the one of the Length (Column A), Width (Column B), and Height (Column C) is greater than 60 I need to add 7.50 to the the returned lookup value in Column H.

2-The second longest side the Length (Column A), Width (Column B), and Height (Column C) is greater than 30 I need to add 7.50 to the the returned lookup value in Column H.

3-If the grith (2x Column C + 2x Column B) is greater than 130 I need to add 45 to the the returned lookup value in Column H.

I'm working with a few IF formulas but I can't seem to get all of them to work at the same time. Does anyone have any thoughts?

## Dimensional Weight Formula II

Mar 18, 2009

calculate dimensional weight (below).

## Adding Weight Major And Minor

Sep 15, 2014

I have two columns. One with the major weight(pounds and one with minor with ounces. The first column would have a 1 the second 8 for 1 lb 8 ounces which I need to combine to say 1.5 Every way I do it it is coming up with 10.5.

## Tracking Weight Loss In Excel

Aug 2, 2008

How do I track my total weight loss in excel? Here is an example of what I am trying to do.

8/1/2008 228lbs
8/2/2008 225lbs
8/3/2008 223lbs

Total 5lbs

I would like use a formula that would subtract my absolute weight loss total day by day in August. Basically, I want the total "cell" to equate to my total weight loss in August. For Example after 8/3/08 the "total cell" should equate to 5 lbs. What formula do I use to do this?

## Calculate The Total Weight Of Any Letter

Oct 18, 2009

I cut alphabet letters out of MDF. I need to calculate the total weight of any letter.
I have a program that will give me the area of any shape. In the picture below I have started with the letter “A”. B7 is the over all area. D7 is the small triangle. G7 is the total area minus the triangle D7. H7 is the thickness and I7 is the total volume.

The weight of my MDF I have set at is 850kg cubic meter. That’s set in A3.

I need the total weight of the letter in J7 to be in grams.
The idea is, I put in the areas and set the thickness and it gives me the total weight.

## Weight Watchers Points Tracker

Apr 15, 2008

I'm trying to make a points calculator and tracker for a diet.

I have the forumla from wikipedia and it calculates the point now I want to record the date, food, meal & Points on a second sheet.

I enter that data in

E1 =Date
E2 = Point (calculated)
E3 = Meal (Breakfast, Lunch, Dinner Etc)
E4 Food (text)
E6 Calories (number)
E7 Saturated Fat (number)

I want to add a button that records this data to another work sheet and clears the inputs I have made.

The following Date should be carried across
E1, E2, E3 & E4 into A2, B2, C2 & D2 (or the next available row)

The following Cells should be cleared (E1, E3, E4, E6 & E7)

## Sum And Weight Average Variable Data

Dec 13, 2008

Hi there, been using this for a number of years now, but never had to post a question - I guess most things are already answered - but this one is a bit more tricky.

I want to calculate a totals and a weighted average based on months from a data set that has duplicate ID's and variable data (attached example).

I would like to calculate the total tonnes by month and the average grade of those tonnes. Using the 'month ID table' as a reference to the 'Raw Data table'.

Is this possible using if and vlookup functions or will require a higher level of programming knowledge?

## VBA Stuff- Weight Watcher Spreadsheet

Nov 4, 2008

I have a Weight Watcher spreadsheet. I enter the Calories, Fat and Fiber and I get the # of points for that food. Then I keep put those points into the lower part of the spreadsheet where I track what I eat, and Excel does my simple math for me. What I want to do is keep a list of my foods, and their point value in my spreadsheet. I would like to be able to put the food name in Cell A1, the WW points in BI, and then be able to click a button with a macro that takes those two pieces of data and add them to bottom of my food list. I don't have a food list yet, I would assume I can just put it on sheet 2. I then want to be able to use lists in my cells to choose the food in the future. I need to be reminded how to make "dynamic named ranges. Today, I just need to know how to write the macro to copy and paste those two cells in the first open row at the bottom of my food list.

## Exponential Weight Volatility Average

Jul 25, 2006

to write a function that will exponentially weight a series of returns by a certain decay factor.

I have excel spreadsheets that can do this over several columns, like this

series of returns... rtns squared
2.50% =2.50%*2.50%
3.50% =3.50%*3.50%
5.00% =5.00%*5.00%
1.01% =1.01%*1.01%
0.75% =0.75%*0.75%
4.01% =4.01%*4.01%
-2.78% =-2.78%*-2.78%

## Unable To Set Weight Property Of Border Class

Apr 22, 2010

I am using the following few lines of code:

Code:

Range("A3:L" & ActiveCell.Offset(1, 0).Row).Select
With Selection

.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlInsideVertical).Weight = xlThin
.Borders(xlInsideHorizontal).Weight = xlThin

End With

However am receiving an error: "Unable to set the Weight Property of the Border Class". The odd thing is, I only receive the error on the following 2 lines:

Code:

.Borders(xlEdgeBottom).Weight = xlMedium
'and
.Borders(xlInsideHorizontal).Weight = xlThin

The sheet is not protected, events are disabled, and the range that gets selected changes dynamically based on the number of rows of data in the table, however in this example cells A3:L15 are selected.

## Use One Series To Color Weight Another In Pivot Chart

Jan 19, 2013

I have a data set that I'm trying to present in a pivot chart and am wanting to use one series to color code or weight another series in the chart. For instance, let's say I have different product categories and two metrics for each, a profit margin (expressed as a %) and a revenue figure (expressed as \$s). I want the height of individual bars to represent the profit margin but have the level of opaqueness represent the revenue figures...ie the more revenue associated with a product the more opaque it becomes.

## Running Total Of Weight Loss & Percentage

Mar 15, 2008

I was wanting to put it on a spreadsheet. Probably a pretty easy deal, but I don't know the formula to use. this is what I want: Joes start weight 200 minus week ones weight, then the original 200 minus week 2's weight and so on till week 8. I want to keep a running total of weight lost in pounds as well as percentage.

## To Calculate That Loss Or Gain As A Percentage Of Their Original Weight

May 1, 2009

I run a slimming club and would like to use Excel to keep records of members weights, losses, gains etc. What I would like to do is enter their starting weight into a cell then each week when they are weighed enter a loss or gain in an adjacent cell. I would then like Excel to calculate that loss or gain as a percentage of their original weight. Is this possible?

## Sum Each Order On Another New Coloum Which I Will Create Called Total Weight

Sep 17, 2009

Hi Guys,
im really stuck and could use one of your expertise.

i have an excel spreadsheet with 65,000 lines of data

i have 9 Coloums in the the file
Despatch DateCustomerDseqOrderLineItemPost CodeVol m3Gross Kg
13/02/09jason00002029095R055TR4 8QQ0.1099280.3313/02/09jason00002034741R043TR4 8QQ0.31621218.313/02/09jason00002034742R064TR4 8QQ0.8793673213/02/09jason00002034743R045TR4 8QQ0.94863684.9
i need a line here to sum it before the next order starts?
13/02/09jason200002033141A157WD18 7QX0.02832113/02/09jason200002033142A096WD18 7QX1.609288213/02/09jason200002033143A125EJAWD18 7QX0.849646.813/02/09jason200002033144A09JJAWD18 7QX0.13732102.8

im trying to create a formula that will sum each order on another new coloum which i will create called total weight

i need it to search the date then the customer name then the gross kg and total them up as there is multiple orders

## IF Formula- Comparing Height Vs Weight Standards Of Individuals

Jan 12, 2010

i am trying to use the following formula in a cell. it is comparing height vs weight standards of individuals....

## Shipping Charge Calculator Based Upon Weight To A Worksheet

Apr 15, 2008

I’m trying to add a shipping charge calculator based upon weight to a worksheet. I was trying to do with with VLOOKUP (my first attempted use of this) so I created a second worksheet called ‘Shipping’ which has the weights in column A (Weight) and the corresponding shipping cost in column B (Price). Up to 70 pounds, column A increases in two-pound increments (A2 through A36, 2 through 70 lbs); from 70 through 150 pounds, the increases are in five-pound increments (A36 through A52, 70 through 150 lbs). The total weight to be shipped is derived from SUM(D2:D30), located on the first worksheet called ‘Order’ in cell D32. In cell E32 I have entered this formula: VLOOKUP(D32,Shipping!A2:B52,2).

My problem is if Order!D32 returns something like 4.5, the value for four pounds is the result, while the six-pound charge should be the result. If I round this value up to 5.0, 5.5 or even 5.9, I get the same result.

I know I’m not supposed to guess at what I need to do, but these are my thoughts: 1) I need to modify the VLOOKUP formula to select the next higher value, i.e., so the 6 lb charge is selected for E32 when D32 contains 4.5 lbs, or the 85 lb charge is selected for E32 when D32 contains 82 lbs, 2) I need to add a ROUNDUP function to the SUM formula; this would need to round up to the next even number up to 70 lbs and then in increments of five above 70 lbs. I don’t know if either approach is correct, and in any case, I couldn’t figure out the syntax for either of these.
I’ve attached a sample spreadsheet, Shipping_Charge.xls.

## Freight Table - Weight And Zone Lookup Return Cost

Mar 4, 2014

I need a courier rate returned based on the zone and weight of the shipment. The rate table has 10 zones (B1:K1) and 12 weight classes (A2:A13)

In the sheet "data & result" I want the cost returned in the yellow cells from looking up the weight (D) and zone (C).

## Calculate Players Ranking Based On Their Weight Categories And Attempts Total?

Jan 31, 2014

I am trying to calculate players ranking based on their weight categories and attempts total, e.g. Category (50kg, 69kg etc..) Rank should be calculated automatically the highest total value of cat. 50kg is 1st second highest is 2nd etc. However, if tow players or more within the same Category have scored same point ranking will be calculated based on Body Weight less comes first and if both have similar weight calculation will be based on Start # first player comes first etc..

Table:
Start #
Name
Body Weight
Team
Category
Total Point
Rank

1
Player #1
49.2
Team #1
50kg
75

## Automating Brackets

Mar 18, 2004

I found a template at microsoftofficeonline.com it's just a bracket. I feel it has some underlying functionality, as they have a template in visio as well, but I can't figure it out.

So, what I'd like to do is have a workbook with say 100 worksheets. Each worksheet will have a person's bracket. Each person will write in all their picks. Their will be a master bracket where the actual winners will go, as the results are posted. When the master bracket is updated, Excel will flip through each worksheet, checking to see if the win in the master bracket matches the individual bracket. Points will be awarded or not.

Can that be done? I've also done a google search and can't get anymore help. I know that the web has multitudes of free versions, which I'd use, the only problem is that people would hesitate to register. doing it in excel, on the office system, there's no need to register, your cpu login takes care of authentication.

## Automating Graphs

Jun 30, 2006

I'm trying to make alot of graphs(400) and I'm not really sure where to start but here with what I've got so far.

Sub macro2()
Dim x As Long, t As Long, y As Long, z As Long
y = 3
z = 5
x = 4
While x < 1000
With Worksheets("cabernet (2)")
t = Cells(x, 1).Value
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("cabernet (2)").Range("By:Hz"), PlotBy:=xlRows
ActiveChart.Location where:=xlLocationAsObject, Name:="Cabernet (2)"
ActiveWindow.Visible = False
ActiveChart.HasTitle = True
ActiveChart.charttitle.Text = " " & t
Windows("complete Favorite Genes.xls").Activate
y = y + 3
z = z + 3
x = x + 4
End With
Wend
End Sub

I don't really know if putting a variable directly into a range works, so far it doesn't.

As you can tell I need a line graph of the range B3:H5, then the next is the those same columns but 3 rows down, so the next graph is at B6:H8. I'd also like to through in a title here, but I may just be playing with fire. The title is located in column A starting at 4 and corrosponds with each graph, so it too goes is increments of 3, i.e., the first title is at 4 the second at 7 the third at 10. Somebody help me out this isn't working very well at all.

## Automating To Export

Sep 16, 2006

Private Sub Database_Click()
Application. ScreenUpdating = False
masterfile = "S:OfficeMaster File.xls"
Answer = MsgBox("Do You want to export to Final Database?", Buttons:=vbYesNoCancel)
ThisWorkbook.Worksheets(2). Range("q9").Copy
masterfile.Worksheets(1).Range("a4").Paste
ThisWorkbook.Worksheets(2).Range("q9").Copy
masterfile.Worksheets(1).Range("d4").Paste
ThisWorkbook.Worksheets(2).Range("b3").Copy
masterfile.Worksheets(1).Range("b4").Paste
ThisWorkbook.Worksheets(2).Range("b9").Copy
masterfile.Worksheets(1).Range("c4").Paste
ThisWorkbook.Worksheets(2).Range("e9").Copy
masterfile.Worksheets(1).Range("e4").Paste
ThisWorkbook.Worksheets(2).Range("g9").Copy
masterfile.Worksheets(1).Range("f4").Paste
ThisWorkbook.Worksheets(2).Range("i9").Copy
masterfile.Worksheets(1).Range("g4").Paste
End If
End Sub

for some reason, it doesnt work as it said the masterfile.worksheets is not recognized, but as i go to the immediate thingee, i type in ?masterfile, it give me the exact address, i had been working on this thign for hours, and have no idea what i did wrong.

## Automating Xirr

May 7, 2007

I have a very peculiar problem. Each month I have a set of financial products for which I calculate the IRR values using XIRR, as the cash flows are uneven. Imagine this, Product names in ColumnA, the cashflow dates in B, and the cash flows with transaction types in ColumnC and finall D has amounts in negetive and positive. Each month I add new cashflows for each product as rows.

Manually I scroll down to select the range for XIRR (both date and amount)
Offlate this work has gone really high in terms of values, and manually cacluating XIRR takes hell lot of time for all the products (currently 80 odd)

## Automating Data Processing

Jul 25, 2008

I have a large number of sheets with data in the following general format:

x ..... y ............ z etc

A Unique value Unique value
A Unique value Unique value
B Unique value Unique value
B Unique value Unique value
C Unique value Unique value
C Unique value Unique value
D Unique value Unique value
D Unique value Unique value
E Unique value Unique value
E Unique value Unique value

In seperate sheets, I want the following:

x y1 y2
A
B
C
D
E

x z1 z2
A
B
C
D
E

And so on for a number of columns - I always want column "x" with another column, however I want the two values for the same value of x to be next to each other, not above one another.

## Automating The Removal Of Certain Data

May 6, 2009

Just wondering if any one can help me come up with some code to remove some lap time in my document ....

## Automating Weekly Formula Changes

Dec 11, 2009

The attached workbook is a follow up report that gets run every Friday. There are 1092 cells that contain formulas that reference a file that was created on Wednesday of the same week. eg.

## Automating File Name Via Inputbox

Jun 11, 2007

I am trying to do is have the code display an input box asking the user to type in the file name, or generate this automatically? to an existing folder in a drive

My master file, imports data from another source (excel file) pastes it in the necessary columns at the moment, however the file is run every Monday so the file name is generic, last week was 10723, this week will become 10724 when the data is run next week,

Can I do what I want to? Via either an inputbox for the file name, or by using something so the file name becomes “107 “ & Weeknum(today()-3)