Search : in
By :

Find max for selected number of values

Last answer on Oct 29, 2009 2:46:35 am GMT brad, on May 21, 2009 4:23:01 pm BST 
 Report this message to moderators

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.

Configuration: Windows XP Excel 2003

Best answers for « Find max for selected number of values » in :
[C language]Checking whether an integer is a prime number Show[C language]Checking whether an integer is a prime number Definition of a prime number Algorithm 1: dividers between 2 and N-1 will be tested Algorithm 2: Even dividers will not be tested, research is limited to odd dividers Algorithm 3:...
Optimizing Firefox completely ShowOptimizing Firefox completely Intro Increase the speed of loading pages Optimize memory usage Intro Type about:config in the address bar of Firefox, then read and confirm the warning. - To obtain a value, type the name or part...
[C Language] finding the square root of a number Show[C Language] finding the square root of a number Intro Notes Intro A simple C program allowing you to find the square root of a number. #include #include int main(void) { double x = 4.0, result;...
Download Find MAC Address by LizardSystems Show
Download Random Number Generator Pro ShowRandom Number Generator Pro is a tool that generates a list of random numbers based on customizable criteria. You can choose the minimum and maximum limits and incrementing numbers. Limits can be positive or negative values. Advantage The...
Download Random Number Generator Pro ShowRandom Number Generator Pro is a very simple tool to generate a random number list. There are several combinations and you can define the randomization criteria. You simply select the lower and upper limits as well as the number increments and the...
HTML forms ShowForms Interactive forms let web page authors give their pages interactive elements, such as for receiving messages from their readers, much like the reply cards found in some magazines. The reader enters information by filling in fields or clicking...
The HTTP protocol ShowIntroduction to the HTTP protocol Since 1990 HTTP protocol (HyperText Transfer Protocol) has been the most widely used protocol on the Internet. Version 0.9 was only intended to transfer data over the Internet (in particular Web pages written in...
Spreadsheet - Formulas ShowIntroduction to Formulas The main use of a spreadsheet is to automate calculations, which means using cells to perform operations based on values in other cells. The spreadsheet recalculates all the values each time a change is made to the value of...
Ask your question Reply

1

mubashir aziz, on May 22, 2009 5:20:01 am BST
  • +1

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 .....



Never ashamed to get or give Advise.
Muhammad Mubashir Aziz , Lodhran

   
Reply to mubashir aziz

2

brad, on May 22, 2009 2:23:52 pm BST

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

   
Reply to brad

6

Geo, on Aug 5, 2009 2:08:04 pm BST

Did you ever get this to work. I am trying to do the same thing.

   
Reply to Geo

3

mubashir aziz, on May 25, 2009 3:12:36 am BST
  • +1

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.

Never ashamed to get or give Advise.
Muhammad Mubashir Aziz , Lodhran

   
Reply to mubashir aziz

4

Brad, on May 26, 2009 12:10:26 pm BST

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.

   
Reply to Brad

8

 erso, on Oct 29, 2009 2:46:35 am GMT

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?

   
Reply to erso

5

Bob, on Jun 9, 2009 6:59:38 pm BST

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.

   
Reply to Bob

7

mubashir aziz, on Sep 4, 2009 6:21:19 am BST

. Never ashamed to get or give Advise.
Muhammad Mubashir Aziz , Lodhran, Pakistan

   
Reply to mubashir aziz
Ask your question Reply
They need your help