Free Microsoft Excel 2013 Quick Reference

Calculating the average Results

I am trying to calculate weighted average cost for a list of thousands of items.

I will try to explain this as best I can.

Column A is item. Column B is Mode. Column C is Volume. Column D is Cost

There are only 2 Transmode Per Item. So each item is pretty much listed twice and I need to calculate the weighted average cost per item.

I know how to use the SUMPRODUCT function to calculate weighted average and could slowly go through the spreadsheet item by item calculating this, but is there a way to do this with some sort of formula? Similar to a Subtotal that would recognize each change item and calculate the Weighted Average at that change.

I am having the hardest time understanding what is going on with my spreadsheet. I have a spreadsheet that is taking four different data inputs and adding them up to figure out what percentage of that work type as it relates to the average processing times. This is the formula that I am using for 510K the bottom

=((AK11/(AK14/'Workload Distribution'!F13))*(AK9)/(SUM(AK9:AN9)))

F13 being the average processing time of 5 hours.

What I don't understand is the second field for Internal Requests is:

=((AK11/(AL14/'Workload Distribution'!G13))*((AL9)/(SUM(AK9:AN9))))

The percentage that is coming out seems to be problem because when I manually put the percentage in it calculates fine. If I do not the values come out the same even though they are not. Can someone explain to me what I am doing wrong. Thank you very much.

May510kInternal RequestsEIRPMA18300Required Monthly Hours146Hours Logged by Work Type510kInternal RequestsEIRPMA90900Actions Needed by Work Type510kInternal RequestsEIRPMA1146494Break Down TotalPagesBoxesAvg Page TotalAvg Box Total302093020162Hourly Breakdown by Work Type510kInternal RequestsEIRPMA6.946.94

Hi all,

Read the forum daily, and I am sure that there is an easy way to do this, but I don't know the answer.

I have 2 columns of numbers--on column is age, and the second column is number of individuals at that age. I need to take a group of records, summed by age, and calcualte the average age of the group.

So, by example: ( I Apologize for the clunky example, but I cannot post a pic of the sreadsheet from work.)

____A______ B
1 __11_____12
2 __12______8
3 __13_____24
4 __14_____12
5 __21______3
6 __23_____21
7 __24_____21
8 __52_____63
9 __60_____28

How would I calculate the average age of this group?

Thanks in Advance!

I am wondering if there is a way to find out the average of a group based upon a criteria.

For example, in column A I have list of banks
In column B I have their commissions
In column C I have daily daily dates.
In column D I have the sales person's name

My date set has over 500 rows but only 3 columns. There are only 5 different banks but there are multiple sales people

I would like to calculate the average commission by bank. I do not care of the sales person or the date. Is there a:


statement in Excel or something similar that can give me this information. Currently I sort the date by banks and then use the simple average statement. In this case I have to keep on changing the ranges for every bank.

Any simple solution without going throug an SQL macro?

I posted earlier requesting a formula to calculate an average but it used the INT function and my table doesn't use integers.

Anyway, Using the chart below, I want to enter a number that will lookup across the top (length) and another that will lookup down the side (width). If the two numbers match, I want it to return the cross-referenced cell (price).
length = .250
width = .028
price = 46.11

If either of the numbers falls between the given length or width, average the numbers above and below the size.
length = .300
width = .028
price = avg(46.11 + 46.56) = 46.33

If both numbers fall between the length and width, I need the average of all four numbers above and below the size.
length = .300
width = .300
price = avg(46.11 + 46.56 +51.12 + 52.04) = 48.96

