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.

please help.

thanks!

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.

please help.

thanks!

- Compute a score based off many variables
- Compute for the total commission and gross pay
- Optimise computer for EXCEL use
- I need a template for scoring tennis matches
- Formula for weekly average & changing daily formula
- Help with this table/data
- Formula and computation for timesheet with workschedule
- Computing for number of hours under Night Differential
- Golf Scores: Average x Lowest of Last x
- Formula needed for weighted average
- Calculating average scores from multiple sheets' information
- How to Differentiate between a Student's Score of "0" and a Score of Null?
- Calculating average scores from multiple sheets' information
- Fill-down for the AVERAGE function, problem
- "IF" formula not computing the way I want it to
- Compute total score based on student number
- Macro for moving average and charting
- Creating a scoring userform based on list text-Value datasheet
- Average based on condition
- Pivot Tables: grandtotal column needs to be for both average
- Using loops for taking averages
- Average Handle Time
- Calculated Weighted & Conditional Average

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 gross pay = net pay + total commission

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

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

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.

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

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.

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

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

View this thread: http://www.excelforum.com/showthread...hreadid=476554

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

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.

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.

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

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!

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

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

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?

Thanks for your help.

Ps: i am a novice in VBA

Column 1 Column 2

buy 100,000

buy 150,000

sell 225,000

buy 160,000

sell 180,000

sell 250,000

buy 140,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

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:Now, clumsy programming aside, I just want to say something like thisRange("A2").Select startCell = ActiveCell.AddressLocal ActiveCell.offset(3,0).select stopCell = ActiveCell.AddressLocalIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

VB:unfortunately, that's not working. Can anyone help me out before I throw myself through a window?averageCell = AVERAGE(startCell:stopCell)If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks, folks.

~Matt in the Hat

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-

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.

No luck finding an answer? You could always try Google.