Tags: across, array, average, column, excel, function, microsoft, minimum, msdn, numerical, rows, selected, software, sumproduct, table, time, value, values

Finding minimum value across selected rows of an array

On Microsoft » Microsoft Excel

2,660 words with 2 Comments; publish: Wed, 04 Jun 2008 12:33:00 GMT; (30678.13, « »)

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?

All Comments

Leave a comment...

  • 2 Comments
    • 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?

      >.

      >

      #1; Wed, 04 Jun 2008 12:34:00 GMT
    • 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?

      > >.

      > >

      >

      #2; Wed, 04 Jun 2008 12:35:00 GMT