Sumproduct Help
 

I am working with hourly readings and making a monthly summary. I am looking
for a formula that will find the maximum of all of the hourly values for each
month and return the date/time of that peak. The date time is broken into
components so my data looks like:
Col C Month
Col D Day
Col E Hour
Col N Total
I know I need sumproduct and I am using
=sumproduct((D2:D8785)*--(C2:C8785="month in
question")*--(max(C2:C8785="Month in Question")*--(N2:N8785))). Im guessing
I either have an extra or missing -- and Im not sure about ctrl-shift-enter.
"Month in Question" is changed based on actual reporting month and is not a
named range. Also what modification would I make to return one of the one of
the individual values in col F-M for that same peak date/time.

Thanks

Sumproduct Help
 

Hi,

Suppose you put the month into A2 then the two array formulas are

=INDEX(D1:D99,MATCH(A2&MAX(IF(C1:C99=A2,N1:N99,"") ),C1:C99&N1:N99,0))

=INDEX(E1:E99,MATCH(A2&MAX(IF(C1:C99=A2,N1:N99,"") ),C1:C99&N1:N99,0))

I am assuming the Month is entered as Jan not as a date.

However, there is a theoretical problem here - there could be two equal
spikes in one month and these formulas don't handle that.

These are array formulas so they must be entered by pressing Shift+Ctrl+Enter.


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Steve" wrote:

I am working with hourly readings and making a monthly summary. I am looking
for a formula that will find the maximum of all of the hourly values for each
month and return the date/time of that peak. The date time is broken into
components so my data looks like:
Col C Month
Col D Day
Col E Hour
Col N Total
I know I need sumproduct and I am using
=sumproduct((D2:D8785)*--(C2:C8785="month in
question")*--(max(C2:C8785="Month in Question")*--(N2:N8785))). Im guessing
I either have an extra or missing -- and Im not sure about ctrl-shift-enter.
"Month in Question" is changed based on actual reporting month and is not a
named range. Also what modification would I make to return one of the one of
the individual values in col F-M for that same peak date/time.

Thanks

Sumproduct Help
 

I'll leave other folk to answer your question, but please don't use *--
If you are doing arithmetic with the multiplication operator, the double
unary minus isn't doing anything for you, and will just cause confusion.
--
David Biddulph

Steve wrote:
I am working with hourly readings and making a monthly summary. I am
looking for a formula that will find the maximum of all of the hourly
values for each month and return the date/time of that peak. The
date time is broken into components so my data looks like:
Col C Month
Col D Day
Col E Hour
Col N Total
I know I need sumproduct and I am using
=sumproduct((D2:D8785)*--(C2:C8785="month in
question")*--(max(C2:C8785="Month in Question")*--(N2:N8785))). Im
guessing I either have an extra or missing -- and Im not sure about
ctrl-shift-enter. "Month in Question" is changed based on actual
reporting month and is not a named range. Also what modification
would I make to return one of the one of the individual values in col
F-M for that same peak date/time.

Thanks

Sumproduct Help
 

Thanks!

"Shane Devenshire" wrote:

Hi,

Suppose you put the month into A2 then the two array formulas are

=INDEX(D1:D99,MATCH(A2&MAX(IF(C1:C99=A2,N1:N99,"") ),C1:C99&N1:N99,0))

=INDEX(E1:E99,MATCH(A2&MAX(IF(C1:C99=A2,N1:N99,"") ),C1:C99&N1:N99,0))

I am assuming the Month is entered as Jan not as a date.

However, there is a theoretical problem here - there could be two equal
spikes in one month and these formulas don't handle that.

These are array formulas so they must be entered by pressing Shift+Ctrl+Enter.


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Steve" wrote:

I am working with hourly readings and making a monthly summary. I am looking
for a formula that will find the maximum of all of the hourly values for each
month and return the date/time of that peak. The date time is broken into
components so my data looks like:
Col C Month
Col D Day
Col E Hour
Col N Total
I know I need sumproduct and I am using
=sumproduct((D2:D8785)*--(C2:C8785="month in
question")*--(max(C2:C8785="Month in Question")*--(N2:N8785))). Im guessing
I either have an extra or missing -- and Im not sure about ctrl-shift-enter.
"Month in Question" is changed based on actual reporting month and is not a
named range. Also what modification would I make to return one of the one of
the individual values in col F-M for that same peak date/time.

Thanks