Prevent #DIV/0 In Conditional Average
Feb 16, 2008
I have an average array formula that works fine when it has something to average. However I get the #Div0 error when there is nothing the average. I need to replace the error message in the cell to be blank. Until there is data to be averaged. I have tried to use ISERROR but struggling to get it to work with my multiple criteria.
This is the formula i am trying to amend:
=AVERAGE(IF('By All Students'!$S$3:$S$100>=80, 'By All Students'!$S$3:$S$100<92, IF('By All Students'!$Q$3:$Q$100="11.1/AR",'By All Students'!$O$3:$O$100)))
View 5 Replies
ADVERTISEMENT
May 19, 2008
I'm looking for a function that will display the average of a row of cells, while at the same time not displaying any error messages. It's easy to average cells without blank values, but to combine that with no errors is difficult for me. I saw many ways to do the average, one of which is:
= SUM(A1:E1)/COUNTIF(A1:E1,">0")
That function doesn't work for a row of blank cells (i.e., hidden rows), though. The result is an error message.
I also read about a way to ignore an error in a computation:
=IF(ISERROR(F1),"",F1)
The problem is when I combine those functions I get a blank cell no matter which function I put first, and without regards to cell values or not. The reason I want this to be error-free is that I have to average the "average column" at the bottom of the table, too (i.e., F100).
View 9 Replies
View Related
May 24, 2013
I have a spreadsheet with many rows of data. I want to be able to see the last month or so of data (each day is one row), but I do not want to have to scroll to the bottom of the worksheet to see the recent data, so I want to reverse the data - I want Row 1 to be my column headers, and for every new day I want to insert a new row at Row 2 and add my data. However, when I do that, my conditional formatting gets all messed up. Once the new row is added, the formatting rule changes its range to only those rows which had data in them before I did the insert, each new row gets a separate but identical rule that applies only to itself, and the headers row gets its own rule.
That seems incredibly stupid to me. Is there a way I prevent the formatting rules from changing and just stay the way they are which applies to the entire column?
View 5 Replies
View Related
Jan 27, 2011
I am trying to design a spreadsheet for planning a project schedule. Down the left are the dates, across the top are the resource names and in the content are the activities being carried out.
Each activity I have is given a two letter prefix, e.g. TD = Training Delivery. I then have conditional formatting applied to each cell to change the cell colour based on the two letter prefix. In all there are six conditional formatting rules.
Now, my problem is that I need this sheet to be really fluid. I want to be able to drag and drop blocks of activities to a buffer area at the side and then drag them into another part of the schedule. However, when you drag and drop (effectively cut and paste) the activities around the conditional formatting moves with the cut content, whereas I want it to remain where it was. I guess what I'm after is a way of dragging and dropping values only.
The best I've come up with so far is an onChange macro that reapplies the conditional formatting to the relevant part of my spreadsheet, but I can't believe this is the most elegant solution. Is there perhaps a way of triggering a macro onPaste?
View 7 Replies
View Related
Jan 30, 2009
I have a spreadsheet with days of week and places saved as text values, and I need to get averages for specific items,
for example,
Day of week in A1, place in A2, number in A3
I want to get in A4 say, the average of the numbers for all sundays at cambridge.
Any ideas how I could do this?
View 9 Replies
View Related
Jul 10, 2006
As you can see from my sheet example, there are three columns of numbers side by side. They represent fiscal year 06, fiscal year 05, and fiscal year 04. The numbers are recorded each month July through June. Each month, a new number is added for the current year, 06.
At the bottom there are cells that show the Average per month for each month. I need an offset formula, or one that adjusts when the new number is added for the month for the current year. For the previous year I need an average that looks at the current year and then the previous year would have a range average to match the current year’s average criteria and return the average for the previous year.
For example, if the current year is updated through March, and it returns an average for March 06 I need the second formula to return an average for March 05 in the next column, and March 04 in the third. As numbers are added each month, I need both formulas to change the average to expand to include the numbers
View 4 Replies
View Related
Mar 10, 2009
See attached example for reference - I am trying to calculate an average if it meets 2 conditions, i.e, calulate the average for the Lead Time column if it is a Bag and On Time. Hope you guys can come through as always!
View 4 Replies
View Related
Apr 20, 2009
I have two columns. One has cost (column A) and the other has profit (column B). I'd like to calculate the percentage profit as a function of how much something cost. So, if it cost $100 and the profit was $20 I would like to calculate 20%. For all percentages, I'd like to average them. I understand this is a "weighted average" and was able to get this working with this formula:
View 8 Replies
View Related
Jan 15, 2006
How do I correct to make conditonal formula with number values ?
My main worksheet has at leat 24 columns and as many as 30 rows, all have
numbered values. Rows are defined names.
I want to get average values when at least 2 conditions have certain values.
I tried =SUM(IF(AND(MapKpa:MapKpa=40),(RPM:RPM=1000),grmcyc:grmcyc))
and answer given is really total of all 30,000 row values for grmcyc and not
when mapkpa values = 40 and RPM = 1000.
Also best would be if the conditions allow lets say when Mapkpa is between
40 and 45 and RPMs between 1000 and 1500 to have a window of grmcyc average
I used Sum but really want an average of grmcyc when Mapkpa and RPM meet the
conditions so I can build a results table of what the grmcyc average value
was from 20 to 105 KPA in 5 KPA windows along with smaller RPM ranges.
View 12 Replies
View Related
Mar 16, 2007
I have countries in column A and the values in column B.
I want to see the avareges of values for each country.
I have tried a few samples that I have found in the forum but they do not work for me.
View 9 Replies
View Related
Feb 15, 2010
I am writing this application wants me to calculate running averages and Std Dev. I will copy a row of values into a spreadsheet. The next row will be a new average of each column. So far so good. But the next time I calculate the new average of the column, I do not want to count the row that represents the last average I measured. SO, I'd like to format the rows that are average values (i.e. bold) and then when I calculate the next average, exclude any value whose font is bold. Currently I am using the following line of code to calculate average:
Is there an easy way to limit the values used to calculate average to only those values that are NOT bold?
Sub ColAve ()
Cells(LastRow + 1, ActiveCell.Column).Value = WorksheetFunction.Average(ActiveCell.EntireColumn)
End Sub
View 8 Replies
View Related
Aug 25, 2007
How do I perform average for a range and be able to exclude outliers. For example I am taking 100 measurements, some are not measureable and recorded as 9999.9. How do I average 100 values, but exclude all the 9999.9?
View 7 Replies
View Related
Jan 18, 2014
I am trying to get the conditional average of 2 data columns.
data_1 category data_2 category
2.5 Yes 1.2 No
4.2 No 2.6 No
3.5 Yes 3.9 Yes
4.1 No 2.5 Yes
What I need is the average across the 2 data columns (data_1 and data_2) for category Yes and category No. I know I can use averageif to get the conditional average of 1 column, but that doesn't work with 2.
View 5 Replies
View Related
Jul 19, 2007
I have three columns in my worksheet.
Column 1 reports the name of three people, John , George and Jim, column 2 the year for which they are paid (e.g. 2002, 2003 etc) and column 3 the amount of money they received each year (e.g. John received 18$ in 2005, $15 in 2004 and 11$ in 2003). I need a function to give me the average salary earned by each of these people in the period 2003-2004 e.g. John earned $15 in 2004 and $11 in 2003, so the average value is 13. Is there a function to do it automatically (so taking the conditional average---average for john given that year is 2004 or 2005)?
I want this average to be in column 5 (column 4 will contain the names john, george and jim)
View 9 Replies
View Related
Jan 22, 2009
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.
View 3 Replies
View Related
Feb 5, 2007
i'm very new to excel formulas & statistics in general. i only need to calculate averages but am having trouble naming the range in my formulas
in the example below, i want to use the first column to determine the range of data for the mean calculation.... so, I am seeking the average of just the numbers which correspond w/ A only. Similarly, I will then want an average for numbers corresponding to B, and then to C, etc etc...
View 9 Replies
View Related
Oct 22, 2007
I have a formula to calculate an average value for a reference range:
= SUMIF(DATA!B32:B61,"<># div/0")/MAX(1,COUNTIF(DATA!B32:B61,))
Because these cells are references the blanks are being treated as zeros so I am getting an inaccurate average value.
Is there a way to make it so it will treat blanks as blanks? I need the zeros to be zeros.
View 9 Replies
View Related
Jun 26, 2008
I'm trying to calculate a monthly average from a set of daily values in a separate sheet. I managed to use some nifty code to return the address of the first value in the month, and the address of the last value in the month.
I tried to create an AVERAGE formula to compute the average between these two looked up address and I'm getting various errors, including #DIV/0 which doesn't make sense to me. Here is the summary of the code I'm using, in generic terms.
Formula to lookup first address in month:
=ADDRESS(MATCH(A57,'Sheet2'!$A$1:$A$2000,0),12,1,TRUE,"Sheet2")
Formula to lookup last address in month:
=ADDRESS(MATCH( DATE(YEAR(A57),MONTH(A57)+1,0),'Sheet2'!$A$1:$A$2000,0),12,4,TRUE,"Sheet2"))
Average formula (not working):
=AVERAGE(ADDRESS(MATCH(A57,'Sheet2'!$A$1:$A$2000,0),12,4,TRUE,"Sheet2")):ADDRESS(MATCH(DATE(YEAR(A57),MONTH(A57)+1,0),'Sheet2'!$A$1:$A$2000,0),12,4,TRUE,"Sheet2")))
A57 is the cell in Sheet1 containing the relevant month, for example in this case it is 4/1/2008 (displayed as Apr-2008). Column A in Sheet2 contains the daily dates. Column 12 in Sheet2 contains the relevant data that I want to average.
I've tested the two address lookup formulas and they are working fine. I assume I'm missing some sort of small conversion such that the average formula is not reading the ADDRESS formulas as addresses.
View 7 Replies
View Related
Feb 23, 2008
have the follwing worksheet"
Date Rate Qty
2:06:56 PM 199.5 116
2:06:49 PM 199.5 343
2:06:40 PM 199.5 226
2:06:28 PM 199.5 48
2:06:20 PM 199.4 162
2:06:11 PM 199.5 95
2:06:04 PM 199.4 449
2:05:58 PM 199.6 1488
2:05:40 PM 199.5 9
2:05:40 PM 199.5 9
2:05:37 PM 199.4 161
2:05:28 PM 199.6 101
2:05:23 PM 199.35 4605....................................
I would like to calculate the weighed average rate( rate*qty/ total qty durig a period) for different periods say 2.00 pm to 2.01 pm, 2.01pm to 2.02 pm etc. Is there a way to do this by using database(dsum,daverage) functions. What will be the content of the criterion range? Or is there any other way of solving this.
View 2 Replies
View Related
Mar 27, 2014
The drivers are simply a start date and an end date.
For example: 1/1/2014 - 3/31/2014
To keep it simple I am only looking at 4 fields in the referneced data set. They are Date, Net Calls, Average Handle Time and Agent.
So records look as follows:
DATE AGENT NET CALLS AVERAGE HANDLE TIME
1/1/2014 Agent 1 30 Net Calls 2.00 Minutes
1/1/2014 Agent 2 40 Net Calls 8.00 Minutes
1/2/2014 Agent 2 05 Net Calls 25.00 Minutes
...etc
I am stuck on the Average Handle Time problem as this needs to be weighted on net call volume contribution by agent conditional on dates being equal to or greater than the start date and less than or equal to the end date.
I have attached a workbook for reference : Dashboard Table miacg.xlsx‎
View 1 Replies
View Related
Feb 12, 2009
The data:
There are a number of columns, but we'll use the first 2 first. Column A has the dates from 1/1/2009 to 12/31/2009. Column B will has numbers, mainly 0-100. One cell will have a 'report date' which is just a date.
The formula needed:
I have a few cells labeled wk1, wk2, wk3. Week 1 will start with the 'report' date. wk1 I would like the Average of Column B IF the dates in Column A are within the 'wk'. wk1 = report date TO report date + 6. wk2 = report date + 7 TO report date + 13 and so on.
My goal is to have the formulas all set so that when we move to another report date and the raw data is added, I can just change the 'report date' and everything (the wk numbers) will automatically update with the new raw data.
The furthest I got was this, but it didn't work.
=AVERAGE(IF('A3:A33>=VALUE(D3)
View 9 Replies
View Related
May 4, 2008
how do I perform calculations on the last x non-blank instances in a data range?
for example, let's say I have a spreadsheet of 5 baseball players' batting averages (rows are team game number played, columns are at bats and hits for each player). I want to see how each player has performed in their last 10 games played, but some players have not played in every game. If I just use the sum function for the last 10 cells, I won't get the correct information for any player that has missed one or more of the last 10 games.
View 8 Replies
View Related
May 8, 2008
I collect unique prices each day. I am trying to find a way to determine the average of the numbers collected from the 21st of the previous month to the 20th of the current month. This formula will need to calculate for multiple months and years. So for example, I need Feb2008 average-which would be the average of numbers found between Jan21-Feb20, I then need Mar 08 average which would be data from Feb21-Mar20 etc. My spreadsheet is setup with the first column having the dates (ex. 01/01/08, 01/02/08 etc) and the second column containing the value for that particular date ($2.85, $3.00 etc).
As the number of days between the 21st and 20th change each month, I just can't seem to find a way to do it without a whole lot of manual effort.
View 9 Replies
View Related
Jun 14, 2013
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.
View 14 Replies
View Related
Dec 9, 2008
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?
View 5 Replies
View Related
Jan 23, 2007
TotHCInv.Value = WorksheetFunction. Sum(KRInv, PBLInv, CRInv, PVInv)
If i >= 34 Then CPSCtphRMA.Value = WorksheetFunction.Average("G" & (i - 30) & ":G" & i)
The first line runs properly, but the second line bugs out with the error message "Unable to get the Average property of the WorksheetFunction class". I can simply do the math, but I thought that using the worksheet function would be easier than summing and dividing. I'm curious, though, as to why I can't seem to use the Average function.
View 3 Replies
View Related
May 23, 2014
Can I get a minimum average and a maximum average, I have a worksheet with days of supply for 100 stores with about 100-200 products each, the dos resides in column D.
I was going to create a summary page and reference column d.
The following just gives me the min, I want the min average if possible:
View 5 Replies
View Related
Sep 1, 2006
I am attempting to calculate Grade point averages for my students for all classes. There are 5 columns of grades to be considered. I frist assign values of 0-5 to the grades then average the points. My problem is that I want the AVERAGE to ignore the zero but it calculates it as well. (I enter a 0 if I have no grade for that class.) I have tried the{ } to make it an array formula but this also did not work. Here is what I have, can anyone help?
=SUM(AVERAGE(IF(AND(L2>89,L2<100),5,IF(AND(L2<90,L2>79),4,IF(AND(L2>69,L2<80),3,IF(AND(L2<70,L2>59), 2,IF(AND(L2>0,L2<60),1,0))))),IF(AND(T2>89,T2<100),5,IF(AND(T2<90,T2>79),4,IF(AND(T2>69,T2<80),3,IF( AND(T2<70,T2>59),2,IF(AND(T2>0,T2<60),1,0))))),IF(AND(AB2>89,AB2<100),5,IF(AND(AB2<90,AB2>79),4,IF(A ND(AB2>69,AB2<80),3,IF(AND(AB2<70,AB2>59),2,IF(AND(AB2>0,AB2<60),1,0))))),IF(AND(AJ2>89,AJ2<100),5,I F(AND(AJ2<90,AJ2>79),4,IF(AND(AJ2>69,AJ2<80),3,IF(AND(AJ2<70,AJ2>59),2,IF(AND(AJ2>0,AJ2<60),1,0))))) ,IF(AND(AR2>89,AR2<100),5,IF(AND(AR2<90,AR2>79),4,IF(AND(AR2>69,AR2<80),3,IF(AND(AR2<70,AR2>59),2,IF (AND(AR2>0,AR2<60),1,0)))))),-1)
I know it is huge. The syntax is correct. It calculates the average, but always for all 5 columns. It will not ignore a 0 in a column.
View 10 Replies
View Related
Jan 7, 2008
I'm using the formula =AVERAGE(B16:L16) to give me the average.
However I have a couple of problems with this. Firstly I would like to exclude the value zero from the average. Secondly to also ignore the lowest and highest values.
Example, if the values in the cells are 0,1,2,3,4,5,6,7,8,9,10 then the current result shows 5, by ignoring the 0 and lowest value 1 and highest value 10 the average should be 4.5.
View 9 Replies
View Related
Oct 11, 2006
Ok, I for some reason just cant wrap my head around this. I need to to get the average time per call of two rows, but they are based on how many calls taken.
so in one column i have 50168 calls taken at 4:21 seconds per call. and in the next row i have 597 calls taken at 5:20 per call. I need to see what the new average will be with them combined, and I need it to display in m:ss form.
View 9 Replies
View Related
