# weighted

1. ### Weighted average but with negative values - % return on purchase and sales

I apologize in advance as I will try my best to explain my predicament. I am attempting to find a way to calculate net gain or loss from the purchase or sale of an item. However, by inputting negative values in to the items sold column, it is throwing off my numbers. Initially I was using a...
2. ### Pivot table Weighted average not the sum of its parts - why?

I have a pivot table that is working out weighted average, now my issue is that the Grand total does tie back to the line by line version, any idea why? The Grand total is saying 78.56% but if I calculate it line by line its 78.14% what is causing the difference...
3. ### working backwards from weighted averages

Hello, I have 3 numbers that were given to me that are \$/SF weighted averages. The developer didn't provide the weight factored into calculating each average. <tbody> Total Project \$/SF Average 1st Product \$/SF W. Avg 2nd Product \$/SF W. Avg 3rd Product \$/SF W. Avg 379 398 339 289 % of...
4. ### Div/0 error, ignoring blanks on weighted averages

Hey Guys, First post. Looking for some help. As the title says I'm working on a formula that does weighted averages while ignoring blanks, the formula works fine until I clear the cells and then I'm left with a Div/0! error. I have this so far...
5. ### Weighted average selling price base on 3 conditions

Hi all, Please help me with formula which calculate weighted average selling prices (WAP) based on volume Sheet 2 result <colgroup><col><col></colgroup><tbody> <colgroup><col><col></colgroup><tbody> Product No. WAP 22-400 17.05 22-500 27.22 22-100 20.00...
6. ### Weighted Average for Non-Contiguous Data

Good Morning, I am trying to get a weighted average of data that is not contiguous I am using this formula: =SUMPRODUCT(BL117:BL134,BJ117:BJ134)/SUM(BJ117:BJ134) The data I need to be included in the formula is just the "ut" one, excluding the "m" ones. Columns are BJ, BK, BL and rows from 117...
7. ### Weighted avg

<!--[if gte mso 9]><xml> <o:OfficeDocumentSettings> <o:AllowPNG/> </o:OfficeDocumentSettings> </xml><![endif]--><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:TrackMoves/> <w:TrackFormatting/> <w:PunctuationKerning/>...
8. ### Weighted Average Ignoring Blanks

Hello. I have a spreadsheet which collates scores received and calculates a weighted average based on a number entered into a reference cell. The point of this sheet is to record scores received for projects after they have completed. What I cannot understand is how to get the formula to ignore...
9. ### Weighted average ignore 0%

Hello, For the first time, I am trying to calculate a weighted average. I think I got the weighted average formula correct: =SUM(B20*B25, C20*C25, D20*D25, E20*E25, F20*F25,)/SUM(B25:F25) But we didn't get a score for the cash category, and need the 0% to not be counted. Is there something I...
10. ### subproductif

hi, i want to calculate the weighted average maturity of a list of contracts as at a certain date for multiple dates. In col A, i have the day the contract started, colum b the maturity in years, and column c the amount of the contract. I want to calulcate month end weighted average maturities...
11. ### Weighted Average with Two Weighting Criteria

Thanks for your help in advance... I have to figure out pricing, using a weighted average. But I have two Weighting Criteria. Dates & Distance (Fractions of a Mile). <colgroup><col><col><col></colgroup><tbody> Date of Sale Distance Pricing 11/6/2017 0.12 654.36 7/11/2018 0.21 765.31...
12. ### Weighted Average / IF statement

Hi all, Any help would be appreciated. I am trying to create a way to model some data in Excel. This involves taking three sections (lets call them a,b,c) and their sub-sections (lets call them A1, A2, A3, A4, B1, B2, B3, B4, B5, C1, C2, C3) and adds individual confidence ratings to these sub...
13. ### Weighted Average

Sorry if this post has been answered elsewhere, I searched but did not find the answer I need. Trying to average weighted scores. We have x1 number of students with a y1 GPA, plus x2 number of students with a y2 GPA. How do I manage to get the "total" (blended) average across the two groups...
14. ### Rank issues

Hi There, My Rank formula isn't working properly and I'm not getting the proper results. I don't mind duplicates which i can fix with some weighted adjustments. I'm getting dupes with different point values which has me baffled. Here's my formula: =IFERROR(RANK(AS3,AS3:AS104,0),"") I'm...
15. ### Weighted Avg If using SUMPRODUCT

<tbody> ColumnA(amount) ColumnB(return%) 3100000 .0403 4800000 .1178 9200000 .0403 10300000 .1517 11000000 .0743 11200000 .0462 11700000 .0461 12000000 .0412 12800000 .0485 13400000 .0620 </tbody> Hello everyone, I wanted to to calculate the weighted average based on a...
16. ### Weighted average cost formula required

Hi, I have a series of transactions and need to calculate the average cost which includes both buys and sells. E.g. Purchase 100 shares @£1/share Purchase 50 shares @£1.50/share Sell 25 shares @£2/share Purchase 30 shares @£1.60/share Weighted total cost after each transaction £100 (100 *...
17. ### Weighted Average on School grades

Hello, I have a sheet that has the words "test", "exam" or "lesson" in the cells in range C6:C47. Each of those words has a numeric value assigned in the range AA2:AB4. I would like my formula to convert the words in column "C" to the numeric values and then do a weighted average with...
18. ### Custom weighted average

Hi, I have the planned numbers in cell A1 to B4. A1 - Planned Amount B1 - Planned Rate A2 - 200 A3 - 1770 A4 - 280 B2 - 92.1175 B3 - 91.9803 B4 - 92.07 Actuals are now from A6 - Actual Amount and B7 - Wtd Rates (These are headers) in A7 - 1820 (actual total amount) In B7 I need the weighted...
19. ### Excel VBA for dynamic mean weighted ranking

Hello, I am working on a weighted ranking procedure. I have the procedure written for when it is always rank the top three. The problem is that this needs to be dynamic, it could be any number up to 15 or 20. n is a defined variable and I have a variable called ranknum that identifies how ranks...
20. ### Sub-Weighted Scorecard

Hi there, I'm looking to create a supplier score card which involves weighting & sub weighting - please see example of my score card below. As you can see Logistics Ordering & Packaging have been further broken down in to sub-criteria. I took this template from a website and their way of...