Max from a certain range

[nedy_03]

Hi,

I'm facing this sitution : I need to find out the maximum from a range that corresponds to another range that meets the condition to be bettween two values. Ex: on column A I have values in time format that represents the time when the call was received, and on column B the duration af the call. For ths calls received between 09:00 and 09:15 I need to find the maximum value from column B from the range that correspunds to the one defined in column A (09:00 - 09:155)!

Thx,
Nedy

[Zack Barresse]

Hi there, welcome to the board!

First of all, your values in column D need to be in separate cells (two columns, not just one). Now, for your SUM formula, what exactly do you want to sum? I don't follow the logic. It seems you are summing times that fall between the start of the call and the end of the call. Why sum? Wouldn't you count instead?

Regarding your max formula, what are your parameters exactly? You want the max, but with respect to your times what are your requirements? A start time between the specified times?

[Bob Phillips]

In E2:



=SUMIF($A:$A,"<"&TRIM(MID(D2,FIND("-",D2)+1,99)),$B:$B)-
SUMIF($A:$A,"<"&TRIM(LEFT(D2,FIND("-",D2)-1)),$B:$B)

In F2:



=MAX(IF(($A$2:$A$1000<--TRIM(MID($D2,FIND("-",$D2)+1,99)))*
($A$2:$A$1000>=--TRIM(LEFT($D2,FIND("-",$D2)-1))),$B$2:$B$1000))

The latter is an array formula, so commit with Ctrl-Shift-Enter, not just enter.

Copy E2:F2 down.

[Zack Barresse]

Uh, Bob, why would you even think about not separating that data?? It would shorten the formulas and make it easier to work with the data. Plus it would keep it as "real" numbers.

[Bob Phillips]

Because that wasn't the question.

It is not my workbook, you had already suggested separating them (actually you said 'need' which was just plain wrong), so I gave a solution working with the workbook as is.

[Zack Barresse]

Okay, where I said "need", it was laden with my opinions and beliefs. The OP doesn't literally need to, but they should. Rather than formulate a workaround (which is what you did) we should also be suggesting how to make data sets work better and be more intuitive. Keeping Excel numbers (including dates and times) as recognizable numbers is far better than keeping them as text. Sure you can work with them, but it is generally not the best method to take.

[nedy_03]

Hi,

I've separated column "D" as u suggested. What should I do now?

And to answer to u're question: "Why sum?" - because I need to sum the talk duration for every 15 min and find the maximum talk duration for the given 15 min ( for ex: between 15:00:00 and 15:14:59 i have the talk duration sum = 0:41:24 and the max correspons to the call received at 15:01:16 {B4 cell} and is 0:04:36 min long )

Thx,
Nedy

-----------------------------------------------------------------------

First of all, your values in column D need to be in separate cells (two columns, not just one). Now, for your SUM formula, what exactly do you want to sum? I don't follow the logic. It seems you are summing times that fall between the start of the call and the end of the call. Why sum? Wouldn't you count instead?

Regarding your max formula, what are your parameters exactly? You want the max, but with respect to your times what are your requirements? A start time between the specified times?[/quote]

[nedy_03]

Hi,

U're seems to be a really good ideea .. and as I've separated column D in two columns ( as u can see in the attached file) can u tell me the formula to find the maximum for the intervals pls. :banghead:

Thx,
Nedy



In E2:



=SUMIF($A:$A,"<"&TRIM(MID(D2,FIND("-",D2)+1,99)),$B:$B)-
SUMIF($A:$A,"<"&TRIM(LEFT(D2,FIND("-",D2)-1)),$B:$B)

In F2:



=MAX(IF(($A$2:$A$1000<--TRIM(MID($D2,FIND("-",$D2)+1,99)))*
($A$2:$A$1000>=--TRIM(LEFT($D2,FIND("-",$D2)-1))),$B$2:$B$1000))

The latter is an array formula, so commit with Ctrl-Shift-Enter, not just enter.

Copy E2:F2 down.

[Bob Phillips]

Mine was specifically aimed at removing the need to separate the times.

