Free Microsoft Excel 2013 Quick Reference

Weighted percentage Results

I have two columns. One has cost (column A) and the other has profit (column B). I'd like to calculate the percentage profit as a function of how much something cost. So, if it cost $100 and the profit was $20 I would like to calculate 20%. For all percentages, I'd like to average them. I understand this is a "weighted average" and was able to get this working with this formula:

However, I don't want to include entries where the profit column has a negative value.  I tried something like this:

This doesn't seem to work quite right as it's not ignoring the cost entry when the profit entry is negative.  Any advice?

We're having a weight loss competition at work and I'm trying to set up a
table that will show each persons weight each week, total loss/gain and
percentage of loss. Can anyone help me?

Hello

having some trouble trying to rank different criterias on percentages

bascially, i have different metrics for sales ppl i.e. KPI NAR, i would that to be 30%

i have attached a sample workbook with the outlined sample percentages, however im not sure how to rank based on percentages?

so for example

KPI NAR
john doe $300
is it simply $300 * 30% * rank(A1,B1:B4,0)
*this is just a made up example*

can someone pls help!

I am looking to get weighted average at the very end of the spreadsheet, but due to NA (some items not applicable) the forumal is giving me error ie., #VALUE!

The reason for this is that it is including the text NA into calculation, whereas I want to calculate the weighted average, can anyone please help me in finding the solution to this problem.

Problem statement:
The Total Score should exclude NA, and should give weighted average percentage for percentages shown for Data Entry & Underwriting only.

Thanks in advance!

Hi,

See attached picture. This shows Profit and Growth for the stores in each region.

The percentage total is weighted based on the profit of each store in the region.

What I have is an excel sheet with hundreds of regions (going down), all with a different amount of stores. I need a way to populate each of its Region % Total with the weighted average formula (in my example, cells C8, C12, and C20) while also varying the formula to include only the stores in the region.

For example, C8 calculates the weighted average for %s in rows 2 through 7 (6 stores), but C12 calculates it for rows 9 through 11 (3 stores). I need the formula to vary based on the number of stores.

Can anyone help? Thanks.

Hi All,

I am stuck big time with this. The attached shows a report to collect data for an evaluation. A score of 1 is entered against each category depending on the outcome of the call. The part where I am stuck is if a category is N/A I want the total percentage score to not take this into consideration. As an example. All the categories could score excellent with the exception of telephony skills which is N/A. In this case, the score is 96% but I want the calculation to only include those categories where the whole section is not N/A.

Really hoping that someone can assist with this. I know there are enough bright sparks out there to dig deep and help me through this!!!
Thanks in advance guys

Hello,

I've attached the excel file that showcases the break out.

Ultimately there 6 vendors (labeled A - F) the various categors and each vendors ranking within each out 6 categories left to right. How do I calculate a combined weighted ranking formula?:
- Category 1, weighted 20%, - (value 1 - 100)
- Category 2, weighted 20%, - (value 1 - 5, w/ 5 being the highest)
- Category 3, weighted 20%, - (value 1 - 5, w/ 5 being the highest)
- Category 4, weighted 20%, - (value 1 - 100)
- Category 5, weighted 10%, - (value 1 - 5, w/ 1 being the highest)
- Category 6, weighted 10%, - (value 1 - 5, w/ 5 being the highest)

Categories (weighted)>>>>>>
A 75 4 2 55 5 4 = (A % Ranking result Formula???? )
B 65 2 5 70 4 3 = (B % Ranking result Formula???? )
C 70 4 4 66 3 5 = (C % Ranking result Formula???? )
D 45 1 3 40 3 3 = (D % Ranking result Formula???? )
E 55 3 3 52 5 3 = (E % Ranking result Formula???? )
F 45 1 4 80 2 4 = (F % Ranking result Formula???? )

Assume I have columns C1:C4 as
A
B
B
B

And corresponding percentages as columns D1:D4
75%
89%
70%
96%

Is there a way that I can use the IF function with an array of some sort to make the averages of all the percentages corresponding with "B" be weighted 100% and all corresponding with "A" only be worth 73%?

I feel like I've done things like this before, but it's never been two constant weights that are not defined on my spreadsheet anywhere. I think it's defining them in the function that's throwing me off.

Thanks!

Hi all,

