Free Microsoft Excel 2013 Quick Reference

# computing for scores (average)

Hi,

im having problems as to what formula to use in excel. I want to get the average scores of a person for 6 months rolling data but seems that I cannot find the correct formula to use.

**the person may be listed on a different cell per month as they are ranked.

thanks!

## Related Results

### Compute a score based off many variables

I'm having a hard time creating the math formula to make this project work.

Max possible points for each category are as follows:

Abdominal Circumference: 20 points
Pushups: 10 Points
Situps: 10 Points
Run Time: 60 Points

Any of the above may be exempt except the Abdominal Circumference. If any of the above are exempt then the math formula would be as follows:

(20 + Exp (pushups) + 10 + 60)/90*100 = 100

So for example a member scores 16 points for abdominal, Exempt from pushups, 8 points for situps and 48 points for run time it would look like:
16 + 8 + 48 = 72
(72/90)*100 = 80 points

Where I'm totally confused is how to write a formula that will test any exempt variable and produce the correct score. So in the above if a member is exempt from both pushups and situps but completes the run and abdominal I need to compute a score. Or in another example, exempt from the run but completes the other 3.

Is this possible to write a formula in one cell? I'm trying to create a table with all this so it can be sorted and used as a possible pivot. I created this before with another spreadsheet but had many differnet hidden cells to accomplish this. I'd like to run all possible variables in the "Score" cell instead of having hidden cells.

Thanks in advance. I'll rep all that can help with this.

### Compute for the total commission and gross pay

formula: compute for total commission = total sales * commision
compute for the gross pay = net pay + total commission

### Optimise computer for EXCEL use

is there a guide anywhere to optimise a computer for EXCEL ?

thanks a lot

### I need a template for scoring tennis matches

I need a template for scoring tennis matches.

### Formula for weekly average & changing daily formula

Hello

I am trying to use a formula for devising averages but having a few problems, so was wondering if anybody could help please.

I want to make a weekly average. So i want to use data from the past week, is there a way for excel to only extract data from data linked to the current date so only data from that week is used? ie if i post a total on the 5th november it will only use data from that week 1st -5th novemeber?

Next question is i have a formula

PHP Code:
```(SUM(\$B\$4:\$B\$500))/(COUNTIF(\$B\$4:\$B\$500,">0"))

```
say on the 1st november this will do the average of all the days before the 1st november, but if i were to make a new entry for the 2nd november, it changes the 1st novembers average, i dont want it to include any entries afterwards, is this possible?

Any help really would be appreciated.

Kind regards

Joe

### Help with this table/data

hi im a noob when it comes to excel and i have a data sheet that i need to complete so i hope you guys can help.

i was assigned to make a computation for our individual scores using this:
EMPLOYEES A-N

inputs:
SURVEY(0-100)
LH(lost hour)
AHT(average handling time)

compute for:
SCORE
RANK

the SCORE has a total of 100%
so to get the SCORE, each input gets a percentage: SURVEY=40% LH=30% AHT=30% then added.

but the thing is LH and AHT has an equivalent.

LH
0 to 1=100
1.1 to 2=90
2.1 to 3=80
3.1 to 4=70
4.1 to 5=60
5.1 above=20

AHT
0 to 14.50=100
14.51 to 15.50=90
15.51 to 16.50=80
16.51 above =20

EXAMPLE:
EMPLOYEE A
SURVEY(0-100) = 33.00
LH(lost hour) = 12.00
AHT(average handling time) = 14.20
SCORE = CODE
RANK = CODE

now for the rank i just use the code =RANK(SCORE A, SCORE A:SCORE N)

im lost on how to compute the SCORE though coz i can just use =((SURVEY*40%)+(LH*30%)+(AHT*30%))

but the one that needs to be on the LH and AHT are the equivalents and not the inputs.

and there is 1st 2nd and 3rd place. the name of the top 3 employees should be shown under the table.

i made an incomplete xls and i hope you can help me complete this.

thanks guys

### Formula and computation for timesheet with workschedule

Am working on my time sheet. The problem is I can't compute for the tardiness and under time. Also I have to consider the working schedule in order to come up with the correct computation for it. sample below is the correct computation:

work schedule: Monday to Friday 8:30am to 5:30pm

