I have looked on here and I can find the minimum value in my range but what I am trying to do is find the minumum two values. Or rather, i want to find the average of the remaining values.
If the lowest value happens THREE OR MORE times, i still only want to exclude two of them. Same thing goes if the second lowest happens more than once, i only want to exclude at most two values from the avg.
currently, i have 12 values in my list.
example:
list --> one in each cell going across
25, 50, 87, 56, 99, 80, 81, 82, 50, 100, 98, 99
i have the answer as 83.2 doing it by hand. the 25 and one of the 50s would be dropped and the rest would be averaged.
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)
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:
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.
Is there a good way of excluding an outlier in an average calculation. In the example below will I exclude 1000 from the average-calculation.
The way to decide excluding-values can either be a percent based on the range or everything that is a higher than a user defined value. It can also be more than one outlier. A user defined function is OK with me, if it is impossible to use the built-in functions.
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 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?
excluding zeros and for excluding hidden rows, but can't seem to find a way to do both at the same time, which is what I need to do.
My company has projects that come and go (and are hidden when they are gone) and at times, those projects return 0s because we do not work on them for a short period... so, when I average a column, I need to exclude both situations.
I use a SQL query to export a subset of data from our server. After manipulating the received data I need to Average one column that has been sorted into a number of blocks consisting of variable numbers of rows and post the result to the ajacent cell. I have tried the Countif worksheet function but am unable to resove the syntax problem.
Sub SumBlock() Dim First_Row As Long Dim Last_Row As Long Dim iTotalRows As Long Dim iCount As Long iTotalRows = Range("A65536").End(xlUp).Row First_Row = 2 Do While Last_Row < iTotalRows - 1 Last_Row = ActiveSheet.Range("j" & First_Row).End(xlDown).Row iCount = Application.WorksheetFunction.CountIf(Range("J" & First_Row), ("J " & Last_Row), ">0") ActiveSheet.Range("K" & Last_Row + 1).Formula = "=Sum(J" & First_Row & ":J" & Last_Row & ")/iCount" First_Row = Last_Row + 2 Loop End Sub
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.
I am trying to automate a process involving a Grubb's outlier test and calculating averages/stdev of a set of numbers. I have the data in one column and have calculated the average/stdev/%CV of that data. I have also added a formula to calculate Max/Min outliers of that column of data. Then there are cells with IF statements that display either "yes" (for an outlier present), or "no" (if no outlier is present). What I would like to do, if it is feasible, would be to set up formulas to recalculate the average/stdev of the column, taking into consideration either the presence or absence of outliers.
Here is what I invision(cells are for example purposes)
=IF((M9 = yes, Avg(H2:H40)-max(H2:H40)),M9 = no, avg(H2:H40))
So it would calculate the average without the outlier if the outlier "yes" was present, or just calculate the average if "no" is present.
I would also do this with a minimum outlier as well, but i can set that up if this first one is possible.
I am using Excel to use stock ticker symbols such as FDX "Fedex", to return past volume and price daily performance found in Google Finance that will be manipulated with the current VBA I am working on that will result in a trading pattern. Each trading pattern will happen at a different range in the same column. I will be inputting hundreds of ticker symbols daily so using VBA and not having to enter formulas manually in each is greatly preferred as this will save me lots of time.
I am stuck trying to find the average value of a range starting from the minimum to the maximum value. For instance, Range("H10:H20") may have a maximum value at range("H12") and a minimum at Range("H18"). I would like the average of Range("H12:H18").
Sub Fnd()
Dim MaxStartPriceRange As Range Dim MinStartPriceRange As Range
Dim MaxPriLocation As Double
Set MaxStartPriceRange = Range("h11:h21") MaxPriLocation = Application.Max(MaxStartPriceRange)
Set MaxStartPriceRange = MaxStartPriceRange.Find(MaxPriLocation)
How do I adjust this formula so it copies & paste special values rather than copying formatting etc? I am very new to this and I have looked at other examples but have found it hard to adjust my code using those examples.
VB: Sub PrepayjournalKW() ' ' PrepayjournalKW Macro ' Range("A6", Range("A" & Rows.Count).End(xlUp)).Copy Destination:=Sheets("Journal").Range("A1") Range("B6", Range("B" & Rows.Count).End(xlUp)).Copy Destination:=Sheets("Journal").Range("C1") Range("AB6", Range("AB" & Rows.Count).End(xlUp)).Copy Destination:=Sheets("Journal").Range("D1") Range("AF6", Range("AB" & Rows.Count).End(xlUp)).Copy Destination:=Sheets("Journal").Range("E1") End Sub
I've used the sumproduct formula very sucessfully in a workbook. The workbook is used to monitor discrepancies routed to other departments. Column U has the status of the discrepancy (Open, Closed, Cancelled etc). The below formula returns the number of discrepancies raised to a particular department. Now I need to tweak the formula to exclude values "Cancelled" found in range $U$119:$U:417.
Question is...in the below formula the red are pointing back to name ranges in a different tab on my spreadsheet. On that spreadsheet I also have a name range called DTCCSUBSUPPREF
I would like to add within my formula, a string which will point to that name range and exclude values if they fit my criteria. Basically I need to see values only where comp or tri dont show up. comp or tri could be the only value in the cell, or could be in the beginning of a string, at end, or in between. Below examples. I have tried (--(DTCCSUBSUPPREF<>"*comp*")*(--(DTCCSUBSUPPREF<>"*tri*") , and I would have thought the * characters would have served as a catch all.
Below is the entire forumla just in case.
11111111FT04comp comp tri 11comp 11tri tri11111111FRP06
I need a formula that calculates the values of 5 seperate cells located in different places throughout my sheet. The value will either be numerical or display a text value (normally "RD"). If it displays text I do not want that value counted. So it literally just totals the number values.
i have found this code on ozgrid to search between a min max value, trouble is it only selects the first cell it finds. How can it be modifide to copy each value in between the min max set say to sheet 2?
Option Explicit Private Sub CommandButton1_Click() Dim strNum As String Dim lMin As Long, lMax As Long Dim rFound As Range, rLookin As Range Dim lFound As Long, rStart As Range Dim rCcells As Range, rFcells As Range Dim lCellCount As Long, lcount As Long Dim bNoFind As Boolean strNum = InputBox("Please enter the lowest value, then a comma, " _ & "followed by the highest value" & vbNewLine & _ vbNewLine & "E.g. 1,10", "GET BETWEEN") If strNum = vbNullString Then Exit Sub On Error Resume Next lMin = Left(strNum, InStr(1, strNum, ",")) If Not IsNumeric(lMin) Or lMin = 0 Then...................
Look in Column A and find the cell that contains "Std A 1" and the cell that contains "Std A 2".
Compare the corresponding values in Column B, and multiply the lowest value by .2 I've tried using various methods (find, if cell.value.instr) but I keep getting errors. Can anyone point me in the right direction?
Attached is a print screen. I'm struggling with using the min function in vba. I want it to find the minimum cumulative cost in week 0 out of the first three, and the copy the permutations of it (1,0 or 1, 1 , e.t.c.) to Week one column C & D of the model.
the macro will track daily min/max values, and keep a count of the times these values occurred in a separate sheet in the workbook. At first I thought simply setting up a macro to look at a predefined range of rows to find the min/max values, but some days may be missing data; therefore, I need a macro with the logic that looks for the min/max value appropriate to each specific day (which is the tricky part). Below is the detailed explanation of the process.
1)The "Main" sheet, Column A, contains days 01/05/1999 – 01/29/1999 (days are already in numerical value format). Begin in A1 and this will be the first daily value. Find the Max Value in Column D corresponding to the numerical daily value of A1. 2)Once Max Value found in Column D, Column B of same row will have the time the Max Value occurred. 3)Count time Max Value occurred in “Time Count” sheet Column C of corresponding time. 4)Move down to next daily value. Continue this process to last row in sheet.
For example, “Main” Sheet, day 01/05/1999 (numerical daily value 36165), Max Value is 1.1835 found in Cell D12. B12 has the time at 2:45. A “1” is then placed next to 2:45 or C14 of the “TimeCount” sheet. If the next day’s Max Value occurred at the same time, then a 2 would be placed in C14...........................
I am trying to do a spreadsheet for my boss and one piece of it has me stumped.
Based on same "die name" I need to return the minumum quantity available.
So, in column D i have a multitude of die names. Some of them match for those that match, I need to return in another cell the smallest quanity available in our die bank (column p).
how I get the maximum and minimum values of the axis from and excel chart? What I mean is I want to specify a chart, and output in the same excel worksheet the values for the maximum and minimum of the two axis.
I have tried various searches, but cannot come up with the answer, although I have a sneaky suspicion it in here somewhere.
I have a table of dates and values and would very much like to know a formula for returning the min and mean values for each column within a specified time (date) period.
Table is as follows
Date value(a) Value (b) .... 1/1/09 10 8 2/1/09 8 6 3/1/09 5 2
I'm currently using the following formula to return values rounded down to the nearest 2.5: =FLOOR(X8*1.25,2.5)
This works fine but I would like to put in place a system where if the "X8" value is 1 the formula produces a 2.5, and if the "X8" value is 10 or greater the formula produces a 10.
how to link axis properties with a scroll bar (two scroll bars).
The idea is to be able to modify the maximum and minimum values for the axis in case there are outliers which might cause problems by analyzing the chart.
The Guideline in the scroll bar should be set to its maximum (all the way up) by Default, meaning that if there chart behaves itself well there is no need to change its axis values.
The same applies for the minimum guideline of the scrollbar but it should be sent all the way down by Default.