I am attempting to create what my user is calling an escalator. If the escalator option is selected on my user form, the user can tweak the weight percentages. The sum of these weights must = 1 (see picture[I know you all love pictures]). I have the following code that is supposed to sum the weights and let the user know if they are not equaling 1. The problem I am having, and I must say that it is not all the time, is; if the user's weights = 1, the error message comes up saying that the escalator should = 1, the current sum of values =1. ???? If the sum =1 then everything SHOULD be okay. The code:
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''Check the Escalator labels to ensure all numbers = 1 '''
    EscCnt = 0
    If chkBxEsc.Value = True Then
        For lblCnt = 1 To 5
            If Me.Controls("Esc" & lblCnt).Enabled = True Then
                EscCnt = EscCnt + 1
            End If
        Next lblCnt
    
    '''''''''''''''''''''''''''''''''''''''''''''''''''
    '''Re dimension the array EscNo() according to  '''
    '''the amount of indices selected (Esccnt).    '''
    ReDim EscNo(EscCnt)
    
    '''''''''''''''''''''''''''''''''''''''''''''''''''
    '''Fill the array EscNo() with the used values  '''
    '''from the Escalator text boxes and sum each   '''
    '''value together in EscSum.                    '''
    For EscCntr = 1 To EscCnt
        EscNo(EscCntr) = Me.Controls("Esc" & EscCntr).Value
        EscSum = EscSum + EscNo(EscCntr)
    Next EscCntr
    If EscSum <> 1 Then
        MsgBox "The sum of escalation values does not equal 1" & vbNewLine _
        & "The current sum of values equals " & EscSum & vbNewLine _
        & "Please adjust the numbers or click the reset button to set the values to 1", , ""
        FormEntryValidater = False
        Exit Function
    End If
EscSum, when stepping through it equals 1 but
 does not compute right for some reason.  Hopefully someone can see in this little section of code how this is not working
out right for me.  The file can be located here.  Like I have previously stated, this problem does not occur all of the time
which confuses me even more.

I would like to create a spreadsheet that will track my progress over an 8 week period for weight lifting. Here is what I would like for it do:

There will be 8 columns that will represent weeks. Rows will represent specific exercises. Over the course of my 8 week training period, I will enter my start weight and end weight for each exercise for each week.

Now, the part that I don't know how to do. In a 9th column, I would like to have a percentage increase tracker that will increase or decrease as I enter information in the exercise rows.

I hope that makes sense. Any help would be appreciated. Thanks.

Hi Guys,

I need some help on this. Im trying to get an overall score by multiplying a raw score to a weighted percentage. Hmm...here, it goes something like this.

Percentage Weight 30% 10% 10% 50% - Total
Score 1 Score 2 Score 3 Overall
1.00 3.00 2.50 = (Score1*30%)+(Score2*10%)+(Score3*10%)/0.5

Note: Score 1,2 and 3 is using vlookup from a data dump.

Problem:

There are instances that either of the scores don't have any data on it (e.g #NA) that will somehow result a #Value error on the Overall Score(formula).

What i need to to do is that, if one of the score is blank or #NA the formula/function will just distribute the weighted percentage of the blank score to the remaining scores present then will divide it with the total of the weigthed percentage present as well.

Am i making any sense?

Hope you can help me with this.

In cases this is not quite clear please dont hesitate to reply so i can try to re-phrase it.

P.S.
Im not that good with excel.

Please see attached, it's a weight loss spreadsheet for me and some girlfriends.

Note that B10 through K10 for example add up to 7.5037%. But cell K13 is supposed to display that number because it's total weight loss so far divide by starting weight (cell A9)
What am I doing wrong?

How come all weekly percentages don't just add up to total lbs lost so far divided by starting weight? How come those two numbers don't match! Please help!

THANKS!

I'm working on a customized teacher gradebook.

I'd like student names in the leftmost column, and dates/assignments running across the top (first row)

If each cell contains a grade, how can I tell Excel to differentiate between different categories of assignment? (Quiz, Test, Homework, Classwork, Participation, Extra Credit, etc.). I will create formulas to calculate a final grade, but it will need to weigh each category differently. Also, some scores will be reported in percentages, some as total points, etc.

How can I assign each cell (or column of cells in most cases) a specific category, and then take these category weights into consideration in formulas?

I recently bought "The Excel 2007 Bible" so feel free to point me to the appropriate chapter if that's easier.

Thanks

Is there any way to calculate a weighted average of data in a field as part of the pivot table function? The only alternative I find is a simple average calculation, but I would like to calculate the weighted average of the data in a field based on the data in another field. Specifically, I have a table that accumulates data regarding commissions paid, sales amount and income generated by each sale. The table includes the sales amount and the income earned on each sale, as well as other data. The pivot table I created shows the average of the income earned as a percentage of sales. However, each sale is different and a simple average calculation does not give me an accurate picture of the overall percent of income to sales.

Thanks for your help.

Is there a way to format a number of the form 0.### so that it appears as ##.# without the percent sign, without physically changing the value in the cell, (by multiplying by 100 for example).

When we publish tables, we put all of the units at the top of the table column. For example, assume the table shows production and revenue of four products. It has weight shown in thousands of pounds, revenue (shown as thousands of dollars and the percent of total revenue shown as a percent. In general we use custom number formats, but for the percentage we physically multiply by 100. I very much dislike changing the value of the number in order to format it.

Again we always note the units--percent--in the column header.

Hi,
I got a problem with a planning tool I've built to show how many extra sales are needed each month by my teams to hit a target and then an overachieveing target.

I've also applied a weighting to each team to allow for experience. My problem occurs when I then try to add the overachieveing target (eg. 12%) and then apply the weighting to each team, every result is only 96.44% of what I expected it to be.

The sum I'm using to calculate each teams stretched target is
=sum(stretched plan number of sales*team size as a percentage of total staff)*(1+weighting applied to that team, which is also a percentage)

Please help!

Hello all:

I am a little dumbfounded here. Hoping someone can educate me on my middle school algebra again.

I am trying to determine the weighted average of an employees break time. I want an apples to apples comparison of break time percentage as it relates to the total time logged into the system.

For example, let us assume I have an employee who's total login time is 8.5 hours and that employee takes a 30 minute lunch. I then have another employee who's total login time is 9 hours and takes a 1 hour lunch. The net login time for both employees is 8 hours. They both work the same amount of time. The employee who takes the longer lunch shouldn't be punished for taking a longer break if he works the additional time.

For the life of me I can't seem to remember how to calculate the weighted average out to show even percentages. I have attached a spreadsheet with hypothetical hours.

Any help would be appreciated. The old brain isn't working today!

Thank you

EDIT:

I should note that the WT in the spreadsheet means Work Time. So the amount of time worked between IN and OUT.

Hello all.

First time post and I am hoping someone can help me out

I have attached a spreadsheet which I am working on. I have a percentage score for a number of criteria in a call centre. I want to ensure that the user can select N/A when a criteria is not applicable and make sure that the result of this selection does not affect the overall score. I have a scoring of 0%, 25%, 50%, 75% and 100% for each question/criteria. By selecting NA the user should not be skewing the data.
Hope this makes sense.

Many thanks in advance

Andrew

Hi, I am trying to figure out a Weighted Average with two variables and a percentage.
Here is what I am trying to do:
I had 3 samples, that had impressions and Clicks, and the CTR equals clicks/impressions. How do I get a wedighted average from here?

	Impressions	Clicks	CTR
A	 224,389 	         70 	        0.03%
B	 13,203,607 	 29,692 	0.22%
C	 49,792,749 	 213,627 	0.43%
             Average			0.38%


Creating a spreadsheet for "Biggest Loser" contest at our organization (see attached xls). The average Team weight loss (measured in percentage) needs to be calculated in O3, O9, O15, & O21 for each of the 4 teams. For team 1, the math would go as follows:

('1-1'!C4 + '1-2'!C5 + '1-3'!C6 + '1-4'!C7) / ('1-1'!B4 + '1-2'!B5 + '1-3'!B6 + '1-4'!B7)

This formula adds up the starting weight for Team 1 in cells B4:B7 (from each respective worksheet) and divides it by their weight at the end of week 1 C4:C7 (from each respective worksheet). This part is easy enough to make work. However, I need O3, O9, O15, and O21 to stay current, dividing only the most current week with data by the original weigh in. These are as follows:

Column B is your control, the inital weigh in.
Colulmn C is W1 (the end of week 1 weigh in).
Column D is W2
Column E is W3 ... and so on through W12

How do I write a formula or VBA for Column O (Weekly team results) that ignores the weight of previous weeks and only goes based on the most current week. (ie. if column E is blank, then calculate for O based on Column D)