0.250 0.313 0.375 0.438 0.500 0.563 0.625 0.688 0.750 0.813 0.028 46.11 46.56 47.02 47.47 48.38 49.30 50.21 52.10 53.96 55.82 0.035 51.12 52.04 52.95 48.38 49.30 50.21 51.12 53.03 56.75 57.68 0.042 56.61 57.52 58.43 52.04 52.95 53.87 54.78 56.75 57.68 58.61 0.049 57.52 58.43 59.35 52.95 54.78 56.61 58.43 60.48 58.61 59.55 0.058 58.43 59.35 61.17 53.87 54.78 58.43 61.17 61.41 63.27 66.05 0.065 63.91 68.47 69.38 54.78 55.69 62.09 66.64 71.64 72.57 73.50 0.072 - 69.38 70.30 58.43 59.35 63.91 70.30 76.29 77.22 78.15 0.083 - - 73.04 62.09 63.00 69.38 73.95 80.01 80.95 81.88 0.095 - - - 63.91 64.82 72.12 75.78 86.52 83.74 89.31 0.109 - - - - - 83.99 84.90 93.97 94.90 95.83

Any help will be greatly appreciated.

Hi, I have a worksheet with race times that was entered in the wrong format.
For example, a time of 24:32.94 is entered in as 243294.

Is there a way to convert this into proper time format mm:ss.00 without manually re-entering all the times? I also need to get the average of these times. I have tried using left, mid, and right functions with characters in between but that creates text and I cannot calculate the average.

Thanks for your help.

Hallo all,

I have the following problem:

I have 8 given numbers, wich are measurements of the sample. So in normal conditions the 8 numbers are +- the same.
When the numbers are the same, I calculate the average and go on with this average.

Now the problem is that sometimes 1 of the numbers is way off because of a fault during the measurement.
This means that I can not use this number when calculating the average.

To find out wich numbers I can use, I use the median : all numbers between the median and +50% of the median and the median -50% of the median are ok.
The other numbers are not ok , and I can not use them to calculte the average.
My question now is: how can I put this in excel?
How can I program it, so that excel will check my 8 numbers, find the median, calculate the +50% and -50% marge and then goes on with only those numbers that are between that interval.

I managed so far to calculate the median, to find the values that represent the median +50% of the median and median -50% of the median.
But then I get stuck.

I know between wich values I need to be, but how do I implent this in excel so that excel know to work only with the values between this interval.

To make it more clear:

given the following numbers:


I calculate the median: 6

+50% = 9
-50% = 3

so only the first number is bad (2)
So now I need to tell excel NOT to use 2 when calculating the average.

Any ideas?

I am using Excel 2000 to build a calculation that will return the average of only the last '##' numerical values in a running list. The '##' number will be determined by entering in a number into a different cell on the page to indicate how many recent numbers must be averaged. For example:

Cells A1:T1 contains the following numeric and text values:
N/A, 40, 42, 44, N/A, 46, N/A, 48, N/A, 50, N/A, N/A
66, 64, 62, 60, N/A, N/A, 58, 56, N/A, 54, N/A, N/A
Cell B1 contains the number of how many recent numbers in the list to be averaged. In this example, B1 is 10. Therefore, I need to average the 10 most recent numerical values in the list.
Cell C1 will contain my answer.

Numbers will continue to be added and I will need to recalcuate the average each time for the most recent '##' numerical values. The newest values will be entered in the right most columns.


I have tried different combinations of IF, SUMIF, SUMPRODUCT, and AVERAGE statements but I can't seem to get it right. I am trying to solve for the following: What is the average number of Hours, per Type (A or B) by Month. Below is a sample of the type of data I am working with. For example, in September, for Type A, the average number of Hours is 3.5. There are some cells that are empty and should not be included in the calculation. For instance, in October, for Type A, the average number of hours is 4.0 (not 2.66). However, I do not want to remove these rows so the data needs to stay in this format. I have several thousands rows of data I am working with. Any help would be greatly appreciated.


Thanks - John

First I think it would be easier for me to post the sheet here but unfortunately something at my work is blocking me from using the add-in, so I'll do my best to explain.

