AVERAGE Of The Previous Meaningful Cells
Feb 26, 2006
B10 = AVERAGE(A1:A10),
B11 = AVERAGE(A2:A11), etc.
In case there are empty cells in the A-column, the result is not correct
anymore because AVERAGE does not take en empty cell into account.
For instance if A1:A10 are 1, A11:A20 are empty and A21:A30 are 2, the above
formula in B21 would give AVERAGE(A12:A21), or 2.
What I want is the average of the previous 10 meaningful cells so - in this
case - the average of A2:A10 and A21, or (9*1+2)/10 = 1.1
Question : Is there a possibility to calculate the average of the previous
10 meaningful cells (0 is meaningful, blank is not)
View 12 Replies
ADVERTISEMENT
Oct 14, 2009
I'm trying to make a formula that will calculate the average of the previous 12months. The goal is to tie the formula to a reference cell that contains a date. Each time the date is changed by a user the calculation will be updated accordingly. Here is the CSE formula that I thought would work:
{=AVERAGE(IF($B$5:$B$53=B$2,OFFSET($C$5,(COUNT(C5:$C$53)-1),0,12,1)))}
I've also attached a sample file to illustrate the problem. The 'range' portion ($C$5,(COUNT(C5:$C$53)-1)[/b]of the Offset function was setup simply to get the 12 months which preceeded the reference date.
View 4 Replies
View Related
Jan 21, 2012
I have data in "column F" that I want to highlight. The data is Listed verticaly from Row 1 to 424 in Column F; I want the spread sheet to hightlight the number when it exceeds the previous days 30,60, and 90 day average volume. How I can make this occur?
View 2 Replies
View Related
Jan 27, 2009
I have a bunch of information in word that I am converting to excel. I have attached the document called test for you to see.
How do I get all the information between the word "focus" to migrate into rows of data so that I can get one company on one row?
For example line one should be like finished product attachment.
View 8 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
Jun 27, 2008
formula using if's and vlookup's to populate a calendar with x's for day a person is gone. however the last couple of people that are gone are not being marked on the calendar even though i use the exact same formula (except for cell numbers and such) for previous, working entries. Here is my formula
View 9 Replies
View Related
Dec 10, 2012
see attached file. Need to find latest non blank value - in attached file it is highlighted in yellow. From there, want to summarise 26 weeks back so, in the attached file:
Row 2 would be finding 750 and summarised back 26 weeks from 30 sep 2012
Row 3 would be finding 2250 and summarised back 26 weeks from 2 dec 2012
Row 4 would be finding 5000 and summarised back 26 weeks from 4 nov 2012
View 7 Replies
View Related
Feb 19, 2009
getting a formula to do this
I have
......D E
5 )......1
6 )......2
7 )......3
8 )......4
9 )......5
10)......6
11)......7
etc down to 31
They only show up when the cell next to it is not empty.
=IF(ISBLANK(D5),"0",(1))
=IF(ISBLANK(D6),"0",(2))
etc
If nothing is put into say D5 D6 or D7 but something is put in D8 then i would like E8 to become 1 as it is the first to be filled.Then when D9 has something in it, it becomes 2 if D10 has nothing in it it gets left blank but when d11 has something in it e11 becomes 4 counting the blank cell in between.
How can this be done.
View 9 Replies
View Related
Dec 2, 2011
I am trying to create an advanced spreadsheet in excel that caters for people who really need to be told exactly what to do and what not to do. So based on the selection in column A I want to disable and change the background colour of some cells as follows (but only disable the cells on the same row):
columnA = Number then disable cells D, F, G and change to red
columnA = Link then disable cells E, F, G and change to red
columnA = Image then disable cells D, E and change to red
View 1 Replies
View Related
Feb 21, 2012
I enter data in Cells starting at C3:O13 I enter 4 digit numbers 1131, 1212, 1122, 1215, 1201, 1207, 1133 etc. etc.
Here is my rule: I cannot have more than 8 numbers in the 1200 series in ROW 3 So I need conditional formatting to come up with a yellow background if I have 8 numbers in the 1200 series.
Example:
C3, C4, C5 etc.etc. 1131, 1212, 1122, 1215, 1201, 1207, 1214, 1233, 1122, 1230, 1216, 1122
1216 would highlight because it was the 8th cell in that row that contains a number in the 1200 series.
View 4 Replies
View Related
Apr 9, 2012
see the example below...
ABCDE1blueblue2greengreen3redred4purplepurple
I basically want column E to check the cells from left to right and display the value that is in the first populated cell. For example, cell A2 is blank, therefore, it should display the value from B2. A3 & B3 are blank, so E3 should display the value from C3 and so on...
Is this an "IF" statement?
View 2 Replies
View Related
Mar 18, 2008
I have a row with values in successive cells. Some cells are blank. I want to conditionally format the cell text colour based on the value compared to the previous populated cell (i.e. compare A3 to A2. If A2 is blank, compare it to A1).
View 9 Replies
View Related
Mar 22, 2012
I have a column of data (Column T) which has about 10 different words in it (varieties of fruit). What I want to achieve is based on the data in column T I want to fill Column U with information, let me put it in an example.
Column T has the following words in 2000 rows. Thompson, Crimson, Sable, Superior, Midnight, Ralli.
If Cell T4 for example has 'Thompson' or 'Superior' I want cell U4 to say 'white', but if cell T4 has 'Crimson' or 'Ralli' I want it to say 'Red'. If it has Midnight I want it to say 'black'.
I think this can be done using a vlookup maybe? I don't have to go down 2000 rows to fill this information in.
View 4 Replies
View Related
Oct 12, 2012
Margo
12/12/86
188478007
7/19/12
801
Anthem BC/BS
Margo
12/12/86
188478007
7/31/12
806
Anthem BC/BS
[Code] .......
I have this info on my excel spreadsheet plus 500 more lines. What I am looking for is if a name repeats then the info in column 2, 3, 5 and 6 for that name needs to be brought to new row. Example would be if I used the name karen on the next row then I would like her info from the desired cells to auto populate so I don't have to keep entering them and so on with new and old names. I have been trying to get this done for months know and have had no luck. My sheet already has over 500 lines of names and it would be nice if I could use the data from the past as well as the future to populate these cells.
View 5 Replies
View Related
Dec 4, 2012
I'm trying to copy certain cells to a new worksheet that keeps a track of previous entries via date and name and location.
The code works somewhat, however the values are not pasted into the respective worksheet. I'll try to explain the code as I see it.
Sub Button2_Click()
So the first section Activates the sheet that data is entered
Sheets("Checks").Select
Sheets("Checks").Activate
ActiveSheet.Unprotect ("fizix")
If the value ST is found in the sheet, the code knows to paste values to another worksheet known as ST_Hist
[Code] ........
Here I have another macro that then deletes the data in the selected ranges denoted by "" or ":", this part of the code works, however I dont have my pasted values in the appropriate sheet!!
Sub ClearAll()
Sheets("Checks").Select
Sheets("Checks").Activate
ActiveSheet.Unprotect ("fizix")
Sheets("Checks").Range("C2:C4").ClearContents
[Code] ........
View 1 Replies
View Related
Jun 1, 2014
Sheet 2 contains the following:
Column AH2:AH367 represents the current year and contains values from Jan 1 to Dec 31 inclusive, some of which are zero and future values are blank.
Column AG2:AG367 represents last year and contains the same and all cells have a value (some are zero).
The date column is A2:A367 (the columns in between are all the years prior to 2013).
I need a formula that counts the total number of cells with a value greater than zero for the range AG2:AG367 up to and including today's date to be returned in cell A1 of Sheet 1.
View 9 Replies
View Related
Dec 8, 2013
I have subtracted two dates from B2 & B1 using DATEDIF() Function and the results are available in (B5) Year, (C5) Month and (D5) Days respectively. Now the problem is how to Subtract, Add days & Months using borrow, Carry forward to the previous cells (I need formula, function for the above). I have to take 30 days from month ie in C5 if the days (D5
View 1 Replies
View Related
Jul 17, 2014
What I want to do is Average out a range of cells "F4:F343". That's the easy part =average(F4:F343). However I want to exclude any numbers for that average if data in coloumn "Q" = "FMC"
Would that look anything like:
=average(F4:F343)/countif(Q4:Q343, "<>FMC")
I'm also displaying it as minutes in a day so i added *24*60 to the end of the formula and formatted to Number/.00. I've entered the formula and it will calculate but when i enter FMC to the appropriate area the average wont change.
View 2 Replies
View Related
Aug 31, 2012
I have a macro which autofilters a range of cells.
I can count the number of visible rows by using the following code
Code:
lcountActive = Range("BC34:BC" & x).SpecialCells(xlCellTypeVisible).Count
What I would like to do is both calculate the Average value of the visible cells and the Sum as well. I have tried to adapt the code I have but my knowledge is just not good enough to do this.
View 1 Replies
View Related
Oct 25, 2006
I have a spreadsheet which I use to track when a work request is recieved, when we confirm the request and when we action the request. I have been trying to write some code to count the amount of requests, receipts and actions we have processed in the last month.
My first column shows who the request is from
The second shows date recieved
The third shows date we send receipt
The fourth shows the date actioned.
View 9 Replies
View Related
Sep 22, 2009
I have 1300 vertical cells.
Is there a quick way to get the average of every ten cells? i.e. to end up with 130 averages of successive groups of ten cells (cell 1-10, 11-20, 21-30 etc.)?
View 2 Replies
View Related
Apr 25, 2014
I am having a dilemma on getting the average for the first 3 non blank cells. This is how my data looks like :
excel average.jpg
I don't know any formula that will disregard the non blank cell and will get the average of the firs 3 non blank cells from the right.
View 2 Replies
View Related
Feb 9, 2009
I have imported a text file using a macro. And I want to calculate the average of two columns, and put this into a new one. But how to do this and let the macro stop if one of the two input values is blank?
ActiveCell.FormulaR1C1 = "=(RC[-3]+RC[-2])/2)" but then when I use the autofill i get a lot of zero's, but I can't delete them using a macro.
View 14 Replies
View Related
Jan 15, 2010
Columns L, M and N are always fully populated.
Columns D, E and F may have some missing values, along with H, I and J.
Lets just concentrate on D.
I need to create a formula where I get the anser in C1 This is an average of L only where D has a value.
View 3 Replies
View Related
Jul 19, 2012
I want to be able to find the last non empty row in column C and then average the last three inputs. Thus utilzing this as a means for a projection of the remaining periods left in the year. All for projecting figures for the remainder of the year.
Bill
Period
Date
Amount
1
1/6/2012
3982.76
[Code] ..........
View 2 Replies
View Related
Jan 13, 2004
I have a list of weeks 1-4 for the fiscal month of January. I have a total column. Each week the appropriate week is updated and the total is updated via formula. The total column is just formulas adding Weeks 1-4 up. I also have an average column with the AVERAGE formula beside the total it that should give me the weekly average for January. However, it's trying to average all the weeks instead of just the weeks that I am on.
For example, Week 1 is 1,000,000. Week 2 is 500,000. Week 3 & 4 are 0 because there is no data in there yet. The AVERAGE formula keeps showing 375,000 instead of 750,000. It's averaging all the weeks and I just want it to average Weeks 1 & 2 right now, but automatically average Weeks 3 & 4 when they are populated.
View 9 Replies
View Related
Aug 8, 2007
I want to take an average of only non-zero numbers in a range of cells containing a few zeros. I know there is a function to do this, but I haven't been able to find it. With the normal average() function, the denominator is too large as cells with 0 are included.
View 9 Replies
View Related
Sep 10, 2008
I am trying to work out a formula for calculating average of non adjacent cells but it should ignore any cells with 0:00:00 value or are empty.
At the moment I am using the simple formula =average(A1,A2,A5,A9). But if any of these cells is empty or 0:00:00, it then gives me a wrong average. I could have used a array formula with if function but it then calculates all other cells in between as well that I don't want to e.g. I may not want to include include A3 in my calculation but as it has some value in it, it gets in the calculation and gives the wrong answer again.
View 9 Replies
View Related
Feb 12, 2009
I need to caculate an average of three cells. (A1 C1 D1) but if a cell contains $0 then it will exclude that cell from the calculation.
The fact that the cell's are continous seems to be the reason why I can't get around this
View 9 Replies
View Related
Jan 30, 2007
I've got the following function working, until I paste it into a cell that is referencing a range with all zeros.
Function Ave_VisibleUnits(Cells_To_Ave As Object, MyStep As Integer)
Dim i As Integer
Dim vCount As Long
Dim vTotal As Double
Calculate
Application.Volatile
vTotal = 0
vCount = 0
For i = 1 To Cells_To_Ave.Cells.Count Step MyStep
If Not Cells_To_Ave(i).Rows.Hidden Then
If Not Cells_To_Ave(i).Columns.Hidden Then
vCount = vCount + 1
If Not Cells_To_Ave(i).Value = 0 Then
vTotal = vTotal + Cells_To_Ave(i).Value '(It dies here)
End If
End If
End If
Next
vTotal = vTotal / vCount
Ave_VisibleUnits = vTotal
Calculate
End Function
View 2 Replies
View Related