[Zack Barresse]

These are Bob's formulas for separate columns of data...


=SUMIF($A:$A,"<"&D2,$B:$B)-SUMIF($A:$A,"<"&E2,$B:$B)

..

=MAX(IF(($A$2:$A$1000<$E2)*($A$2:$A$1000>=$D2),$B$2:$B$1000))

[Shazam]

Hi firefytr & xld,




I just want to throw my 2 shilings and a haypenny in here.

Input formula in cell F2 and copied down.

=SUMPRODUCT(($A$2:$A$572>=D2)*($A$2:$A$572<=E2)*($B$2:$B$572))


Input formula in cell G2 and copied down.

=SUMPRODUCT(MAX(($A$2:$A$572>=D2)*($A$2:$A$572<=E2)*($B$2:$B$572)))


Sample workbook below.

[Bob Phillips]

But Shaz, why would you use SUMPRODUCT when you can use SUMIF? The second, MAX, may be more debatable, but not the first IMO.

[Shazam]

But Shaz, why would you use SUMPRODUCT when you can use SUMIF? The second, MAX, may be more debatable, but not the first IMO.


Yes the SUMIF solution is the best choice and I think its faster, But I just threw another solution out there. It might give the user other ideas to use in the future.

[Zack Barresse]

Always a pleasure seeing your contributions Shazam! :yes

[Bob Phillips]

It might give the user other ideas to use in the future.

You mean instead of just asking? :)

[nedy_03]

Thank u SHAZAM and to all of u guys ... I really needed a solution ...

[Bob Phillips]

You already had one, and a suggestion for an alternative.

[nedy_03]

Hi,

I have another challenge for u ( problem for me :banghead: ) ... Pls help me on this too ... If u open the attached file, in the columns "F-O" I need to count how many calls have the "talk time" (column B) betweend the interval specified in header row of "F-O" , from the calls received (column A) in the interval specified in "D-E" columns.

I've put some comments and colors to emphasize my requests!

Thx,
Nedy

[Bob Phillips]

F2: =SUMPRODUCT(--($A$2:$A$1000>=$D2),--($A$2:$A$1000<=$E2),--($B$2:$B$1000=0))

G2:
=SUMPRODUCT(--($A$2:$A$1000>=$D2),--($A$2:$A$1000<=$E2),--($B$2:$B$1000<=--MID(G$1,FIND("-",G$1)+1,LEN(G$1)-2-FIND("-",G$1))))-SUM($F$2:F2)

and copy across to N2

O2:

=SUMPRODUCT(--($A$2:$A$1000>=$D2),--($A$2:$A$1000<=$E2))-SUM($F$2:N2)

[nedy_03]

Thx xld ... what can I say ?? A quick and good answer! ..

[nedy_03]

Hello,

I've learned something from u guys! now I found a formula myself for what i asked u .. what u say about a CSE formula : "=COUNT(IF((A2:A1000>=D2)*(A2:A1000<=E2)*(B2:B1000=0),B2:B1000))" ?? ... are there any inconvenients in using them ?? ...

[Bob Phillips]

You can simplify, remove the IF, and the final array



=SUM((A2:A100>=D2)*(A2:A100<=E2)*(B2:B100=0))

or do it as a non-array



=SUMPRODUCT(--(A2:A100>=D2),--(A2:A100<=E2),--(B2:B100=0))

SP is quicker than an array formula, by 10-20%.

[nedy_03]

Hello,

But why the "=SUM((A2:A100>=D2)*(A2:A100<=E2)*(B2:B100=0))" formula it counts the values and not sum them as the name of the function indicate? .. how does it works ? ...

[Bob Phillips]

Because it is not summing the values, if you look you will see that I removed the values, but it is summing the number of items that match all 3 conditions

(A2:A100>=D2)

will return an array of TRUE/FALSE, so will

(A2:A100<=E2)

so will

(B2:B100=0)

when you multiply those arrays of TRUE/FALSE you get an array of 1/0, which is what is summed, all the 1s and all the 0s, hence count.