I have a scenario where I have a rolling list of sales figures which get added to each week that passes.
I need a formula that will calculate the last 5 weeks of sales and generate an average - which I think I have an idea how to do.
The sticking point is that so as not to skew the averages, when there has been an exceptionally busy or quiet day for a reason we know about I exclude the sales from that week.
This then interferes with the averages as it either takes it as a zero and lowers it or seems to stop formulas from working.
So to summarise:
Average of last 5 weeks sales Excluding any blanks Dynamic enough to always pick up the last 5 values in the list (i.e. the last 5 weeks)
I am writing a formula for finding a maximum out of three average values. The cells that will be populated with data are A1 to A5; B1 to B5; C1 to C5. The final cell where I want to write the formula needs to show the maximum of the three averages (average of data in each row).
The difficulty is that sometimes only the A and B rows are populated (and sometime only A row is populated) and if B and/or C are blank, the formula fails and shows "#DIV/0!".
How can I write so that if C is blank, then evaluate only A and B data. And If both B and C are blank, then just display the average of row A.
I have an array formula that calculates the average of numbers between two dates:
{=AVERAGE(IF('Date Range'!$B:$B>$H$4,IF('Date Range'!$B:$B<=$B$3,'Numbers to Sum'!$C:$C)))*100}
The start date is the day after the date in H4 and the end date is in cell B3. The dates are in column B and the numbers to sum associated with these dates are in column C.
Some of the cells in column C are blank and my formula is taking these blanks into account in calculating the average, while I would prefer not to count them in the calculation.
Can someone please suggest how I could amend the formula above to accommodate this?
How can i exclude Blank or cells that have a 0 when plotting my graph? Right now everything is very small because i have about 50 cells, some with quantities and some with zero quantities.
I can't modify my formula to leave blank cells blank when dragging it down, Also, I've got two formulas that i need to combine. Please view the comments I've put in cells E4, F2,F3,H2 and I2 to understand clearly what am seeking. See the attached worksheet.
I am looking for an Excel macro that will Autofilter a worksheet to display all rows with a empty value in one column (e.g. column B) as well as display the entire row above the aforementioned row with a blank cell in column B. I need it to be "reversible" so that I can click the Autofilter button (or advanced filter button) to show all rows again.
I have data that is formatted in an Excel 2010 table. The two columns in question are [Invoice #] and [Description]. In the description column I have descriptions of products as well as freight. The same invoice number would be tied to the product description as well as its associated freight. I need to filter out certain product types and their associated freight items.
The macro I wrote creates an array of invoice numbers that I want to filter out and leave the remaining invoices, but I can't get the filtering part to work. Here is my code:
Dim Invoices() As Variant 'array of invoice numbers Dim Descriptions() As Variant 'array of Descriptions Dim InvoiceFilter() As Variant 'array of invoice numbers to filter Dim i As Integer 'counter Dim j As Integer j = 1
[Code]...
What happens now is that it filters out all values in the Invoice column instead of only the values in the InvoiceFilter array.
I am trying to create a schedule that includes weekdays only. Is there a formula or set-up step to do that?
Currently I have 3 columns: Start date Action date Finish date
The Action date column has the formula: that cell (say C4) minus number of days that action requires (say 10), but when 10 days fall on a weekend, I need to change to the next work day instead.
I can't figure out how to make this formula work because I need to use cell AG46 and it contains an IF formula that leaves the cell blank if the sum = 0. I keep getting the #VALUE! error. How do you create a formula using a cell that has an IF formula that could leave the cell blank?
Basically this is what I need in cell I12: =sum(a12)-(ag46*8)
I have the column E in excel labeled 'Notes' in tab 1 adding at the bottom of the column using(=COUNTA(E2:E29), so if the cell is empty it doesnt add it up in the totals. In tab two i have that same column linked via formula. In tab 2 its counting the empty cells because even though they appear blank they have a formulas in them how do i get it to not count those cells. here is the formula im using in column E tab 2.
i am wondering if there is a way that i can have these cells that depend on formulas to remain blank, until i have my data entered, or that they may remain blank, but give me running totals of only the cells i have inputted.
I have several lists that I need to be able to print out periodically.
The main worksheet that underpins each list is over 900 lines long and numerous calcs and formulae populate columns to provide the data for my 'print lists'.
I need my 'print lists' on seperate worksheets and I have linked the data from my main worksheet results, using IF statements to stop unwanted results being displayed by making the rows appear empty (i.e. to display "").
Due to the design of my main Worksheet it is inevitable that I have many rows in each 'print list' that are not required for that particular 'print list'.
I want to remove the 'empty' rows from the print sheets at the time of printing. I cannot use the GoTo > Special > Blanks > Delete Rows because the rows are not recognised as being 'blank'.
I have attached 2 screenshots of my 'print lists'.
P.S. I am not assuming anything but I have searched on OZGRID and it appears to me that I might need to use Excel/VBA?
Attached is a spreadsheet I am working on that has a series of graphs with defined dynamic ranges. I am having 2 separate issues on 2 different tabs.
1) Ranges named AirRecBra4 (and Bra10); AirComBra4 (and Bra10); AirRemBra4 (and Bra10) are not pulling in data from the correct data points. Instead of using the last 26 data point, they are currently beginning at the top of the column (currently cells 11 to 36 is their respective columns). The formulas are copy and pasted from working offset ranges in the same tab but are reading differently.
2) On the last 6 tabs beginning with Stn Backlog, I want to have defined formulas beginning in cell B119 and continuing downward to at least F200. However, when I add the formulas, the offset reads the formulas as being data and adjusts for that. I do not know how to adjust the dynamic range to ignore formulas in blank cells.
Global Demand-Capacity Management_working (version 3).xlsm
I have a spreadsheet with data in every other column (i.e. A, C, E, etc.). I am using the "filter" option in Excel on the aforementioned range of cells although the little filter icon shows up on all columsn, including the blank columns in between my data. Is there a way to only have the filters show up on the columns with data?
I am using the standard validation from the tool bar. What I am trying to do is in check the date in a cell and if the new date is Greater and or equal to another date in another cell. The problem is I am having is when the cell that I am checking for a date is Blank the validation does not work and one can put in a date. I tried unchecking the "Ingore Blank"
I have one basic spreadsheet with all the data and then I filled a second spreadsheet with weighted averages based on the data in spreadsheet 1. However, then when I switch my filter on spreadsheet 1 all of the numbers change in spreadsheet 2.
I want to be able to Autofilter a specific criteria and copy over to another sheet without the header, then in the first column it'll change the information to a date, then copy back over to a third sheet. I have accomplished all of that.
My problem lies with that if there is no contents when the Autofilter is applied, it will still continue to do the steps and then I have Run-time error '1004' AutoFill method of Range class failed. Because there is no contents it tries to do this.
I have created a file where I use the Subtotal function. Once I collapse the information to only give me the Total, I would like to copy the Total rows into another worksheet. However, when I do this I get blank lines in between. I am trying to find a way to delete the blank rows in between the Total rows I need. Is there a way to do this with the auto filter function?
I need to Autofilter across many sheets. I have found some examples on doing this such as..
Autofilter Across Multiple Sheets
But I need to take it a step further. Autofilter several Worksheets (skip blank ones - the above link gives code that bombs when it sees a Worksheet with out data.) Then copy those results to a 'summary' page.
So...
1. Autofilter all Worksheets / skip blank ones
2. Show results of all Worksheets on summary page.
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.
I have several sheets with about 250,000 rows per sheet.
But, even I sort by Column A, there are STILL hundreds or rows that are total blank interspersed down the page . . . I can't autofilter for blanks because there is too much data . .
Is there a way to do a Formula (SUM) but exclude cells with invalid values? I would like to add up all values in attached spreadsheet for cells A14 to A28 and again for A49 to A63, as well as get an average C-14 to 28 and again for C49-63.
As some of the cells contain text rather than numbers, the formula doesn't work. how to exclude these cells?
The problem is, it averages all cell values (conditional upon the C column's cell being equal to A2 of course), whereas I only want to average the VISIBLE cells that match the condition.
I have tried using a little VBA with:
Function Vis(Rin As Range) As Range 'Returns the subset of Rin that is visible Dim Cell As Range Application.Volatile Set Vis = Nothing For Each Cell In Rin If Not (Cell.EntireRow.Hidden Or Cell.EntireColumn.Hidden) Then If Vis Is Nothing Then Set Vis = Cell Else Set Vis = Union(Vis, Cell) End If End If Next Cell End Function
and then trying the formula: =AVERAGE(IF(Vis('Data Table'!$C$10:$C$65536)=A2,Vis('Data Table'!$BP$10:$BP$65536))) but it does not work.
I am looking to adapt a piece of code (originally created by Ger Plante) so that it autofilters multiple columns of a table. I have three data validated lists that need to search 3 different columns in the table and filter accordingly, but also show all if no hits are made (hence why Ger Plante's code) was perfect in most respects. I would ideally like to keep the code as a Worksheet_Change event, but can deal with it being run as a normal Macro via a button if this is necessary.
[URL]....
VB: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("B1")) Is Nothing Then Range("A5:C5").AutoFilter Field:=1, Criteria1:=IIf(Trim(Range("B1").Text) = "", "<>", "=") & Range("B1").Text End If End Sub