I have a spreadsheet with survey results, each columns are the results of the survey. They are displayed in percentages, and grouped by people whose classes the surveys are about. If Smith gave 5 classes, displayed in colums C:G then column F is a wieghted average (the wieght, number of attendees is in one of the rows)

In column W I am trying to make a total average, but the people I am making this sheet for doesnt want the average of the averages, they want the total weighted average.

This is what I wrote
Where the gaps in the sumproducts are the columns with the individual weighted average. For some reason it is not calculating
the divide par of the equation its just showing the sum of the sum products. Whats happening?

If there is anything unclear about this let me know, and I apologize if it is unclear.

Can someone help me with this?
I have one variable that is a date format (e.g. 1/1/2003 for every date up till today), and then i have another variable that is my rate.
I want to get the average rate for each month of my data. does anyone know if there is a quick way in excel to do this instead of just scrolling down to each month and typing in =average (A1:A30) and so on. thanks!

I am using Office Excel 2007 with XP Pro. I am self taught on computers and office excel formulas but have become stumped with this one.

I require a formula to do an average based on entries not lines in a column.

Will try to explain as best I can as I cannot get the html maker to work so cannot post a example here. If someone wishes me to email them an example please let me know.

Column A 1:99 has various entries through out the column. Active cell will be B90, I need B90 to look up from A90 until it finds 30 entries and then give me the average of those 30 entries. B91 will need to do the same however if A91 does not have an entry then the average result should still be the same as in B90. If we then go to B92 and A92 has an entry then B92 will be use the A92 entry as entry 1 and look up column A until it finds another 29 entries to do the 30 entry average calculation for B92. I hope that make sense.

I cannot use the =avg(A90:A60) formula as there may not be an entry on every line and I need 30 entry average not 30 line average.

I also need a 60 entry and 90 entry average formula as well for another column, but will try to figure those ones out after I get the 30 entry average first.

Again if I havn't explained it or have some confused please ask and I'll try to send you an example.

good afternoon all:
question regarding the calculation of age plus the averages of those ages. I have stumbled upon a formula which allows me to have the birthday of an individual and then spit out the current age.


these rows of birthdates result in a column of ages. I would like to take the average of that column of ages, but am getting a #DIV/0 response.


thanks in advance.

I have a worksheet that gives students a behavioral grade. The worksheet has the school months starting with May then June, July, August, and September for the first semester. Then it starts back at November, December, January, February, and March, which is the months for the second semester. I give the student grades by name in five areas to 20, which the total would equal 100 %. Now, where my problem. If a student starts late, say in August, how can I keep a standard average for the student without entering grades for the previous months. Here are the formulas that I'm using:
May =IF(SUM(R13)=0,0,AVERAGE(R13))
June =IF(SUM(R13,R55)=0,0,AVERAGE(R13,R55))
July =IF(SUM(R13,R55,R97)=0,0,AVERAGE(R13,R55,R97))
August =IF(SUM(R13,R55,R97,R139)=0,0,AVERAGE(R13,R55,R97,R139))

As stated if the student stars in August and has a prefect score of 100, I need the formula to start at that month and not average in May, June, and July. With the formulas here the cell R13, R55, R97 would show "0" for no grade entered. So the "Average" would not be starting at 100 %. Instead it will show 25% for its counting May, June, and July in the "Average". I know that this is possible and I seen it somewhere, but forgot where and how to make this type of calculating work.

I'm trying to create a formula or a system to automatically work out the average of values for me, providing they fit a certain criteria.

Specific example -
I have a number of pH values listed by strain number

COLUMN A: strain no
COLUMN B: batch no
COLUMN C: pH value

I want my worksheet to automatically calculate the average of the values in column c, providing they have an identical value in column a.

The closest i've been able to get is

=AVERAGE(IF(A2=A3, $C$2:$C$160, FALSE))
when the data is sorted by strain no.

But this gives me the average of every value in column C, not just the ones specific to each strain number (ie, average of all values, not just the values specific to strain 1)


