Hi there,
(First time poster!)
I'm working on a pretty large file. I want to calculate a weighted average on the left hand side of my excel spreadsheet. I have several tables (15 to be exact) and each table has 10ish columns. I want to calculate a weighted average using just 2 columns from each of the 15 tables.
I have a QTY column and a % Column. I also inserted a "TOTAL QTY" column in the very left hand side, totaling my QTYs (this worked fine). So, I had tried to do this manually and take the following general formula: ((QTY/Total QTY)*%)+ ((QTY/Total QTY)&%), etc for each of my 15 sets of numbers. This is given in the copy/paste of formula below.
=(((Table25[@[Total Quantity Sold]]/[@[Total Quantity Sold]])*Table25[@[Profit Dollars v. Baseline]])+((Table26[@[Total Quantity Sold]]/[@[Total Quantity Sold]])*Table26[@[Profit Dollars v. Baseline]])+((Table27[@[Total Quantity Sold]]/[@[Total Quantity Sold]])*Table27[@[Profit Dollars v. Baseline]])+((Table28[@[Total Quantity Sold]]/[@[Total Quantity Sold]])*Table28[@[Profit Dollars v. Baseline]])+((Table29[@[Total Quantity Sold]]/[@[Total Quantity Sold]])*Table29[@[Profit Dollars v. Baseline]])+((Table30[@[Total Quantity Sold]]/[@[Total Quantity Sold]])*Table30[@[Profit Dollars v. Baseline]])+((Table31[@[Total Quantity Sold]]/[@[Total Quantity Sold]])*Table31[@[Profit Dollars v. Baseline]])+((Table32[@[Total Quantity Sold]]/[@[Total Quantity Sold]])*Table32[@[Profit Dollars v. Baseline]])+((Table33[@[Total Quantity Sold]]/[@[Total Quantity Sold]])*Table33[@[Profit Dollars v. Baseline]])+((Table34[@[Total Quantity Sold]]/[@[Total Quantity Sold]])*Table34[@[Profit Dollars v. Baseline]])+((Table35[@[Total Quantity Sold]]/[@[Total Quantity Sold]])*Table35[@[Profit Dollars v. Baseline]])+((Table36[@[Total Quantity Sold]]/[@[Total Quantity Sold]])*Table36[@[Profit Dollars v. Baseline]])+((Table37[@[Total Quantity Sold]]/[@[Total Quantity Sold]])*Table37[@[Profit Dollars v. Baseline]])+((Table38[@[Total Quantity Sold]]/[@[Total Quantity Sold]])*Table38[@[Profit Dollars v. Baseline]])+((Table39[@[Total Quantity Sold]]/[@[Total Quantity Sold]])*Table39[@[Profit Dollars v. Baseline]])+((Table40[@[Total Quantity Sold]]/[@[Total Quantity Sold]])*Table40[@[Profit Dollars v. Baseline]]))
It looks more complicated then it is! I promise
But, I get a #Value! error. I think this is because some tables are blank. This isn't something where I can manually calculate it because I have 15k rows it needs to be done for.
Any help is much appreciated!!
Kendall
(First time poster!)
I'm working on a pretty large file. I want to calculate a weighted average on the left hand side of my excel spreadsheet. I have several tables (15 to be exact) and each table has 10ish columns. I want to calculate a weighted average using just 2 columns from each of the 15 tables.
I have a QTY column and a % Column. I also inserted a "TOTAL QTY" column in the very left hand side, totaling my QTYs (this worked fine). So, I had tried to do this manually and take the following general formula: ((QTY/Total QTY)*%)+ ((QTY/Total QTY)&%), etc for each of my 15 sets of numbers. This is given in the copy/paste of formula below.
=(((Table25[@[Total Quantity Sold]]/[@[Total Quantity Sold]])*Table25[@[Profit Dollars v. Baseline]])+((Table26[@[Total Quantity Sold]]/[@[Total Quantity Sold]])*Table26[@[Profit Dollars v. Baseline]])+((Table27[@[Total Quantity Sold]]/[@[Total Quantity Sold]])*Table27[@[Profit Dollars v. Baseline]])+((Table28[@[Total Quantity Sold]]/[@[Total Quantity Sold]])*Table28[@[Profit Dollars v. Baseline]])+((Table29[@[Total Quantity Sold]]/[@[Total Quantity Sold]])*Table29[@[Profit Dollars v. Baseline]])+((Table30[@[Total Quantity Sold]]/[@[Total Quantity Sold]])*Table30[@[Profit Dollars v. Baseline]])+((Table31[@[Total Quantity Sold]]/[@[Total Quantity Sold]])*Table31[@[Profit Dollars v. Baseline]])+((Table32[@[Total Quantity Sold]]/[@[Total Quantity Sold]])*Table32[@[Profit Dollars v. Baseline]])+((Table33[@[Total Quantity Sold]]/[@[Total Quantity Sold]])*Table33[@[Profit Dollars v. Baseline]])+((Table34[@[Total Quantity Sold]]/[@[Total Quantity Sold]])*Table34[@[Profit Dollars v. Baseline]])+((Table35[@[Total Quantity Sold]]/[@[Total Quantity Sold]])*Table35[@[Profit Dollars v. Baseline]])+((Table36[@[Total Quantity Sold]]/[@[Total Quantity Sold]])*Table36[@[Profit Dollars v. Baseline]])+((Table37[@[Total Quantity Sold]]/[@[Total Quantity Sold]])*Table37[@[Profit Dollars v. Baseline]])+((Table38[@[Total Quantity Sold]]/[@[Total Quantity Sold]])*Table38[@[Profit Dollars v. Baseline]])+((Table39[@[Total Quantity Sold]]/[@[Total Quantity Sold]])*Table39[@[Profit Dollars v. Baseline]])+((Table40[@[Total Quantity Sold]]/[@[Total Quantity Sold]])*Table40[@[Profit Dollars v. Baseline]]))
It looks more complicated then it is! I promise
But, I get a #Value! error. I think this is because some tables are blank. This isn't something where I can manually calculate it because I have 15k rows it needs to be done for.
Any help is much appreciated!!
Kendall
