Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Max values of like cells in a Range
I have a 2 ranges both with 16 rows. 1 column has dates with just 4 dates
being repeated 4 times, which make up the 16 rows. The other column has values that correspond to each of the dates. I need a procedure to scroll through the dates and get the max value from the value column of all LIKE dates..... Is this possible |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Max values of like cells in a Range
Assume the dates are in A1:A16 and are order the same in each group of 4.
Further assume the values to look at are in C1:C16 Sub Tester1() Dim maxVal As Double Set rng1 = Range("A1:A4") i = 0 For Each cell In rng1 i = i + 1 maxVal = Application.Max(cell.Offset(0, 2), _ cell.Offset(4, 2), cell.Offset(8, 2), cell.Offset(12, 2)) Debug.Print cell.Text, maxVal Next End Sub -- Regards, Tom Ogilvy "ibeetb" wrote in message ... I have a 2 ranges both with 16 rows. 1 column has dates with just 4 dates being repeated 4 times, which make up the 16 rows. The other column has values that correspond to each of the dates. I need a procedure to scroll through the dates and get the max value from the value column of all LIKE dates..... Is this possible |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Max values of like cells in a Range
Hey Tom thanks....but it isn't exactly working and I think it is because I
didn't explain it properly. Here are two lists and I want to get the Max(MaxHits) for each of the grp of dates: so the max value for 7/5 aND THE mAX value for 7/12 Date MAXHits 7/5/2003 12200 7/5/2003 132 7/5/2003 87 7/5/2003 501 7/5/2003 294 7/5/2003 24 7/5/2003 333 7/5/2003 61 7/5/2003 500 7/5/2003 163 7/5/2003 1022 7/12/03 8459 7/12/03 208 7/12/03 200 7/12/03 490 7/12/03 295 7/12/03 45 7/12/03 407 7/12/03 42 7/12/03 592 7/12/03 213 7/12/03 1538 "Tom Ogilvy" wrote in message ... Assume the dates are in A1:A16 and are order the same in each group of 4. Further assume the values to look at are in C1:C16 Sub Tester1() Dim maxVal As Double Set rng1 = Range("A1:A4") i = 0 For Each cell In rng1 i = i + 1 maxVal = Application.Max(cell.Offset(0, 2), _ cell.Offset(4, 2), cell.Offset(8, 2), cell.Offset(12, 2)) Debug.Print cell.Text, maxVal Next End Sub -- Regards, Tom Ogilvy "ibeetb" wrote in message ... I have a 2 ranges both with 16 rows. 1 column has dates with just 4 dates being repeated 4 times, which make up the 16 rows. The other column has values that correspond to each of the dates. I need a procedure to scroll through the dates and get the max value from the value column of all LIKE dates..... Is this possible |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Max values of like cells in a Range
Thanks.....what I pastred was just a sample of the data, not the entire
data..... "Tom Ogilvy" wrote in message ... Let's see, You said you had 16 cells with 4 dates. Now you show 21 cells with 2 dates and say the solution offered isn't working? In C2 put in the formula =if(A2<A1,(Max(($A$2:$A$100=A2)*($B$2:$B$100)),"" ) Enter with ctrl+Shift+Enter. Drag fill the formula down your column. Adjust 100 to reflect the last row with data. -- Regards, Tom Ogilvy "ibeetb" wrote in message ... Hey Tom thanks....but it isn't exactly working and I think it is because I didn't explain it properly. Here are two lists and I want to get the Max(MaxHits) for each of the grp of dates: so the max value for 7/5 aND THE mAX value for 7/12 Date MAXHits 7/5/2003 12200 7/5/2003 132 7/5/2003 87 7/5/2003 501 7/5/2003 294 7/5/2003 24 7/5/2003 333 7/5/2003 61 7/5/2003 500 7/5/2003 163 7/5/2003 1022 7/12/03 8459 7/12/03 208 7/12/03 200 7/12/03 490 7/12/03 295 7/12/03 45 7/12/03 407 7/12/03 42 7/12/03 592 7/12/03 213 7/12/03 1538 "Tom Ogilvy" wrote in message ... Assume the dates are in A1:A16 and are order the same in each group of 4. Further assume the values to look at are in C1:C16 Sub Tester1() Dim maxVal As Double Set rng1 = Range("A1:A4") i = 0 For Each cell In rng1 i = i + 1 maxVal = Application.Max(cell.Offset(0, 2), _ cell.Offset(4, 2), cell.Offset(8, 2), cell.Offset(12, 2)) Debug.Print cell.Text, maxVal Next End Sub -- Regards, Tom Ogilvy "ibeetb" wrote in message ... I have a 2 ranges both with 16 rows. 1 column has dates with just 4 dates being repeated 4 times, which make up the 16 rows. The other column has values that correspond to each of the dates. I need a procedure to scroll through the dates and get the max value from the value column of all LIKE dates..... Is this possible |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculate values in an unlimited range of cells in a column WITHOUTalso calculating values that had previously been filtered AGAINST? | Excel Discussion (Misc queries) | |||
sum values from range of cells if cells correcponding have the sam | New Users to Excel | |||
If certain cells not blank, and cells in range are, set values to | Excel Discussion (Misc queries) | |||
Count values in a range of cells | Excel Worksheet Functions | |||
Find a range of values in a range of cells | Excel Worksheet Functions |