Fill-down For The AVERAGE Function
Apr 25, 2009
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).
View 2 Replies
ADVERTISEMENT
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 1, 2014
I am working on a pivot table and am having issues with our subtotal: Sum / Averages.
The pivot is setup as so:
excel help.jpg
The problem is I need the average column to average the viewable area, and not from the data, so for example G28 should be 55 and not 6. Also, I need the Average of the fill rate to be a value.
View 2 Replies
View Related
Feb 18, 2010
TPR, DISPLAY and FEATURE columns generate a rating based off of an IF function. In the Executed column, I need TPR, FEATURE, DISPLAY to be averaged together...BUT....In I want the average only include columns where there are numbers. For example in row one the eqn would be (1+3+2)/3, but in row 2 the eqn would be (1+1)/2...can I state an average function within an if function? Or what would be the best way to create an eqn for this?? I have thousands of rows to complete and doing it manually is not an option.
0- Did not meet expectations
1- Below expectations
2- Met expectations
3- Exceeded expectationsTPRDISPLAYFEATUREExecuted?Effective?Comments132Coming off of a Dec promotion113111111221
View 9 Replies
View Related
Jan 10, 2009
In the worksheet just say:Cell A1: Jan-11-2009 as today date. Cell A2: What is a date function I need to put in there so when a computer clock change to 12:01am it automatically fill in a next day it should be Jan-12-2009 and continues do so for the next day at A3 ....when computer clock change again. Remember I wanted a cells A2...A3...A2000 are blank until the computer clock change to 12:01am.
View 5 Replies
View Related
Jan 26, 2007
I have a function =TimeSinceIns(3) in cell K3. Where the 3 represents the row number. I would like to fill the function all the way down to cell k100. And be able to do this reguarly. Unfortunately when I use the fill handle it leaves 3 as 3 when I want it to be changing to the appropriate row no. Ie in cell k4 would be =TimeSinceIns(4) and so on.
View 3 Replies
View Related
Aug 11, 2009
I need to use the ADDRESS function to define a range from which an average can be determined. My current function is as follows:
View 3 Replies
View Related
Dec 11, 2009
Custom average function. can this be done with Worksheet functions:
View 4 Replies
View Related
Aug 20, 2008
I have a data range - C4:C54 and I want an AVERAGE calculated in C56, but I only want a value displayed in C56 when a data entry is put in. Up to 50 data points could be put in anywhere e.g. only 14 values in C16:C30, or 44 values in C6:C50, or the full 50 in C4:C54.
The formula I've been trying is
=IF(ISERROR(C4:C54<>""),AVERAGE(C41:C54),"")
However, when no values are in the range it displays DIV/0! whereas I'd like the cell blank.
View 4 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
Dec 27, 2012
Weight
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
[Code]...
Monday Input Weight: 23
Monday Input number: 5
I'd like to autofill the above table from the "Input" numbers and weights I would enter elsewhere.
Previously, I would do that like this:
=IF(Y$10=0; ""; IF(Y14
View 6 Replies
View Related
Nov 27, 2013
Each sheet contains the test data for an individual class and I am trying to create a "Totals" sheet where I can show the average that each class had on a particular test and the letter grade breakdown. I am trying to create one 'average' formula that I may then copy across that will change the sheet reference automatically without me having to manually re-create the formula every time I add a new class sheet. I tried using the INDIRECT function but could not get it to work. Keep getting #Ref error.
View 3 Replies
View Related
Jul 15, 2008
I will attempt to make this as easy as possible in explaining something I am looking to do. I am looking to Calculate the average lifespan of an individual based upon
1) They are "dead" (A status,either "Alive" or "Dead" found in Data!F2:F653)
2) They were born between 1/1/2007 and 12/31/2007 (Delivery Date provided in Data!C2:C653)
The lifespans were provided on Data!E2:E653
View 12 Replies
View Related
Jun 13, 2009
Is there a way to include an if then else function to an average formula that will skip cells with zero?
I went into better detail in the attached example.
View 9 Replies
View Related
Jun 8, 2007
I have data in Column A as follows:
A1 15
A2
A3 20
A4 56
A5 45
A6
A7 71
A8
A9 23
where cells A2, A6 and A8 are empty.
I want to be able to AVERAGE or SUM the first four nonblank cells. I know I could manually select the cells, but I have a spreadsheet with 30 columns and 40 rows, and the data (including empty cells) in each column is different.
Is there a single formula that will find the first four nonblank cells and then perform the AVERAGE or SUM function?
View 9 Replies
View Related
Jul 29, 2006
I'm trying to find the best Macro or Formula to get this done.
Sheet 1 has a list of salesperson in column "A" and the total numbers they made on Monday (in the same row, column "E"), Tuesday (In the same row, column "F"), Wednesday (In the same row, column "G"), Thurday (In the same row, column "H"), and Friday (In the same row, column "I"). I also have a sheet for each individual salesperson. I need to find a Macro or a Formula that I can use to give me the average of the numbers if "A5" = the salesperson in Sheet 3 then avarage the numbers from "E5" to "I5" and I want the results to show up in cell "D35".
View 9 Replies
View Related
Aug 22, 2006
I wonder if it is possible to formulate a formula in which average values are recalculated after every added record.
For example:
Record 1 shows an amount of 200 so the average value after 1 record is (200/1) 200. Record 2 shows an amount of 250 so the average value after 2 records is ((200+250)/2) 225. Record 3 shows an amount of 210 so the average value after 3 records is ((200+250+210)/3) 220.
I don't want to calculate all averages manually and most probably there must be a formula that is able to recalculate the average value after another record is added to the file.
View 6 Replies
View Related
Nov 19, 2006
I need some assistance in using an array formula which averages based on multiple arguments. The relevent range names in my input data are:
Col A - Manager Col B - Report Type Col C - Days taken to Complete
and I have used Validation Lists to include several different Managers and 5 Report Types which I'll call A - E, with Days taken to Complete an input. Each Manager may have 0 to numerous instances of each report during each period.
I'm trying to build a table which includes each manager and the average number of days taken to complete each report. The complication is that, while Report A & B are separate reports, for this purpose I need to get an average for A & B together.
For C, D & E, I have used the following:
{=AVERAGE(IF((Type="C")*(Manager="Smith"),Days_taken_to_Complete))}
For A&B, I have tried:
{=AVERAGE(IF((Type="A")*(Type="B")*(Manager="Smith"),Days_taken_to_Complete))}
but this doesn't work. Could someone please advise me the correct formula if either of two states in a named range are to be used to average a second named range?
View 9 Replies
View Related
May 10, 2008
I am working with averages of ACT scores and Class rank percentages. I cannot figure out how to exclude all the zeros and/or DNR (do not rank) values that are in those columns. For example if I have 10 items, and 2 are DNR, the I want to exclude the 2 DNR's in my average. I tried putting it in the Row or Column area and unchecking what I wanted to exclude and then drag it into the data area, but that didn't work either.
View 2 Replies
View Related
Jun 18, 2008
If I have numeric values in a cell examples c5 = 126, and d5 = 135. How do I place these values in c5 &d5 in another cell as a cell reference within the formula ex: =Average(f126:f135)
View 3 Replies
View Related
Jan 16, 2014
My formula in sheet "A" in cell D7 is this
=IF(Activity_Log!C2="BH",Activity_Log!F2,"").
What I would like to do with it is fill in row D7 to AH7 with this formula without changing the row reference in the IF function, only the column reference.
On the Activity_Log worksheet, my data is listed from cell C2:C32, which is selected from a drop down list, and F2:F32, which contains the formula =(E2-D2)*24.
I've attached a sample : Sample_MonthlyReport.xlsx
View 9 Replies
View Related
Mar 29, 2007
the function I'm referring to is when you have a value in a cell and you hover your mouse cursor over the edge and get the black cross with arrows on all corners. Once you drag the cell into another location, it copies the the data into the other cell.
I need a way to stop this from happening, because this causes reference errors on other sheets when users do this by accident. However, I still need to allow the user to be able to type in that cell, use the autofill option, and the Ctrl+C option. Is there a way to program a macro that can be put in the sheets to do this for specified cells or ranges?
View 5 Replies
View Related
May 22, 2014
I have a spreadsheet that contains developer related data. I want to be able to do a weekly analysis of average construction time and costs. The problem I am running into is properties enter rehab phase and exit rehab phase throughout the week.
For example:
I want to know the average $ spent and total rehab time week 1 of april. I may have 4 homes that entered rehab that week and 3 that move out of rehab that week. Moreover, I need to ensure those homes that entered and are still in rehab for that week are counted. How do I accomplish this feat?
View 2 Replies
View Related
Feb 5, 2014
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 Related
Feb 14, 2014
This time I have a situation like this:
Column A - Dates
Column B - Equipment Type
Column C - Load Start Time
Column D - Load End Time
I need to do some statistical analysis (average, mode, medium, st dev) on the load times, which is load start time - load end time. As far as I know, I can't subtract ranges (column d i column c) within Averageifs function. I assume I would need to use an array function for this.
View 11 Replies
View Related
May 19, 2009
Actually with the help of array formula I"m taking out Average, Min, Max value of ranges and I was omitting zero but the problem is occurring that if i have originally zero in my ranges then Average & Minimum function is not working properly.
View 8 Replies
View Related
Sep 15, 2009
I'm trying to use the average if function to average a range of numbers, only if a different range of numbers numbers equal a number in two different cells.
ex. average A1:F1 if G1:L1 equals M1 or M2
View 3 Replies
View Related
Apr 16, 2014
Excel function or macros for calculating the average of waterSD column where the TT column is less than or equal to 100(red markings). functions like IF TT IS LESS THAN OR EQUAL TO 100, THEN CALCULATE AVERAGE OF WATERSD....
I had done manually on the right side in the attached excel sheet. only those yellow markings.
Because already I have some macro which do this process but it is not accounting for the TT column less than 100. it starts from 200 TT values. but i need to include 100 TT also. That's is where now we have yellow markings. I will provide you the macro code if you can edit that where it will start calculating form 100TT value, it will be really great. The code is,
[Code] .....
Attached File : average for watersd OF 100TT.xlsx
View 3 Replies
View Related
Jan 15, 2006
how to make the average,median,max, and/or min functions ignore cells in the referenced range that contain the #value! error? All four of the functions are returning #value! because one or more in the referenced range have the error.
View 9 Replies
View Related
Jul 10, 2008
I've got a spreadsheet that I do every month with columns of numbers that I average. This sheet has to match about 10 others similar. The columns are divided by Weekdays, Saturdays, Sundays. But some months there are no entries for certain cells on Saturday or Sunday.
I thought that if I just used the Average function, it would dismiss and not count the blank cells. Alas, apparently not. I've highlighted in yellow the one column that I'm really having trouble with.
View 7 Replies
View Related