Find max for selected number of values [Closed]

Report
-
 erso -
Hello,

i want to try to find the maximum number for every 96th rows. i have data that is inputed into excel, first column (A) is the date and time and the tenth column (K) contains various values. every 96 rows is one day. within those 96 columns i want to find the maximum number (k column). this has to work for an infinite number of days. any ideas or solutions are appreciated. feel free to contact me with questions.

6 replies

Posts
191
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
212
Please be specific in your question as i understand that first you were saying you have some value in your 96th column so I"ve made formula which will figure out maximum value from 96, 192 and so on column.

Please tell me wtih some examples that what you actually want. I"m sorry that i'm not very smart in macros.

4
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 3370 users have said thank you to us this month

Posts
191
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
212
Suppose you have headings in Row 2 (K2=HEADING) and Formula in Row 1 (K1=FORMULA) ......

K1 = MAX(IF(MOD(ROW($K$3:$K$65536)+94,96)=0,$K$3:$K$65536,0))
Now before quiting the cell Press Ctrl+Shift+Enter. {} will come around formula this is called array formula
don't try to manual enter {} brackets ......... Now this formula will look every 96th row ..... you can try ....

if you need any further clarification don't hesitate to contact me .....



Not a macro solution but...

Have you thought about using pivot tables. This will group the data and find the max of each group (it also has a graph wizard) but you'll need to add a column for just the date first. Depending on your date time format use a formula like:

C2 =Right(A2, 8) ' if stored as text


or use text to columns if stored as date/time (you may want to copy the date/time to a new column first)

Note: Don't leave empty columns between your data or pivot table won't work

If possible I'd record date and time in two seperate columns.
so i just copy and paste this into the K1 cell and it does the rest? i tried it and it only returned the highest overall value in that cell. i need to see each of the highest values for each day in different cells so i can graph it. i was thinking a macro would work better. sry if you get this message twice
Did you ever get this to work. I am trying to do the same thing.
in one column i have the date and time and in another one i have kilo-watts, for each date and time i have a corresponding value in a meter that measures the kilo-watts. every 96 rows is one day and i want to find the maximum for each day. is there a way i can return those values in a seperate column from both of those so i can use it in a graph.
simple Brad - use the MAX fomula.

assuming you have a heading row, date is col A, Watts is col B
in col C starting at row 97, enter =MAX(B2,B97) and then copy this down to the bottom of list.

This will return the max of B for every preceding 96 rows. If you only want to see every 96th output then leave the cells C2:C95 blank but copy C2:C97 and paste from C2:[bottom of list].

Im actually looking at this entry coz im trying to do similar thing but without a fixed number of rows. I want to calculate the MAX using a date range in col A.

eg. Col A = date/time, Col B = value and in Col C i want to see MAX of B for range in A across last 40 mins.

any ideas?
Posts
191
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
212
.