Free Microsoft Excel 2013 Quick Reference

- Easy Weighted Average For Thousands Of Items
- Percentage Not Calculating Correctly
- There's got to be a formula for this...
- Is there an "averageif" statement in Excel?
- Look up a price in an array and average if needed
- Convert number to mm:ss.00
- Median problem
- How to return only the last 10 numerical values in a list?
- Trying to Average a subset data range
- Shouldn't the formula work?
- Calculate averages within groups
- 30 entry average formula needed.
- Average age
- Average without previous enteries
- AverageIF dilemma
- Geomean calculation discrepancy
- Average list of numbers with null values
- Golf Average help
- Calculating cells used based on another cells data
- Average Formula with empty cells

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.

=((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

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

___AGE___COUNT

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!

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:

=averageif(.......

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?

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.

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.

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:

25468786

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?

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.

MonthTypeHoursSeptA3SeptA4SeptA5SeptBSeptB4OctA3OctB4OctAOctB5OctA5OctB4NovB3NovBNovB3NovBNovA3NovA4DecBDecB4DecA5

Thanks - John

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

Code:

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.

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

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.

=DATEDIF(C3,TODAY(),"y")&""

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.

thoughts?

thanks in advance.

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.

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)

Help!

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

(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.

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.

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.