Finding MAX of a conditional range???

  • Thread starter Thread starter kras
  • Start date Start date
K

kras

Hi everyone,

I would like to know if there is a way /set of formulas/ to find
MAX/MIN/ of a range that is defined by certain conditions. To be more
specific I am giving an example: I need to find MAX of range of numbers
/1, 3, 6, 8, etc./ and the range is defined by two dates. To each number
correspond a specific date. In other words, I would like to find MAX of
the range between two dates. I tried a combitanion of MAX and
SUMPRODUCT formulas, but no success.

Any help is welcome.

kras
 
How is your data laid out, where are the dates and where are the numbers? Are the dates sorted? Are there any duplicate dates (and
if so, how to act)?

--
Kind regards,

Niek Otten

|
| Hi everyone,
|
| I would like to know if there is a way /set of formulas/ to find
| MAX/MIN/ of a range that is defined by certain conditions. To be more
| specific I am giving an example: I need to find MAX of range of numbers
| /1, 3, 6, 8, etc./ and the range is defined by two dates. To each number
| correspond a specific date. In other words, I would like to find MAX of
| the range between two dates. I tried a combitanion of MAX and
| SUMPRODUCT formulas, but no success.
|
| Any help is welcome.
|
| kras
|
|
| --
| kras
| ------------------------------------------------------------------------
| kras's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28667
| View this thread: http://www.excelforum.com/showthread.php?threadid=535929
|
 
=MAX(IF((rng>=date1)*(rng<=date2),rng))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
this is an array formula which needs to be entered with ctrl+shift+enter
where checksA contains your dates,n1 is the earlier date,o1 is the later
date and checksd contains the numbers to analyze.

=MAX(IF(ChecksA>=N1,ChecksA<O1,ChecksD))
 
Thank you for all prompt responces and suggestions. And special thanks
for Bob; his formula hit the target.
 
I've stuck again. Now I have to find the avearge of the range but on a
daily basis. I tried Bob's formula and put avg, instead of max and got
range average, but not the daily average. During date 1 and date 2 I
have the process, I estimate, happened on some days /1, 2, 3, etc.
times/ and not happened at all on other days, and networkdays formula
isn't helping me.

Any suggestions are very helpful.

kras
 
You need to add an extra test for weekends and exclude them

=AVERAGE(IF((date_rng>=date1)*(date_rng<=date2)*(WEEKDAY(date_rng,2)<6),amt_
rng))

still an array formula

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Hi Bob,

Thank you for your reply.
Probably I do something wrong, because the formula is working just like
an AVG function.

Here it is in my spreadsheet:
=AVERAGE(IF((A13:A102>=A105)*(A13:A102<=A106)*(WEEKDAY(A13:A102;2)<6);I13:I102))

where,
columb A is date range; A105 is date 1; A106 is date 2; columb I is
estimated process.

I would like to say that occasionally the estimated process occurs
several times per day, which means that I would have several consequent
rows with the same date /date includes:day, month, year/. My intention
is to estimate the process average on a daily basis /divide the sum of
process on the number of days it occured, not the number of
occurences/.

Thank you again
 
kras,

Again it works fine for me.

Can you some actual data examples and tell me what you get and what you
expect to get?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Hi Bob,

In columb A a put dates. The format is: dd.mm.yyyy /I'm from Europ
:)/. So, sometimes a few rows got the same date. For example, let sa
that row cell A2 is 20.04.2006 and cells A3 thru A6 containg dat
21.04.2006. So, I got to days and 5 entries. If I use a regular AV
function the result would be averaged to 5. My purpose is to average i
to days and in this example there are 2 days.

The estimated numbers are in columb I. They are regular numbers /1, 2
5, etc./. For example in cell I2 I have 5, in I3 I have 3, in I4 I hav
6, in I4 I have 5, in I5 I have 6 and in I6 I have 2. I need to find th
average of I on a daily basis, which in this example means to sum all
cells /5 + 3 + 6 +5 + 2 = 21/ and divide it to 2.

kra
 
<dd.mm.yyyy>

That's probably not a date, but text. Try with the ISTEXT() function.

--
Kind regards,

Niek Otten

|
| Hi Bob,
|
| In columb A a put dates. The format is: dd.mm.yyyy /I'm from Europe
| :)/. So, sometimes a few rows got the same date. For example, let say
| that row cell A2 is 20.04.2006 and cells A3 thru A6 containg date
| 21.04.2006. So, I got to days and 5 entries. If I use a regular AVG
| function the result would be averaged to 5. My purpose is to average it
| to days and in this example there are 2 days.
|
| The estimated numbers are in columb I. They are regular numbers /1, 2,
| 5, etc./. For example in cell I2 I have 5, in I3 I have 3, in I4 I have
| 6, in I4 I have 5, in I5 I have 6 and in I6 I have 2. I need to find the
| average of I on a daily basis, which in this example means to sum all I
| cells /5 + 3 + 6 +5 + 2 = 21/ and divide it to 2.
|
| kras
|
|
| --
| kras
| ------------------------------------------------------------------------
| kras's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28667
| View this thread: http://www.excelforum.com/showthread.php?threadid=535929
|
 
Hi Bob, Niek,

Bob, I downloaded your workbook and check the calculations.
Probably, I didn't put the question in the right way, but what I would
like to accomplish is to find the average on a daily basis, not on a
process occurence basis. For example, in your workbook the sum of the
Amount columb is 27. There are only two dates - 20.April and 21.April,
so the average on a daily basis /if I am right. I could misunderstand
what does mean daily average/ should be 27 divided to 2 /number of
days/, that equals 13.5 on average daily. So the average on a daily
basis is 13.5, not 5.4.
Please correct, if I am wrong.

kras
 
Hi kras,

I thought you meant the day's average, not the average per day <vbg>.

Try this formula

=SUM(B2:B20)/SUMPRODUCT((A2:A20<>"")/COUNTIF(A2:A20,A2:A20&""))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Hi Bob,

Yes. It works now.

My initial purpose was to find an average per day for a given period
/from date 1 to date 2/. So, I tried to include that in your last
formula. I did it for the numerator of the formula/
=SUMPRODUCT(--(A2:A20>=date 1);--(A2:A20<=date 2);B2:B20) /, but could
not implement it for the divisor part. Any help would be much
appreciated.

Thanks in advance.

kras
 
Hi Bob,

I did it for the divisor: =SUMPRODUCT(--(A2:A20>=date
1);--(A2:A20<=date 2);(A2:A20<>"")/COUNTIF(A2:A20;A2:A20&"")).

Thank you again for your invaluable help.

Regards,
kras
 
kras,

I looked at this formula and thought that the blank test was unnecessary,
but when I tried it it errored, as you had no coercion operator. So at least
you need

=SUMPRODUCT(--(A2:A20>=date1);--(A2:A20<=date2);--(A2<>A20)/COUNTIF(A2:A20;A
2:A20&""))

but as I said, the blank test is unnecessary as they would be precluded by
testing within the two dates. so all you need is

=SUMPRODUCT(--(A2:A20>=date1);--(A2:A20<=date2)/COUNTIF(A2:A20;A2:A20&""))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Back
Top