I have inherited some legacy templates (Standard, Leave and Exception) which cannot be changed. I need to summarise them (Total) selecting the earliest start and the latest finish. (Sample attached). The templates are 90 columns wide and about fifty lines deep so named ranges isn't practicle (I think). I'm running 2003.
View 4 RepliesI have the following formula which works perfectly unless one of the cells has a value of zero;
=MIN(SUM(AD13),MIN(SUM(AK13)))
When AD13 = £60.00 and AK13 = £94.00 (or vice versa) the formula returns £60.00, which is correct. BUT when AK13 = £0.00 obviously the formula returns £0.00, but I would like the formula to still return £60.00.
How this can be done?
I am having trouble with large data sets of rents by market: trying to find the peak rent in a time-series, and then look for the next trough rent in time. I tried using the Newsletter 3 techniques which feels like the right direction, but I am not getting the nesting right or some of the parameters in the functions.
So, within each time-series I need to find the MAX value (peak rent), wherever that is in the array. Then I need to look to the right of that exact MAX value and find the exact MIN value (trough rent), whereever that is in the array (again, to the right of the MAX value).
A small sample of what the data set looks like:
2008 Q22008 Q32008 Q42009 Q12009 Q22009 Q3
Atlanta771773768761749756
Austin770786784779776776
Baltimore947948949950950953
Boston1,6471,6601,6541,6481,6301,613
Chicago998998996985984986
I have a range of values, (1,10,20,0,2,5)
I need to find the minimum values, but I want to avoid 0(zero)
How do I find the minimum and maximum numbers in a table if I do not know the size of the table but can assume the user will choose the first cell (upper left cell) of the table and run the macro?
View 5 Replies View RelatedDaily At the end of day We receive sales files from our different teams, these files are received at different times due to difference in working shifts. I have collated last one month data and from this data I want to know the earliest and maximum time and date on which we received the files in last month, so that i can fix the receiving time for all regions.
View 5 Replies View RelatedI have pivot table that is pulling data from a page that is using the vlookup formula. I would like the table to only include fields that have data in the count. However, the pivot table is registering cells that have "0" (i.e., there's no actual data in the cell it is pull from) as having data. How can I get the pivot table count to ignore these cells?
View 1 Replies View RelatedI have a list of numbers that I want to average. Problem is, some of the numbers contain zeros and i want to exclude these entries.
So, if I had a list of 6 values and 3 were zero, I'd want to sum the list and divide it by 3 (not 6).
How can I copy a range and paste it excluding the lines with a zero value. The sample attached explains it better.
View 6 Replies View Relatedim trying to average over a number of work sheets to determine the avergage talk time of employees over a day, ive tried:
View 14 Replies View RelatedI current have an Excel doc that I am trying to pull averages from. Every row has data that needs to be averaged but I only need every other cell. And I cannot have zeros in the average because it will skew the data. Also if the average for a row ends up being 0, I want that average to show up as 0 not #DIV/0!. From Column H to EE I need to average each row individually.
I came up with the below formula.
=IF(SUM(H1:H1:J1:J1:L1:L1)=0, "0", AVERAGEIF(H1:H1:J1:J1:L1:L1, ">0")
It works but Its not practical because I have to manually enter all of the cells that it pulls from.
How to get the small value excluding zeros?
small(Convention!$I$3:$I$1000,rows(Dashboard!$D$41:$D41))
I have a one column of numbers...let's say consisting of six rows
the following numbers 12,0,14,25,0,9
if I average this it comes out to 10
how can I average the same rows but exclude the zeros in the outcome(is there a formula to do that?)
I would like the answer to be 15.....I still need the zero's in the rows however
I have a column with values, say column a. In column a are several rows with values equal to zero. I would now like to create a new list that omits the values that are zero, e.g.:
original list:
23
0
54
76
0
0
new list:
23
54
76
data in 3 colums (A2:c100)
Would like to sum the maximun values in each row together.
is Max(A2:C2) + Max(A3:C3) + Max(A4:C4) ........
I assume whatever works with max I can change to min.
I have a row of data with the cells I want to find a MAX for are separated from each other:
a1=300 d1=500 g1=800 j1=10,000
I want to find the MAX value for a1,d1,g1,j1, but exclude any value over 1000. So instead of =MAX(a1,d1,g1,j1) returning 10,000, =???(???) will return the next highest max of 800.
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.
View 3 Replies View RelatedI have 3 columns 1st one has numbers that reference a wellbore.
2nd have a depth
3rd has a number value (a mud weight) volume
Well API example Depth Weight
42050102600000 10000 10.2
42050102600000 9500 9.2
42050102600000 6500 8.5
42050105000000 12000 11.0
42050105000000 7500 6.0
42050105000000 10200 9.5
42050110100000 8000 8.0
42050110100000 9500 9.2
I want the formula to give me based on the well API number the max depth and the weight associated to that max depth. And the minimum depth and the weight associated to that minimum depth. I'm not sure how to say it in a formula. I don't want to use VBA.
Im trying to filter a name range of 12 columns in vba. However im able to emit ZEROS but NOT blanks when i usse AutoFilter Field:=12, Criteria1:="0" it stil contains some blanks in the filtered data
View 1 Replies View RelatedI have used max and min function to display minimum and maximum time value; however, it is not displaying the correct time value from the list. I think there is a better formula to achieve this. From the sample data displayed below, minimum time value should be 11:30PM and maximum time value should be 6:30AM
See sample data below:
4/1/2013 11:45 PM
4/1/2013 11:30 PM
4/2/2013 6:30 AM
4/2/2013 6:15 AM
4/2/2013 6:00 AM
4/2/2013 5:45 AM
[Code]....
Is there a simple way to find the min and max value for my formula below
HTML =SUMPRODUCT((Data!$J$2:$J$10000="E700")*(Data!$M$2:$M$10000>'Rental Rates'!$A3)*(Data!$M$2:$M$10000<'Rental Rates'!$B3)*(Data!$M$2:$M$10000))
I need a macro that will search MIN and MAX columns and identify which horse has the maximum number in both the MIN and MAX columns an example is highlighted yellow in the attached spreadsheet. If there is a selection i want the macro to keep the horse that matches the criteria along with the race date and time e.t.c (header) but delete all other horses in that race. If there are no selections that meet the criteria in a race i want it to delete all details for that race including headers and go to the next race.
View 2 Replies View RelatedI have the two buttons on userform "viewer" that try to push the listbox past it min and max range. resulting in a error. Could it be that if listbox is at min it set the button to hidden.
View 4 Replies View RelatedSay I have 2 columns that in basic form look like this:
Column A Column B
Jan 1
Feb 0
Mar 7
Apr 4
May 15
Jun 2
Jul 5
Aug 4
First I want to look up the max value in this column. This is easy =max(b1:b8)
Then I want to know the minimum value that occurs after the maximum value. Thus the answer would be 2.
here is an attachment... i have found array fromulas for finding Maximum, Minimum & Median. Now i need to find the no of Maximum, Minimum & Median.
View 2 Replies View RelatedI have the following formula C= day in DD/MM/YYYY and D = Time in HH:MM format and BJ5 is the date
View 3 Replies View RelatedPlease see attached file which is data for horse races (this is a small example of the data i will be working with). I need to find the largest values from the MIN and MAX column (shaded Grey) range for each race. I want to be able to add a function that will allow me to quickly identify which horse has the largest number in both the MIN & MAX columns example on spreadsheet is highlighted RED. It would be handy if it would inform me if the criteria has been met by highlighting it or by placing some text value in an adjacent cell on my spreadsheet i have used the example "Y".
It would also be beneficial if that race be deleted in its entirity if there are no horses which meet the criteria above or similary if all other selections within that race be deleted if ther is a horse which meets the criteria. If there is a selection within that race i will need to keep the row that includes the heading which is coloured blue on the attachment.
I will be working with 20,000 or more selections at a time so it is important that they can be identified quickly for ease of use.
I have tried the functions i know such as MAX and LARGE but this requires me to do alot of work when you consider the ammount of data i'm required to work with.
Is there a easy way to calculate the MAX or MIN of column B dates to column A items that have say many different dates in column B? see attached excel file to show what I am looking for. How to calculate the MIN value in column "B" of the items in column "A" with formula in column "C"...
View 2 Replies View RelatedI have searched your forums and thought I had found a sufficient answer but could not get the vba to work. So any help is greatly appreciated. I am trying to determine a max value from a list then put that value in a cell. Next I want to determine how many times and on what day that max value occured. From there take the value and concatenate them adding a "," between them I have attached an example. I would like the values placed in cells F1 and H1 (the other is a min value and when it occurred)
View 4 Replies View Relatedi 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...................