Free Microsoft Excel 2013 Quick Reference

- Conditional weighted average
- How do I keep a running total of pounds lost and percentage lost
- Ranking (weighted) issue
- Weighted Average with NA...can't calculate
- Weighted Average Formula Population
- Weighted scoring formula to exclude not applicables
- A series of weighted variables for a percentage ranking
- Constant Weighted Average with IF Function.
- If Statement Erroring at Runtime
- Need Help Making a Weight Lifting Spreadsheet
- Overall Score Formula
- Percentage spreadsheet
- Assigning different weighting to score categories in an Excel gradebook
- Weighted average calculation in pivot table
- formatting percentages
- Percentage doesn't add up
- Weighted Average of Time
- Weighted average including N/A
- Weighted Average with CTR
- Calculating percentage using data from other worksheets?

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?

table that will show each persons weight each week, total loss/gain and

percentage of loss. Can anyone help me?

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!

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!

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.

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

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???? )

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!

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 IfEscSum, 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.

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.

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.

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'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

Thanks for your help.

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.

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!

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.

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

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%

('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)