Finding MAX of a conditional range???

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
 
N

Niek Otten

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
|
 
B

Bob Phillips

=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)
 
D

Don Guillett

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))
 
K

kras

Thank you for all prompt responces and suggestions. And special thanks
for Bob; his formula hit the target.
 
K

kras

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
 
B

Bob Phillips

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)
 
K

kras

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
 
B

Bob Phillips

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)
 
K

kras

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
 
N

Niek Otten

<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
|
 
K

kras

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
 
B

Bob Phillips

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)
 
K

kras

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
 
K

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
 
B

Bob Phillips

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)
 
K

kras

Bob,

Thank you for your reply. I adjusted the formula. It works.

Kindest regards,

kras
 
K

kras

Bob,

Thank you for your reply. I adjusted the formula. It works.

Kindest regards,

kras
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multiple Min & Max range lookup. 3
formula to display the max (or min value) for an entire column, basedon value in an individual cell 1
Arrays, Dates & Blank Cells 3
Min & Max Range Lookup 5
Conditional Max 7
Conditional formatting question? 3
More Selective Maximums 3
Min and Max Function 4

Top