i was given a formula in B15 which allows me to get the average of the figures that are submitted within that box not all 28 squares will be used hence the formula but this leaves me a #DIV/0! if no numbers are inserted.i need to get the average of all 7 boxes for totals but cant why i have this error there how can i oberride this so if all the boxes are blank it reads nothing
View 13 Replies
I am looking for a sum if formula to average the numbers in column 2 but only if the number isn't 0, so the average of CARLING will be 12.5 and not 8.3
View 9 Replies View RelatedI would like to find the average number of days events take from start to finish.
My data is in 3 columns:
EventName StartDate EndDate
I have a ton of EventNames, each of which have a StartDate. But I want to calculate the average days to complete only for those EventNames that have EndDates.
So, if a cell in the column EndDate is greater than zero, then take that cell, subtract it from the corresponding cell in the StartDate column; Add all of them together and divide by the count of those rows in the EndDate column that have a value.
How do I put this in excel terms? ....
In a grade book, if students get a grade on the final exam that is higher than the midterm grade, this becomes the exam portion of their (if it's lower, it's averaged). So a student who gets an 81 on the midterm and a 90 on the final gets a 90 for the exam portion of the final grade. What's the easiest formula to make the final exam grade the average if it's higher than the midterm grade?
Right now the formula I use for the course grade is this:
=SUM(I3*$I$1)+(J3*$J$1)+(K3*$K$1)
where I3/J3/K3 are the midterm/final/quiz averages, and I1/J1/K1 represents the respective % (30/40/30) of the grade assigned to the midterm/final/quizzes.
I want to count the number of cells that have a value that is above the average of all of them. Ive tried the COUNTIF formula but no luck.
Sample attached : sample weds.xlsx
I have a worksheet containing 5 columns. A start and end date, a number of days between these dates, the quarter number of the start date (i.e. 1 to 4) and then the year.
I want to calculate the average number of days per quarter so an average of days in column C, based on the details in column D and E.
In the sample book attached I need to:
* Calculate if the numbers on J2:J4 are within 30% of each other (which I do and the results are in the table in O1:Q4.
* The numbers in J2:J4 (and accordingly the results from O1:Q4) change when the numbers in F2:F4 change
* In M2 I need to calculate the average of only the numbers from J2:J4 that fall within 30% range from each other. In the example attached, I need to average only J2 & J3.
I thought about 2 ways:
A multiple IF formula or an Averageifs formula.
The multiple ifs formula below works fine except when all 3 numbers are either within or out of30% range.....
I need a formula to determine the average number of days between sales transactions for each item in inventory. There are several hundred different item numbers with thousands of transactions.
I have attached an example of what I'm looking for. I've toyed around with Average and If functions but can't seem to get it right.
Avg Number of Days - Excel Help.xlsx
I'm once again here asking for a smart trick to do a difficult (for me) thing. I need to write a formula capable of averaging the n cells on its left. The problem is that the number of cells to be averaged is a variable contained in anothe cell therefore the exact number is not known (a priori) and changes at every sheet refresh.
View 3 Replies View RelatedWe are trying to track the average star values of reviews posted to our clients accounts. Here is an example of the spreadsheet we have set up:
Company Name
Review 1
Review 2
Review 3
Review 4
Review 5
Company 1
[CTY] 4 Stars review content
[ISP] 5 stars review content
[GGL] 4 stars review content
[CTY] 3 stars review content
[YELP]5stars review content
In this example, we use the initial [ ] to track which site the review was posted on ex: [CTY] means city search, [GGL] means google, etc.
We want to be able to track the average star value on each site. So the formula for City Search would scroll across the row of reviews, pick out reviews 1 and 4 and then find the first number in each and average them. The result in this case would be 3.5
From the research we have done so far, this looks like it will require a complex array formula. How to make this formula? Is this even possible what we are trying to do?
I am trying to find a formula that works out the average of the figures in column K.....my problem is that so far in column K there is only one positive number (11)- the rest of the cells are DIV/0 so the average is showing as DIV/0. This is incorrect as the average is really 11 - how can I sort this out with a formula?
View 9 Replies View RelatedI am trying to average a range which continually changes depending on the amount of registries entered in a given week. for example, one week there may be 5 registries which would fill five columns while another week there may be 15 registries, once again filling 15 columns. I would like to find the average of x amount of columns.
My initial approach was to select the first blank cell to the right of the data and then find the last filled cell (which should be the first column of data.
with this range i tried hopelessly to use the average function (did not work):
Sub averagemake()
Dim iLastColumn As Integer
Dim Rng As Range
Sheets("All Data(Values)").Select
Range("A6").Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
iLastColumn = Cells(6, Columns.Count).End(xlToLeft).Column
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-iLastColumn]:RC[-1])"
End Sub
I'm working on building a workbook to track sales progress, and I'd like to perform an analysis on some of the data with regards to sales efficiency.
To simplify:
Column A = Initial Contact Date (mm/dd/yyyy)
Column B = Close Date (mm/dd/yyyy)
As an example, I would like to calculate the average number of days between the Initial Contact Date and the Close Date to calculate the average number of days in the prospecting cycle. Basically, the formula should function as AVERAGE((B1-A1)+(B2-A2)+(B3-A3)...) and so on for the entire column.
I know one solution would be to insert a new column that performs the subtraction between the two dates, and then I could AVERAGE this new column. However, I want to perform similar calculations to analyze the time period between other key milestones in the prospecting cycle - rather than have a lot of extra columns in my sheet, I was wondering if there's some type of array formula that will calculate what I need.
I am working on an excel master marksheet which will be distributed to many teachers to fill in their students marks. How can I enter an average function that can work out the mean for those different classes. For example I'd like the main average function to be like this =average(d5:d50) but if a teacher has thirty students in his list the mean will be only limited to the marks of those thirty students. Same thing I want for the mode function and the median function.
View 7 Replies View RelatedI am looking for a way to calculate the average time, over a number of times.
So basically, would have different times in 24 hour hh:mm:ss, all in column i.
I have tried =AVERAGE(i2:i18) and it doesn't work.
I have also tries Ctrl/Shift enter as an array, it didn't work.
I tries to format cells as time and number, it didn't work!
I want a formula to calculate the average number of staff working per week in the attached data.
View 9 Replies View RelatedI am trying to subtract the highest number from a row of numbers then find the average for the remaining numbers in Excel 2003, as: =SUM(B2:E2)-MAX(B2:E2)AVERAGE(B2:E2).
View 5 Replies View RelatedI need to find the cells that are greater than 48 and then find the average of this number.
Is there a formula that could put this together?
Average if greater than 48.xlsx
I'm trying to create a gradesheet in which any score that falls into the
failing range will automatically highlight in red...
I have a team of 8 people who take calls. I need to get the median (and average) value of the total number of calls for these people for a specified date range, say 1/1/2013 to 1/24/2013.
When i use the following f(x):
{=MEDIAN(IF((Call_Data!$A$2:$A$13000>=$E$8)*(Call_Data!$A$2:$A$13000
I have a formula in Cell A1. It tells me the cell location of the last number in column E. So, for example, I input the number 44 in cell e85, the value for cell A1 becomes $E$85.
Let's say I now put the value 33 in cell e86, now the value for A1 becomes $E$86.
I want to calculate the average of the values in column E, all the way from E1 to whatever is in A1.
Here's what I need: =AVERAGE(E1:<whatever is in A1, like $E$85>)
How do I write the AVERAGE formula to make this work?
I want to create a long list of 10,000 random numbers between -100 and +150, but do it so that the average of all the random numbers equals 20.
View 9 Replies View RelatedI need help with a formula to calculate average number of rebills per client.
I don't know how to get excel to add the number of unique client in a given row. Example
Column A
Client 1
Client 1
Client 1
Client 2
Client 2
Client 2
Client 3
Client 4
Client 4
Client 4
Client 4
Client 5
Formula needs to calculate number of Unique clients. In this case, the answer is 5, but how can I get excel to calculate for me?
sumif problem but it wont work with a countif or average if.
Column A has various names and Column B has amounts, what I need is to count the number of occurances "John Smith" has an amount in Column B. The previous formula I tried was
=sumif(A:A,"John Smith",B:B) but with either countif or averageif it errors too many arguements.
I wasn't sure if Dcount or an array would be suitable but have not used them before.
Pivot tables I'm sure will be the future with this but haven't got to the foot of that mountain yet.
I have a worksheet that has all weekday dates in column 1 and values in column 2. I want to create a 30-day moving average based on the last (non-zero) value in the column 2.
Since every month has a different amount of days, I want it to search the date that has the last value (since I don't get a chance to update it daily) and go back thirsty days from that date and give an average of all the column 2 values skipping and values that are null or zero.
I have a column of numbers that are derived with a formula. I need to Average only the ones that either have a Positive or Negative number, ignoring blanks or zero.
I have tried Search but couldn't find anything that address both blank and zero.
Sheet2
K610.00%624.76%632.53%6418.75%65666.38%6768-4.00%6970-5.84%710.86%
Excel tables to the web : [URL] .......
With this small sample, the answer should be 3.35% according to Excel when I choose just those neg and pos cells.
I am trying to do the following.
Monday=0
Tuesday=5
Wednesday=10
Thursday=0
Friday=5
Saturday=0
Sunday=0
Averages
Monday=0
Tuesday=5
Wednesday=7.5
Thursday=0 (I have got a value return of 7.5)
Friday=5
Saturday=0 (I have got a value return of 5)
Sunday=0 (I have got a value return of 5)
I need to work out averages for cells higher than zeros, in other words, I need to ignore those.
Also say I have got an average of 5 by Tuesday and no number yet for the rest of the week, I still get an average of 5 for all days left which I do not want.
I am using excel 2003 and formula =SUM(RANGE)/COUNTIF(RANGE,">=0").
I would also like to hide the annoying #DIV/0! error.
Excel 2010 Higher than past average formula?
Im using Excel 2010 and want to make a formula that will High light the number when it exceeds the past 30 days average. My information is listed vertically in row F..
E11 through E24 contains numbers and a few errors (#N/A) that need to persist (the errors need to show).
E10 needs to show the average of the numbers that are in E11 through E24, and just ignore the errors.
I have many columns like that - where the errors need to show and I need to show an average of the number/values that do appear, ignoring the errors.
i have two columns...a and b (a w/ names, and b w/grades). then i have the table lookup with names and grades all mixed up for many rows. i want to be able to average the grades with appropriate names.
=average(vlookup(name, table, column, false))?? i don't get it to work and how can i specify the grade to average?
