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 RepliesI 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?
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 RelatedI'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.
I am trying out VBA to write a macro in order to average every 25 cells in a column. My attempt so far is
Dim myrow1 As Integer
Dim myrow2 As Integer
myrow1 = 2
myrow2 = 25
Range("G2").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R " & myrow1 & " C3:R " & myrow2 & " C3)"
myrow1 = myrow1 + 24
myrow2 = myrow2 + 24
So I am hoping the first ActiveCell.FormulaR1C1 gets read as =Average(C2:C25)
However I just get Run-time error '1004' Method 'FormulaR1C1' of object 'Range' failed.
I'm working on a workbook that will track staffing patterns.
The workbook has three worksheets: Sheet1 "RCS", Sheet2 "HCT' and Sheet3 "Hidden". I've attached the workbook to this thread. The password for the form is "j".
On Sheet3 "Hidden" I have two tables that are set up to collect the SUM of columns on Sheets1 "RCS" and Sheet2 "HCT". I'm finding the SUM of each range by way of the background color. I've set up the following formulas and when the "data collection tables" are in the same worksheets as the original information, the formula's work perfectly:
The following functions are pulling data from Sheet1 "RCS" and placing them into a table in Sheet2 "Hidden"
[Code] ........
The following functions are pulling data from Sheet1 "RCS" and placing them into a table in Sheet2 "Hidden"
[Code] .......
I have two more functions that aren't working due to the fact that the source values are percentages and NOT plain numbers. The above functions work great for SUM but not for percentages. EXAMPLE--Let's say, 3 sub percentages it gives me the SUM of the 3 percentages (i.e. 85% + 100% + 100% = 285% instead of giving me 95%.
[Code] ........
How might I use the following functions to find the average of the source fields instead of the SUM?
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.
Is it possible to replace the Pivit table name/reference in a GETPIVOT function with a cell/range reference? I can on other components of the function call, but not that. For example ... the "hardwired" function call might be:
=GETPIVOTDATA(" Sum of 1991",'[EE_financial data.xls]Sheet1'!$A$3,"Company","ERG","Item"," Depreciation & Amortisation")
I can achieve the same result by externalising the 1991 and ERG, so i replace this with other dates or names, thus:
ERG
1991=GETPIVOTDATA("Sum of "&C13,'[EE_financial data.xls]Sheet1'!$A$3,"Company",D12,"Item"," Depreciation & Amortisation")
.. and this works fine.
However, if I try and replace the '[EE_financial data.xls]Sheet1'!$A$3 with a reference to a cell containing that string, it returns #REF!
I have a spreadsheet which shows a matrix in pounds. But I would like to have a reference cell somewhere near the top which will automatically show the conversion to kilos of any selected cell. Is anyone aware of a function which will do this?
For example, one pound equals 0.45359237 kilos.
Cell A1 contains the reference function to the active of selected cell. If I click on B1 which contains 12, cell A1 would automatically show 5.45. If I click on cell C1 which contains 20, cell A1 would automatically show 9.09.
So I’m thinking it would be something like
=SelectedCell/0.45359237 or maybe =ActiveCell/0.45359237
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'm trying to create a dynamic COUNTIF function that references a previous cell.
For example, if I have 10 columns:
A B C D E F G H I J
a 1 b 2 c 3 a 1 b 3
I am trying to write a function that will let me reference A1:J1 and then if the cell contains an A, do a countif among the cell+1 for cells that have a value of "1". In this example, that function would return a count of 2. I think that I need some kind of combination of IF, INDIRECT, and COUNTIF, but I'm not sure how to string them all together--or maybe there's another way altogether.
Any way to use a find and replace function involving a cell reference. I have a spreadsheet with ton of junky text burying information that I want. Here is a simplified example
A B
Book 345353hg dgdgsdgfd fff Book belongs to Jim
Sneaker fdg4 Sneaker is worn by Jan
Gum dfg s d e te4345Gum tastes great
Cake jklsjflsjfjikCake smells delicious
The pattern is that the A1 text appears in the B2 text. What I want is
Find *A1
Replace with blank
Find *A2
Replace with blank
and etc.
So that in the end, I get this:
B2
belongs to Jim
is worn by Jan
tastes great
smells delicious
I've looked at the functions of find, replace, substitute, left, and right and I can't seem to find the right one to do what I need.
I'm working in Excel 2010. I'm trying to create a dynamic range, using the OFFSET function. I've got it working beautifully but as soon as I save it and close the Name Manager the range stops working. When I go back into Name Manager I find all the cell references have changed (from C1:C600 to C1048572:C595 - or some other strange range).
P.S. What it's doing isn't so important as when the cell references are right it works. But just for full info: It's an OFFSET function, starting at A1. It will look at a list of companies and use a Match function to find out how many rows to offset (based on where the first instance of a particular company. And it uses the COUNTIF to make the range the same number of rows as there are entries for that company.
Here is my broken function:
=OFFSET('Map Point'!A1048572,MATCH("Company Name",'Sheet Name'!C1048572:C595)-COUNTIF('Sheet Name'!C1048572:C595,"Company Name"),0,COUNTIF('Sheet Name'!C1048572:C595,"Company Name"))
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
I have a number of statements within the Sheet Event Code (Excel 2007). Three times lately I have added a column and had to go back into the code and find all of the references that needed changing to reflect the new column.
I have been working on this for a couple of days and even tried EE, but to no success.
I have read that Defined Names / Constants should be used as often as possible, but even trying that, the VBA code errors out or "hangs up". Even within Bill Jalen's book (VBA and Macros 2007), there is nothing that addresses this, especially using Intersect.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
On Error GoTo mEnd
Set rng = Sheets("Log").[F14:F10000]
If Not Intersect(rng, Target) Is Nothing Then
If Target = "" Then
With Sheets("Log")
I set up formulas to count text characters in a range of cells. I'm tracking attendance and payments for a small yoga studio.
All I need to do is count "Y"s for prepaid attendance and "DI"s for drop-ins. I have the formulas working but they are absolute so inserting a row will break my sheet.
=COUNTIF(E14:Z14,"*Y*")
=COUNTIF(E11:Z11,"*DI*")
It is suppose to be that if the employee is "FT" and has worked >=4 years the return is 15. But if the employee is FT and has worked 2 years but less than 4 years then it is suppose to return 10 (these are days off) Or if the employee is FT and has worked 1 year, but less than 2 then it should return 5 days off. And all the others in the column get no days off.
I have tried to do it with structured references and with cell references I get a column of zeros!
I want to sum A2 to B10. The catch is: I have the letter "A" in cell A1, the number "2" in cell B1, the letter "B" in cell C1, and the number "10" in cell D1. In effect, I want to make a function =SUM("A1B1":"C1D1") such that it gives me =SUM(A2:B10). Hopefully, the answer will work for any function (e.g. SUMIF, COUNT, VLOOKUP, etc).
View 4 Replies View RelatedI 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).
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 RelatedCustom average function. can this be done with Worksheet functions:
View 4 Replies View RelatedI 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.
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 RelatedI am using the dsum formula to sum some values...the formula in B2 is:
=DSUM(BaseSistemasFebrero,"vlfinf",OFFSET('Planes Entidades'!B$1,0,0,COUNTA('Planes Entidades'!B$1:B$49),1))
The Planes Entidades sheet the data is layed out like this: ....
I am running a research experiment in triplicate. Upon generation of data for all three, I create an averaged value of the three. I then take this value and do more calculations with it on a separate sheet. I wanted to know if there was a way in which I could quickly generate formulas based on a changing reference cell.
Example Data:
V1A 28
V2A 26
v3A 30
AvA 28
V1B 20
V2B 22
V3B 18
AvB 20
Vxy represents the value of y sample, x triplicate. V1A being sample A, first triplicate. AvX is the average of the three values.
I want to be able to generate a formula that references AvA which can be applied to the cell immediately below to reference AvB. A simple example would be to square the average.
I'm currently working on a summary page for the defects in the building we are working on.
I've done up a summary page which reference's cells in other sheets which contain a TEXT(COUNT formula. When I goto SUM these referenced cells in my summary page I keep getting it returning '0'.
Example of what I'm doing:-
Defects Column in Summary Page
411 --> referenced from =Basements!L1 --> L1 contains =TEXT(COUNT($C$8:$C$100),"0") formula
56 --> referenced from =Level1!L1 --> L1 contains =TEXT(COUNT($C$8:$C$100),"0") formula
33 --> referenced from =Level2!L1 --> L1 contains =TEXT(COUNT($C$8:$C$100),"0") formula
When I go to do a simple SUM of 411,56,33 via =SUM(D3:D5) it returns a zero?
I have a range of cells (B6:M6) and I want to count how many have data. My problem is that all of the cells reference cells on another worksheet. Every cell starts out like =Bethlehem!B6. I want to ignore cells that have a reference in it when I count. I know how to do it if you want to ignore these cells as follows: =countif(b6:m6,"<> 0"). Those cell references return a 0 if they have no data in them. However, a user might actually enter a 0 in the referenced cell and I need to count that one. The above formula would exclude it.
View 4 Replies View RelatedI have Sheet1 which contains a cell named "Limits" which refers to =Sheet1!$B$12.
Now I wish to have Sheet2 which should contain a cell named "Limits" which should refer to =Sheet2!$B$12.
How do I get the reference Limits:=Sheet1!$B$12 (highlighted in red part) to change dynamically according to the sheet I chose?
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 RelatedI 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