Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
Between using Sumproduct | New Users to Excel | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
sumproduct | Excel Discussion (Misc queries) | |||
Sumproduct? | Excel Discussion (Misc queries) |