Also, if it is possible to alter the formula so that when I add a new batch to a strain no that it includes this in the average, even if the data is no longer sorted

I don't want a million if statements where i have to type in
=average(if(strain no.=1)... 2)... 3)... etc

For a set of of paired values A and B, I calculated the Geometric Mean two different ways in Excel (Office 2003). First using the GeoMean function. Second using the equivalent formula, which is:
(A * B)^.5
(i.e. the square root of A times B).

I was surprised to find that the two methods produced different results 11% of the time.

OK, the discrepancy does not appear until after 10 or 11 decimal places, but I would like to know which method produces the more accurate result. Does anybody know?

BTW, calculating the geometric mean using logs (wherein the geometric mean of A and B is equal to the anti-log of the average of their logs) produces discrepancies about 30% of the time, so I assume that is the least accurate method.

I wish to average the last 10 numbers in a list that contains null values (""). Since the number of nulls varies between calculations, the starting point (row number) will vary depending on how many nulls are in the list. How do I average the last 10 numbers regardless of how many nulls there are ?. Thanks.

Newbie/ first timer - needs help with a series of formulas with golf averages. Scores are entered C4:C23. The corresponding averages are in cells G4:G23. I need to calculate the last four scores only. The problem is if a player does not play; need to calculate a zero entry but still average the last 4 scores. The other problem arises with entries in cell C4, C5, C6, C7 if they are zeros, because they do not have four scores to calculate, but I still need an average of scores entered. I know enough about excel to be dangerous, so no macros please. Be gentle with this old man. Thanks.

Ok, I have a bit of an odd issue I need to solve. I use an Excel spreadsheet to track daily income for the store over a months' period. I have each day set to give me a rough estimate of profitability for that day. I also want to create a cell that tells me how much we've made so far, based on the number of business days so far in the current month. For example:

Day 1, $1500, $800 in expenses (monthly expenses averaged over the month, plus labor costs for the day), so a total of $700 profit.

Day 2, $1700, $749 in expenses (same as before), so a total of $951 profit.

The formula I'm using to calculate that isn't an issue, but I have the same formula for every single day, and each day has it's own cell with the estimated profitability for that particular day. In two other cells, I put the total number of business days in that month (in this case, 26), and in another cell I put the current number of business days we've had this month (in this case, 4), which is used partially to calculate the other cells. I want to use them to create another cell to contain the estimated profitability based solely on the number of business days out of the month we've had.

So E3 through E33 contain the individual days, I want to calculate - since we've only had 4 business days - what E3 through E7 would total. When I change it to 5 business days, i want it to calculate E3 through E8. So if we've had 4 business days, it only calculates the totals from the first 4 cells, if 5 business days, then the first 5 cells, etc, etc.

I can sort of picture it in my head, but I can't seem to find the necessary equation for it. It may require a macro, but I'd rather use a formula if possible. I'm not a big fan of macros.

I have a grade system where I need to obtain an "Average" of grade data within four cells. At time there may onlt be two cells with a value or three. Example: The cell cell cantain a value of 100 points with the totral of 400 point. AS we know the "Average" would be 100 points. But is only two cells cantained 100 point and the other two where empty then the "Average" come back as 50 points. Because I have 4 cells that requires a value input. So my question is how do I create a formula that will give the correct value for the "Average" of data that is placed in the cells. The values are calculated as a total from the grades entered. That total is in F14, F31, F48, F65 - Now if F14 has a value of 100, and F31 has a value of 100 then the "Average" should be 100, but it is not for the is calculating F48, and F65 as 4 values, so the return is 50. The situtation is that I need all the cells for in some cases data will be necessary, but I need the "average" to be calcalated for only the values entered.
Where is the Formula.
=IF(SUM(F14,F31,F48,F65)=0,0,AVERAGE(F14,F31,F48,F65)) - My brain says this is simple but no matter what I do it returns 50.

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