Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Dazed and confused about min, max
 
Posts: n/a
Default Finding minimum value across selected rows of an array

I have a table of data which has time values in column B and numerical values
in column C. I am using the Sumproduct function to find the average value of
C using only data when the time is between 730 and 930 with great success.
The problem is I also need to find the minimum and maximum values during
these same times.

I tried using Max(if( with ctrl-shft-enter (though I'm not sure if this is a
proper formula) with no luck. Can someone please help?
  #2   Report Post  
BobT
 
Posts: n/a
Default

Biff anwered when I asked

I was getting the mode, min, max in D if conditions in a,
b, & c are met. these work for me:

All are array formulas, enter with the key combo of
CTRL,SHIFT,ENTER:

=MODE(IF(A1:A10="x",IF(B1:B10="y",IF(C1:C10="z",D1 :D10))))

=MIN(IF(A1:A10="x",IF(B1:B10="y",IF(C1:C10="z",D1: D10))))

=MAX(IF(A1:A10="x",IF(B1:B10="y",IF(C1:C10="z",D1: D10))))

Biff


-----Original Message-----
I have a table of data which has time values in column B

and numerical values
in column C. I am using the Sumproduct function to find

the average value of
C using only data when the time is between 730 and 930

with great success.
The problem is I also need to find the minimum and

maximum values during
these same times.

I tried using Max(if( with ctrl-shft-enter (though I'm

not sure if this is a
proper formula) with no luck. Can someone please help?
.

  #3   Report Post  
Dazed and confused about min, max
 
Posts: n/a
Default

This worked like a charm. Thank you very much BobT.

"BobT" wrote:

Biff anwered when I asked

I was getting the mode, min, max in D if conditions in a,
b, & c are met. these work for me:

All are array formulas, enter with the key combo of
CTRL,SHIFT,ENTER:

=MODE(IF(A1:A10="x",IF(B1:B10="y",IF(C1:C10="z",D1 :D10))))

=MIN(IF(A1:A10="x",IF(B1:B10="y",IF(C1:C10="z",D1: D10))))

=MAX(IF(A1:A10="x",IF(B1:B10="y",IF(C1:C10="z",D1: D10))))

Biff


-----Original Message-----
I have a table of data which has time values in column B

and numerical values
in column C. I am using the Sumproduct function to find

the average value of
C using only data when the time is between 730 and 930

with great success.
The problem is I also need to find the minimum and

maximum values during
these same times.

I tried using Max(if( with ctrl-shft-enter (though I'm

not sure if this is a
proper formula) with no luck. Can someone please help?
.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert rows based on specific value bob Excel Worksheet Functions 6 February 29th 08 08:11 PM
Finding common data in multiple columns and rows in Excel sparham Excel Worksheet Functions 3 February 12th 05 05:11 AM
Deselect one of many non-adjacent rows selected Nicolle K. Excel Discussion (Misc queries) 1 January 11th 05 07:24 PM
Adding Rows to Master Sheet Excel Newbie New Users to Excel 1 December 23rd 04 11:56 PM
Display selected rows from one worksheet to another Brian Excel Worksheet Functions 5 October 29th 04 12:26 PM


All times are GMT +1. The time now is 09:05 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"