time in time out tardy under time
employee name: 08:45 AM 04:30 PM .15 1.00

Because of the work schedule I can't come up with the correct result.

Thanks in advance. any help will be appreciated.

### Computing for number of hours under Night Differential

Hi,

How do i compute for the number of hours that fall into the Night Differential hours within an employees shift? In one cell is the shift start time of an employee and on the other cell is the shift end time. There is no constant number of hours that an employee should complete. Night Differential time starts at 10:00 PM and ends at 6:00AM.

Thanks

### Golf Scores: Average x Lowest of Last x

I would like to set up a Excel spreadsheet for golf handicaps where it will use
the 4 lowest scores of the last 5 entries. It must be able to work even if
there are missed entries (someone didn't show up/blanks ignored). I would
like it to work if there are only three scores used at the beginning of the
season, but when there are 5 or more scores, use only the 4 lowest scores out of the last 5. I want it to average these scores. The scores would start in column d. The following is an example of scores with an underscore being a blank:
Golfer 1 45,50,42,43,46,37,45,45,46,44
Golfer 2 41,43,42,_,41,_,_,47,42,39
Golfer 3 44,43,45,54,_,45,43,40,42,_
Our season is 18 weeks long, but I don't think that should matter with this
formula. Can you help me with this problem? Thank you.

### Formula needed for weighted average

Hi, I'm new to making a gradebook in excel. I have to design a grade speadsheet so my students can track their scores in future terms, for a final average.

4 terms, weighted 16%, 18%, 37%, and 29%. I have a formula of =(A3*.16)+(B3*.18)+(C3*.37)+(D3*.29). Trouble is, all term cells must be entered to get an accurate average. Is there a formula that could be used to keep a running average starting from term one? Thanks!!!

RH

### Calculating average scores from multiple sheets' information

I'd like to create a sheet that shows average scores for each individual
with scores in other worksheets. This 'averages' sheet is in sorted name
sequence, as are all of the score sheets.

1. Using the name of the individual on the 'Averages' sheet, I'd like
to find all scores in one specific column across all other scoring
sheets.
2. Also, if a sheet contains no score (blank or zero) for that
individual, then don't factor that into the average. Using just this
AVERAGE function below, returns an invalid average if an individual has
only 2 out of 3 scores:

=AVERAGE(Week034Week01!D6)

I'm thinking I need to use a VLOOKUP function (SUM also???), but
haven't had much success getting past the invalid syntax message when
attempting to nest the function.

--
quailhunter
------------------------------------------------------------------------
quailhunter's Profile: http://www.excelforum.com/member.php...o&userid=27739

### How to Differentiate between a Student's Score of "0" and a Score of Null?

Hi Excel-lent People,
I am trying to average assignment scores in a gradebook. Assignment
scores are entered into cols starting with AO. On row 10 are the max
points possible for assignments (for instance, AO10 may be 20 for 20
points poss). Students begin on row 13, and, if students do not need
to do an assignment, I will leave their cell blank for that
assignment. I cannot solve how to write a formula that will exclude
null cells when max values are still present in row 10. Here's what I
have:

Formula in O13 to compute Assign. Points Possible:
=SUMIF(AO13:AO13,">0",AO\$10:AO\$10)

Formula in P13 to compute Assign. Points Earned:
=SUMIF(AO13:AO13,">0",AO\$13:AO\$13)

Formula in Q13 to compute Assign. % Earned:
=AVERAGE(IF((\$AO13:AO13"")*(\$AO\$10:AO\$100),\$AO 13:AO13/\$AO\$10:AO
\$10))*100

**Here's the key--the student in row 13 may get a 0 on an assignment--
AO13 would = 0 and then Q13 would then = 0.00, which is the same
result as if the student didn't have to do the assignment. Other
formulas will use this value of "0"

If AO13 is left blank, how can this formula produce the desired
result?

I would like to simply figure each assignment percent for each
student, then average all of the assignments for a student during a
quarter.

Thanks a bunch!!!

### Calculating average scores from multiple sheets' information

I'd like to create a sheet that shows average scores for each individual with scores in other worksheets. This 'averages' sheet is in sorted name sequence, as are all of the score sheets.

1. Using the name of the individual on the 'Averages' sheet, I'd like to find all scores in one specific column across all other scoring sheets.
2. Also, if a sheet contains no score (blank or zero) for that individual, then don't factor that into the average. Using just this AVERAGE function below, returns an invalid average if an individual has only 2 out of 3 scores:

=AVERAGE(Week03:D4Week01!D6)

I'm thinking I need to use a VLOOKUP function (SUM also???), but haven't had much success getting past the invalid syntax message when attempting to nest the function.

### Fill-down for the AVERAGE function, problem

Hi!

This is my first post, here goes...

I am trying to create a table showing the yearly average occupancy rates per quarter for every year between may 1998 and december 2008 inclusive (for licensed hotels, motels and guest houses, and serviced apartments in Australia; if that interests you).

I have a spreadsheet with a row of data showing the appropriate quarterly results for those dates. Let's say the data for this is displayed A1 through to A44.

For the yearly per quarter averages, let's say they are to go from B1 through to B11, I know I could put, for each year, an average formula with the relevant data range. Finding this to be tedious, I tried to fill-down.

I learnt the hard way however (not being too aware of how excel's grammar ticks) that the fill down function will not work for me. i.e., if B1 contained the average for the data range A1:A4 (the four quarters of 1998), the filled-in B2 would have the data range A2:A5 (i.e. the 2nd quarter of 1998 through to the 3rd quarter of 1999).

The actual question I am working on bores me, but finding a way around this is pretty interesting. I've been looking about and asking friends (you know, those self-titled 'computer-savvy'-type friends...pfft) for hours now. I could've easily done the work cell-by-cell I guess. I could've really slogged it out and finished it by now...

But that's of minimal consequence.

I'm sure there's an answer and it's just pains me to let it slip by. Maybe it's so simple that there are those who would read this and just chortle to themselves, shaking their heads and rolling their eyes. Clucking their tongues even. I don't know what people do in these situations.

Anyway, in short, how would it be possible to make a formula that can be filled-down with the data range beginning after the previous one ended, and so forth?

Please, sorry and thank you so very much... just for reading, even.

Cheers.

### "IF" formula not computing the way I want it to

The "/3" part of the formula is not working.
What I'm trying to do is compute the Test Average for each student. If the student has perfect attendance they get to drop their lowest test score & average the three highest test scores, if not then all four test are averaged.

The formula is working for people without perfect attendance, but I must be doing something wrong with the "/3" part.

Thank you,
Trina

### Compute total score based on student number

Hi guys/gals,

I want to compute the [Total] and [Number of Attempts] each student received based on three separate tests.

Here is a sample:

[Student Number] [Score]
1299375 2
1299375 1
1299375 1
1300508 1
1300508 1
1300607 0
1300904 0

I would like the output to look like this:

[Student Number] [Total] [Attempts]
1299375 4 3
1300508 2 2
1300607 0 1
1300904 0 1

Have also attached full file I want to manipulate.

Thanks for the help!

### Formula Help!!-compute the student’s test average

I am doing a Project for one of my classes and this is what I have to do:

Click in cell G4 and type the formula to compute the student’s test average, and drop the lowest grade if the student has taken all five exams.

What Formula do I use?

Also, I need to Create a formula in cells J3 through J18 that calculates the students’ grade and displays
the word Pass when students are taking the course for credit only and pass.

I know you do not have all the information and numbers infront of you but anything will help.

Please help! This project is killing me, I've been infront of the computer for hours trying to figure it out

### Macro for moving average and charting

I have the following in my file,

Sheet 1 is a typical output consisting of five columns (A,B,C,D,E) that I get from my experiment. For my data analysis then, I do a two point moving average ( in Sheet 2) and three point moving average (in Sheet 3) of all the columns in Sheet 1 and plot a graph that consists of two series (i.e. Col B (X) vs Col C (Y) and Col D (X) vs Col E (Y) ). The formula for two and three point average that i use are

Two point moving average: =AVERAGE(INDIRECT("Sheet1!A"&(ROW(A4)-1)*2+1&":A"&(ROW(A4)-1)*2+2))

Three point moving average: =AVERAGE(INDIRECT("Sheet1!A"&(ROW(A4)-1)*3+1&":A"&(ROW(A4)-1)*3+3))

My file size exceeds the limit that can be posted and hence could not attach a representative file.

I have hundreds of files like this and inorder to save time and automate the processing I tried recording a macro to do the task for me. But then I encountered many errors and coming from a non-programming background I had no clue what was going on.

Can someone please help me with this. Can a macro be written that can perform this? Any help is greatly appreciated.

Much thanks,

Karthik

### Creating a scoring userform based on list text-Value datasheet

Hi,

I am new to the forum and would like to share my issue.

I have this project to create a userform with comboboxes associated to a list of options. Each option counts for a numerical value. I already created the userform in VBA and associated the combobox to a named list.

For eg: "Age group", if user chooses 20-30 it scores 8, if user chooses 30-40 it scores 10, etc...for about 20 questions with multiples choices + tickboxe choice.

How can i link the list of options to numbers and compute a score that sums all the value in a field when the user press OK at the end of the form. what is the coding for each Combobox i should put?

Ps: i am a novice in VBA

### Average based on condition

Here is what I would like to do and can't quite figure out.

Column 1 Column 2
sell 225,000
sell 180,000
sell 250,000

I would like to automatically compute the "buy" average only in column 2 and then also do the same for the "sell" average.

Thanks,

Mike

### Pivot Tables: grandtotal column needs to be for both average

I am using pivot table and on the grand total column I want to have a row to have average instead of sum. For example, i have data of headcount, revenues, hours by weeks and on the grand total column, I want them to be sum of all weeks but not for headcount. For headcount, I want to have headcount by weeks but the grandtotal column, I want it to be average of all weeks in the month. What can I do? Thanks.

### Using loops for taking averages

Hello, everyone.

This is my first post on the forum, and I'm looking for some help. This will probably be a breeze for most of you, but I'm stuck and frustrated. . .

I have a column of 96 numbers (observations every 15 minutes for a 24 hour period), and I want to take the average of these numbers in groups of four (the hourly average).

My data starts in cell A2 and goes to cell A97.

My first batch of averages are labeled as follows:

```
VB:
Range("A2").Select
ActiveCell.offset(3,0).select

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```
Now, clumsy programming aside, I just want to say something like this

```
VB:
averageCell = AVERAGE(startCell:stopCell)

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```
unfortunately, that's not working. Can anyone help me out before I throw myself through a window?

Thanks, folks.

~Matt in the Hat

### Average Handle Time

Hi All!
I work for a call center and I need your assistance to effectively & efficiently help my teammates. My main concern is how to compute for each team member's total and average handle time at the end of the shift. Moreover, I need to generate this on hourly, daily & weekly basis.
For example:
Agent1 Agent2
Day1 18:25 12:30 (in minutes)
Day2 78:02 61:35 (in minutes)
Day3 35:08 29:59 (in minutes)
Day4 00:55 02:51 (in minutes)
Day5 21:00 24:47 (in minutes)
TOTAL (in minutes)
AVERAGE (in minutes)

The entries above are in minutes. Please also consider that even if the call goes beyond an hour, it would still be measured in minutes. Input and Output must be in standard mm:ss standard format (not Military.) Kindly shed light as well on how the entries would be put in the cell (i.e., would it be typed as 18:25 or "18:25" or 18.25.) Kindly consider also that total minutes computed on a weekly basis might go beyond 2 characters (i.e., 728 minutes total.) Anything else that you may add or share to make the computation very comprehensive would be greatly appreciated.
Thanks a lot!
-KIKI-

### Calculated Weighted & Conditional Average

The attachment is only a small portion of the data I need to analyse, but is representative of the full set of data.

What I am trying to do is calculate a weighted average of each of the chemicals shown by location and machine usage and also still be able to use the filter or sort funtions. I have created a sample calculation in cell F9 to further explain this calculation.

I would like to be able to use the A-Z sorting icon in any of the columns, while still computing the weighted average of the respected chemicals. As it is now, the referenced cells just remain as the 6-7 cells above the calculation instead of the 6-7 cells that apply to the specific chemical at the location and machine (in this example - caustic at location1 on mch-03). For example, if I try to sort in column F, I'd still like to see my example calculation to reference the same values as it is now, calculating the same resulting 60.719.