Find the largest number

[Guest]

I had posted a counting and summing question here a couple weeks ago and
received a great answer and introduction to the sumproduct function. I'm
looking for one more item, this one extracting the largest number from a
range but only if conditions are met for other ranges.

The help I received before with the sumproduct function allowed me to count
and sum results from cell ranges e1:e10 when conditions were true in ranges
a1:a10, b1:b10, c1:c10 and d1:d10. So if a4, b4, c4 and d4 conditions were
true, i was able to count e4 and sum e4. Same for row 6, etc.

What I'm looking for is a way to identify the largest number in e1:e10 when
the first four conditions are true. Thus, in my previous example, if e4 was
10 and e6 was 6, I want my result to be 10, even if e8 [which should be
ignored if there is a false condition in a8, b8, c8 or d8] is 15. I have
tried working MAX or LARGE into some IF functions, but it would always return
the largest number [i.e., 15 from e8] in the whole range rather than the
ranges with true conditions.

Is what I'm looking for possible? Did this make sense?

Thanks in advance.

 

[Frank Kabel]

Hi
try the following array formula (entered with cTRL+SHIFT+ENTER):
=MAX(IF(A1:A10="cond1")*(B1:B10="cond2")*(C1:C10="cond3"),E1:E10))

or the array formula
=MAX((A1:A10="cond1")*(B1:B10="cond2")*(C1:C10="cond3")*(E1:E10))

 

[Bob Phillips]

or

=MAX(--(A1:A10=1),--(B1:B10="text1"),--(C1:C10="text2"),--(D110=5)*E1:E10)

still array entered, Ctrl-SHift-Enter.

Change the values to suit.

--

HTH

RP

Hi
try the following array formula (entered with cTRL+SHIFT+ENTER):
=MAX(IF(A1:A10="cond1")*(B1:B10="cond2")*(C1:C10="cond3"),E1:E10))

or the array formula
=MAX((A1:A10="cond1")*(B1:B10="cond2")*(C1:C10="cond3")*(E1:E10))


--
Regards
Frank Kabel
Frankfurt, Germany

I had posted a counting and summing question here a couple weeks ago
and received a great answer and introduction to the sumproduct
function. I'm looking for one more item, this one extracting the
largest number from a range but only if conditions are met for other
ranges.

The help I received before with the sumproduct function allowed me to
count and sum results from cell ranges e1:e10 when conditions were
true in ranges a1:a10, b1:b10, c1:c10 and d1:d10. So if a4, b4, c4
and d4 conditions were true, i was able to count e4 and sum e4. Same
for row 6, etc.

What I'm looking for is a way to identify the largest number in
e1:e10 when the first four conditions are true. Thus, in my previous
example, if e4 was 10 and e6 was 6, I want my result to be 10, even
if e8 [which should be ignored if there is a false condition in a8,
b8, c8 or d8] is 15. I have tried working MAX or LARGE into some IF
functions, but it would always return the largest number [i.e., 15
from e8] in the whole range rather than the ranges with true
conditions.

Is what I'm looking for possible? Did this make sense?

Thanks in advance.

 

[Tom Ogilvy]

You must have left something out Bob. That doesn't work.

--
Regards,
Tom Ogilvy

or

=MAX(--(A1:A10=1),--(B1:B10="text1"),--(C1:C10="text2"),--(D110=5)*E1:E10)

still array entered, Ctrl-SHift-Enter.

Change the values to suit.

--

HTH

RP

Hi
try the following array formula (entered with cTRL+SHIFT+ENTER):
=MAX(IF(A1:A10="cond1")*(B1:B10="cond2")*(C1:C10="cond3"),E1:E10))

or the array formula
=MAX((A1:A10="cond1")*(B1:B10="cond2")*(C1:C10="cond3")*(E1:E10))


--
Regards
Frank Kabel
Frankfurt, Germany

I had posted a counting and summing question here a couple weeks ago
and received a great answer and introduction to the sumproduct
function. I'm looking for one more item, this one extracting the
largest number from a range but only if conditions are met for other
ranges.

The help I received before with the sumproduct function allowed me to
count and sum results from cell ranges e1:e10 when conditions were
true in ranges a1:a10, b1:b10, c1:c10 and d1:d10. So if a4, b4, c4
and d4 conditions were true, i was able to count e4 and sum e4. Same
for row 6, etc.

What I'm looking for is a way to identify the largest number in
e1:e10 when the first four conditions are true. Thus, in my previous
example, if e4 was 10 and e6 was 6, I want my result to be 10, even
if e8 [which should be ignored if there is a false condition in a8,
b8, c8 or d8] is 15. I have tried working MAX or LARGE into some IF
functions, but it would always return the largest number [i.e., 15
from e8] in the whole range rather than the ranges with true
conditions.

Is what I'm looking for possible? Did this make sense?

Thanks in advance.

 

[Bob Phillips]

Tom,

It works for me. I only did it because Frank's respomnse didn't work for me
(sic!)

This data

A B C D E
1 text1 text2 5 10
1 text1 text2 5 15
1 text1 text2 6 25

gives me 15.

confused RP

You must have left something out Bob. That doesn't work.

--
Regards,
Tom Ogilvy

or

=MAX(--(A1:A10=1),--(B1:B10="text1"),--(C1:C10="text2"),--(D110=5)*E1:E10)

still array entered, Ctrl-SHift-Enter.

Change the values to suit.

--

HTH

RP

Hi
try the following array formula (entered with cTRL+SHIFT+ENTER):
=MAX(IF(A1:A10="cond1")*(B1:B10="cond2")*(C1:C10="cond3"),E1:E10))

or the array formula
=MAX((A1:A10="cond1")*(B1:B10="cond2")*(C1:C10="cond3")*(E1:E10))


--
Regards
Frank Kabel
Frankfurt, Germany


mdoyle13 wrote:
I had posted a counting and summing question here a couple weeks ago
and received a great answer and introduction to the sumproduct
function. I'm looking for one more item, this one extracting the
largest number from a range but only if conditions are met for other
ranges.

The help I received before with the sumproduct function allowed me to
count and sum results from cell ranges e1:e10 when conditions were
true in ranges a1:a10, b1:b10, c1:c10 and d1:d10. So if a4, b4, c4
and d4 conditions were true, i was able to count e4 and sum e4. Same
for row 6, etc.

What I'm looking for is a way to identify the largest number in
e1:e10 when the first four conditions are true. Thus, in my previous
example, if e4 was 10 and e6 was 6, I want my result to be 10, even
if e8 [which should be ignored if there is a false condition in a8,
b8, c8 or d8] is 15. I have tried working MAX or LARGE into some IF
functions, but it would always return the largest number [i.e., 15
from e8] in the whole range rather than the ranges with true
conditions.

Is what I'm looking for possible? Did this make sense?

Thanks in advance.

 

[Frank Kabel]

Hi Bob
it also does not work for me )
lets exchange a test file <vbg>

--
Regards
Frank Kabel
Frankfurt, Germany

Tom,

It works for me. I only did it because Frank's respomnse didn't work
for me (sic!)

This data

A B C D E
1 text1 text2 5 10
1 text1 text2 5 15
1 text1 text2 6 25

gives me 15.

confused RP

You must have left something out Bob. That doesn't work.

=MAX(--(A1:A10=1),--(B1:B10="text1"),--(C1:C10="text2"),--(D110=5)*E1
:E10)

still array entered, Ctrl-SHift-Enter.

Change the values to suit.

--

HTH

RP

Hi
try the following array formula (entered with cTRL+SHIFT+ENTER):
=MAX(IF(A1:A10="cond1")*(B1:B10="cond2")*(C1:C10="cond3"),E1:E10))

or the array formula
=MAX((A1:A10="cond1")*(B1:B10="cond2")*(C1:C10="cond3")*(E1:E10))


--
Regards
Frank Kabel
Frankfurt, Germany


mdoyle13 wrote:
I had posted a counting and summing question here a couple weeks
ago and received a great answer and introduction to the sumproduct
function. I'm looking for one more item, this one extracting the
largest number from a range but only if conditions are met for
other ranges.

The help I received before with the sumproduct function allowed
me to count and sum results from cell ranges e1:e10 when
conditions were true in ranges a1:a10, b1:b10, c1:c10 and d1:d10.
So if a4, b4, c4 and d4 conditions were true, i was able to count
e4 and sum e4. Same for row 6, etc.

What I'm looking for is a way to identify the largest number in
e1:e10 when the first four conditions are true. Thus, in my
previous example, if e4 was 10 and e6 was 6, I want my result to
be 10, even if e8 [which should be ignored if there is a false
condition in a8, b8, c8 or d8] is 15. I have tried working MAX or
LARGE into some IF functions, but it would always return the
largest number [i.e., 15 from e8] in the whole range rather than
the ranges with true conditions.

Is what I'm looking for possible? Did this make sense?

Thanks in advance.

 

[Tom Ogilvy]

A B C D E
1 text1 text2 5 10
1 text1 text2 5 15
1 text1 text2 6 25
5 ABC efgh 5 1000

would give you 1000

A, B, C will have no bearing on the results unless the combination of D and
E result in a number less than 1. (columns A, B, and C will return either a
0 or a 1 so the max of those columns will be zero or 1. The last part of
your formula restricts the results to the max in column E for Column D = 5.

--
Regards,
Tom Ogilvy

Tom,

It works for me. I only did it because Frank's respomnse didn't work for me
(sic!)

This data

A B C D E
1 text1 text2 5 10
1 text1 text2 5 15
1 text1 text2 6 25

gives me 15.

confused RP

You must have left something out Bob. That doesn't work.

=MAX(--(A1:A10=1),--(B1:B10="text1"),--(C1:C10="text2"),--(D110=5)*E1:E10)

still array entered, Ctrl-SHift-Enter.

Change the values to suit.

--

HTH

RP

Hi
try the following array formula (entered with cTRL+SHIFT+ENTER):
=MAX(IF(A1:A10="cond1")*(B1:B10="cond2")*(C1:C10="cond3"),E1:E10))

or the array formula
=MAX((A1:A10="cond1")*(B1:B10="cond2")*(C1:C10="cond3")*(E1:E10))


--
Regards
Frank Kabel
Frankfurt, Germany


mdoyle13 wrote:
I had posted a counting and summing question here a couple weeks ago
and received a great answer and introduction to the sumproduct
function. I'm looking for one more item, this one extracting the
largest number from a range but only if conditions are met for other
ranges.

The help I received before with the sumproduct function allowed me to
count and sum results from cell ranges e1:e10 when conditions were
true in ranges a1:a10, b1:b10, c1:c10 and d1:d10. So if a4, b4, c4
and d4 conditions were true, i was able to count e4 and sum e4. Same
for row 6, etc.

What I'm looking for is a way to identify the largest number in
e1:e10 when the first four conditions are true. Thus, in my previous
example, if e4 was 10 and e6 was 6, I want my result to be 10, even
if e8 [which should be ignored if there is a false condition in a8,
b8, c8 or d8] is 15. I have tried working MAX or LARGE into some IF
functions, but it would always return the largest number [i.e., 15
from e8] in the whole range rather than the ranges with true
conditions.

Is what I'm looking for possible? Did this make sense?

Thanks in advance.

 

[Guest]

Thanks to all. I have tried all of them and keep coming up with the same
result I was getting before, namely it giving me the largest number every
time. Maybe an example of what I'm trying to get will help.

A B C D E
1 R V 11 3
1 R V 24 7
1 P V 7 22
2 R H 20 -2
2 R H 33 17
2 R H 20 21
2 R H 33 4
2 P H 7 13

What I'm trying to do is get the largest number from E when the other
conditions would be R, H and 33. The result should be 17, but I'm always
getting 22. For R,H and 20, I want the result to be 21, but it is coming up
22.

Did I mention this would be reading from about 2000 rows? That's why I'm
looking for a formula to extract the largest for each condition rather than
having to go through each row for multiple conditions.

Thanks again.

Hi Bob
it also does not work for me )
lets exchange a test file <vbg>

--
Regards
Frank Kabel
Frankfurt, Germany

Tom,

It works for me. I only did it because Frank's respomnse didn't work
for me (sic!)

This data

A B C D E
1 text1 text2 5 10
1 text1 text2 5 15
1 text1 text2 6 25

gives me 15.

confused RP

You must have left something out Bob. That doesn't work.

--
Regards,
Tom Ogilvy

or

=MAX(--(A1:A10=1),--(B1:B10="text1"),--(C1:C10="text2"),--(D110=5)*E1
:E10)

still array entered, Ctrl-SHift-Enter.

Change the values to suit.

--

HTH

RP

Hi
try the following array formula (entered with cTRL+SHIFT+ENTER):
=MAX(IF(A1:A10="cond1")*(B1:B10="cond2")*(C1:C10="cond3"),E1:E10))

or the array formula
=MAX((A1:A10="cond1")*(B1:B10="cond2")*(C1:C10="cond3")*(E1:E10))


--
Regards
Frank Kabel
Frankfurt, Germany


mdoyle13 wrote:
I had posted a counting and summing question here a couple weeks
ago and received a great answer and introduction to the sumproduct
function. I'm looking for one more item, this one extracting the
largest number from a range but only if conditions are met for
other ranges.

The help I received before with the sumproduct function allowed
me to count and sum results from cell ranges e1:e10 when
conditions were true in ranges a1:a10, b1:b10, c1:c10 and d1:d10.
So if a4, b4, c4 and d4 conditions were true, i was able to count
e4 and sum e4. Same for row 6, etc.

What I'm looking for is a way to identify the largest number in
e1:e10 when the first four conditions are true. Thus, in my
previous example, if e4 was 10 and e6 was 6, I want my result to
be 10, even if e8 [which should be ignored if there is a false
condition in a8, b8, c8 or d8] is 15. I have tried working MAX or
LARGE into some IF functions, but it would always return the
largest number [i.e., 15 from e8] in the whole range rather than
the ranges with true conditions.

Is what I'm looking for possible? Did this make sense?

Thanks in advance.

 

[Frank Kabel]

Hi
try the array formula (entered with CTRL+SHIFT+ENTER):
=MAX((B1:B1000="R")*(C1:C1000="H")*(D11000=33)*(E1:E1000))

--
Regards
Frank Kabel
Frankfurt, Germany

Thanks to all. I have tried all of them and keep coming up with the
same result I was getting before, namely it giving me the largest
number every time. Maybe an example of what I'm trying to get will
help.

A B C D E
1 R V 11 3
1 R V 24 7
1 P V 7 22
2 R H 20 -2
2 R H 33 17
2 R H 20 21
2 R H 33 4
2 P H 7 13

What I'm trying to do is get the largest number from E when the other
conditions would be R, H and 33. The result should be 17, but I'm
always getting 22. For R,H and 20, I want the result to be 21, but it
is coming up
22.

Did I mention this would be reading from about 2000 rows? That's why
I'm looking for a formula to extract the largest for each condition
rather than having to go through each row for multiple conditions.

Thanks again.

Hi Bob
it also does not work for me )
lets exchange a test file <vbg>

--
Regards
Frank Kabel
Frankfurt, Germany

Tom,

It works for me. I only did it because Frank's respomnse didn't work
for me (sic!)

This data

A B C D E
1 text1 text2 5 10
1 text1 text2 5 15
1 text1 text2 6 25

gives me 15.

confused RP


You must have left something out Bob. That doesn't work.

--
Regards,
Tom Ogilvy

or

=MAX(--(A1:A10=1),--(B1:B10="text1"),--(C1:C10="text2"),--(D110=5)*E1

E10)

still array entered, Ctrl-SHift-Enter.

Change the values to suit.

--

HTH

RP

Hi
try the following array formula (entered with cTRL+SHIFT+ENTER):
=MAX(IF(A1:A10="cond1")*(B1:B10="cond2")*(C1:C10="cond3"),E1:E10))

or the array formula
=MAX((A1:A10="cond1")*(B1:B10="cond2")*(C1:C10="cond3")*(E1:E10))


--
Regards
Frank Kabel
Frankfurt, Germany


mdoyle13 wrote:
I had posted a counting and summing question here a couple weeks
ago and received a great answer and introduction to the sumproduct
function. I'm looking for one more item, this one extracting the
largest number from a range but only if conditions are met for
other ranges.

The help I received before with the sumproduct function allowed
me to count and sum results from cell ranges e1:e10 when
conditions were true in ranges a1:a10, b1:b10, c1:c10 and
d1:d10. So if a4, b4, c4 and d4 conditions were true, i was
able to count e4 and sum e4. Same for row 6, etc.

What I'm looking for is a way to identify the largest number in
e1:e10 when the first four conditions are true. Thus, in my
previous example, if e4 was 10 and e6 was 6, I want my result to
be 10, even if e8 [which should be ignored if there is a false
condition in a8, b8, c8 or d8] is 15. I have tried working MAX
or LARGE into some IF functions, but it would always return the
largest number [i.e., 15 from e8] in the whole range rather than
the ranges with true conditions.

Is what I'm looking for possible? Did this make sense?

Thanks in advance.

 

[Myrna Larson]

I think he omitted the multiplication, i.e. should be

=MAX((A1:A10=1)*(B1:B10="text1")*(C1:C10="text2")*(D110=5)*E1:E10))

 

[Guest]

Frank, thanks for the tips and help. The problem that has arisen with this
attempt is #VALUE! error message. The error showed up even when I broke it
down to the minimums of d*e. I did save it as an array, so I don't think
format is the problem. It's just a doozy, it appears.

Hi
try the array formula (entered with CTRL+SHIFT+ENTER):
=MAX((B1:B1000="R")*(C1:C1000="H")*(D11000=33)*(E1:E1000))

--
Regards
Frank Kabel
Frankfurt, Germany

Thanks to all. I have tried all of them and keep coming up with the
same result I was getting before, namely it giving me the largest
number every time. Maybe an example of what I'm trying to get will
help.

A B C D E
1 R V 11 3
1 R V 24 7
1 P V 7 22
2 R H 20 -2
2 R H 33 17
2 R H 20 21
2 R H 33 4
2 P H 7 13

What I'm trying to do is get the largest number from E when the other
conditions would be R, H and 33. The result should be 17, but I'm
always getting 22. For R,H and 20, I want the result to be 21, but it
is coming up
22.

Did I mention this would be reading from about 2000 rows? That's why
I'm looking for a formula to extract the largest for each condition
rather than having to go through each row for multiple conditions.

Thanks again.

Hi Bob
it also does not work for me )
lets exchange a test file <vbg>

--
Regards
Frank Kabel
Frankfurt, Germany


Bob Phillips wrote:
Tom,

It works for me. I only did it because Frank's respomnse didn't work
for me (sic!)

This data

A B C D E
1 text1 text2 5 10
1 text1 text2 5 15
1 text1 text2 6 25

gives me 15.

confused RP


You must have left something out Bob. That doesn't work.

--
Regards,
Tom Ogilvy

or




=MAX(--(A1:A10=1),--(B1:B10="text1"),--(C1:C10="text2"),--(D110=5)*E1
E10)

still array entered, Ctrl-SHift-Enter.

Change the values to suit.

--

HTH

RP

Hi
try the following array formula (entered with cTRL+SHIFT+ENTER):
=MAX(IF(A1:A10="cond1")*(B1:B10="cond2")*(C1:C10="cond3"),E1:E10))

or the array formula
=MAX((A1:A10="cond1")*(B1:B10="cond2")*(C1:C10="cond3")*(E1:E10))


--
Regards
Frank Kabel
Frankfurt, Germany


mdoyle13 wrote:
I had posted a counting and summing question here a couple weeks
ago and received a great answer and introduction to the
sumproduct
function. I'm looking for one more item, this one extracting the
largest number from a range but only if conditions are met for
other ranges.

The help I received before with the sumproduct function allowed
me to count and sum results from cell ranges e1:e10 when
conditions were true in ranges a1:a10, b1:b10, c1:c10 and
d1:d10. So if a4, b4, c4 and d4 conditions were true, i was
able to count e4 and sum e4. Same for row 6, etc.

What I'm looking for is a way to identify the largest number in
e1:e10 when the first four conditions are true. Thus, in my
previous example, if e4 was 10 and e6 was 6, I want my result to
be 10, even if e8 [which should be ignored if there is a false
condition in a8, b8, c8 or d8] is 15. I have tried working MAX
or LARGE into some IF functions, but it would always return the
largest number [i.e., 15 from e8] in the whole range rather than
the ranges with true conditions.

Is what I'm looking for possible? Did this make sense?

Thanks in advance.

 

[Frank Kabel]

Hi
any chance you have a #VALUE error in your data range or are there text
entries in column E?

--
Regards
Frank Kabel
Frankfurt, Germany

Frank, thanks for the tips and help. The problem that has arisen with
this attempt is #VALUE! error message. The error showed up even when
I broke it down to the minimums of d*e. I did save it as an array, so
I don't think format is the problem. It's just a doozy, it appears.

Hi
try the array formula (entered with CTRL+SHIFT+ENTER):
=MAX((B1:B1000="R")*(C1:C1000="H")*(D11000=33)*(E1:E1000))

--
Regards
Frank Kabel
Frankfurt, Germany

Thanks to all. I have tried all of them and keep coming up with the
same result I was getting before, namely it giving me the largest
number every time. Maybe an example of what I'm trying to get will
help.

A B C D E
1 R V 11 3
1 R V 24 7
1 P V 7 22
2 R H 20 -2
2 R H 33 17
2 R H 20 21
2 R H 33 4
2 P H 7 13

What I'm trying to do is get the largest number from E when the
other conditions would be R, H and 33. The result should be 17, but
I'm always getting 22. For R,H and 20, I want the result to be 21,
but it is coming up
22.

Did I mention this would be reading from about 2000 rows? That's why
I'm looking for a formula to extract the largest for each condition
rather than having to go through each row for multiple conditions.

Thanks again.

:

Hi Bob
it also does not work for me )
lets exchange a test file <vbg>

--
Regards
Frank Kabel
Frankfurt, Germany


Bob Phillips wrote:
Tom,

It works for me. I only did it because Frank's respomnse didn't
work for me (sic!)

This data

A B C D E
1 text1 text2 5 10
1 text1 text2 5 15
1 text1 text2 6 25

gives me 15.

confused RP


You must have left something out Bob. That doesn't work.

--
Regards,
Tom Ogilvy

message or

=MAX(--(A1:A10=1),--(B1:B10="text1"),--(C1:C10="text2"),--(D110=5)*E1

E10)

still array entered, Ctrl-SHift-Enter.

Change the values to suit.

--

HTH

RP

Hi
try the following array formula (entered with
cTRL+SHIFT+ENTER):
=MAX(IF(A1:A10="cond1")*(B1:B10="cond2")*(C1:C10="cond3"),E1:E10))

or the array formula
=MAX((A1:A10="cond1")*(B1:B10="cond2")*(C1:C10="cond3")*(E1:E10))


--
Regards
Frank Kabel
Frankfurt, Germany


mdoyle13 wrote:
I had posted a counting and summing question here a couple
weeks ago and received a great answer and introduction to the
sumproduct function. I'm looking for one more item, this one
extracting the largest number from a range but only if
conditions are met for other ranges.

The help I received before with the sumproduct function
allowed me to count and sum results from cell ranges e1:e10
when conditions were true in ranges a1:a10, b1:b10, c1:c10 and
d1:d10. So if a4, b4, c4 and d4 conditions were true, i was
able to count e4 and sum e4. Same for row 6, etc.

What I'm looking for is a way to identify the largest number
in e1:e10 when the first four conditions are true. Thus, in my
previous example, if e4 was 10 and e6 was 6, I want my result
to be 10, even if e8 [which should be ignored if there is a
false condition in a8, b8, c8 or d8] is 15. I have tried
working MAX or LARGE into some IF functions, but it would
always return the largest number [i.e., 15 from e8] in the
whole range rather than the ranges with true conditions.

Is what I'm looking for possible? Did this make sense?

Thanks in advance.

 

[Guest]

All of the cells in E have a formula to get a number. But if the condition is
false, it returns "". Perhaps that is causing the error?

Hi
any chance you have a #VALUE error in your data range or are there text
entries in column E?

--
Regards
Frank Kabel
Frankfurt, Germany

Frank, thanks for the tips and help. The problem that has arisen with
this attempt is #VALUE! error message. The error showed up even when
I broke it down to the minimums of d*e. I did save it as an array, so
I don't think format is the problem. It's just a doozy, it appears.

Hi
try the array formula (entered with CTRL+SHIFT+ENTER):
=MAX((B1:B1000="R")*(C1:C1000="H")*(D11000=33)*(E1:E1000))

--
Regards
Frank Kabel
Frankfurt, Germany


mdoyle13 wrote:
Thanks to all. I have tried all of them and keep coming up with the
same result I was getting before, namely it giving me the largest
number every time. Maybe an example of what I'm trying to get will
help.

A B C D E
1 R V 11 3
1 R V 24 7
1 P V 7 22
2 R H 20 -2
2 R H 33 17
2 R H 20 21
2 R H 33 4
2 P H 7 13

What I'm trying to do is get the largest number from E when the
other conditions would be R, H and 33. The result should be 17, but
I'm always getting 22. For R,H and 20, I want the result to be 21,
but it is coming up
22.

Did I mention this would be reading from about 2000 rows? That's why
I'm looking for a formula to extract the largest for each condition
rather than having to go through each row for multiple conditions.

Thanks again.

:

Hi Bob
it also does not work for me )
lets exchange a test file <vbg>

--
Regards
Frank Kabel
Frankfurt, Germany


Bob Phillips wrote:
Tom,

It works for me. I only did it because Frank's respomnse didn't
work for me (sic!)

This data

A B C D E
1 text1 text2 5 10
1 text1 text2 5 15
1 text1 text2 6 25

gives me 15.

confused RP


You must have left something out Bob. That doesn't work.

--
Regards,
Tom Ogilvy

message or





=MAX(--(A1:A10=1),--(B1:B10="text1"),--(C1:C10="text2"),--(D110=5)*E1
E10)

still array entered, Ctrl-SHift-Enter.

Change the values to suit.

--

HTH

RP

Hi
try the following array formula (entered with
cTRL+SHIFT+ENTER):

=MAX(IF(A1:A10="cond1")*(B1:B10="cond2")*(C1:C10="cond3"),E1:E10))

or the array formula

=MAX((A1:A10="cond1")*(B1:B10="cond2")*(C1:C10="cond3")*(E1:E10))


--
Regards
Frank Kabel
Frankfurt, Germany


mdoyle13 wrote:
I had posted a counting and summing question here a couple
weeks ago and received a great answer and introduction to the
sumproduct function. I'm looking for one more item, this one
extracting the largest number from a range but only if
conditions are met for other ranges.

The help I received before with the sumproduct function
allowed me to count and sum results from cell ranges e1:e10
when conditions were true in ranges a1:a10, b1:b10, c1:c10 and
d1:d10. So if a4, b4, c4 and d4 conditions were true, i was
able to count e4 and sum e4. Same for row 6, etc.

What I'm looking for is a way to identify the largest number
in e1:e10 when the first four conditions are true. Thus, in my
previous example, if e4 was 10 and e6 was 6, I want my result
to be 10, even if e8 [which should be ignored if there is a
false condition in a8, b8, c8 or d8] is 15. I have tried
working MAX or LARGE into some IF functions, but it would
always return the largest number [i.e., 15 from e8] in the
whole range rather than the ranges with true conditions.

Is what I'm looking for possible? Did this make sense?

Thanks in advance.

 

[Frank Kabel]

Hi
yes this is causing this error.

--
Regards
Frank Kabel
Frankfurt, Germany

All of the cells in E have a formula to get a number. But if the
condition is false, it returns "". Perhaps that is causing the error?

Hi
any chance you have a #VALUE error in your data range or are there
text entries in column E?

--
Regards
Frank Kabel
Frankfurt, Germany

Frank, thanks for the tips and help. The problem that has arisen
with this attempt is #VALUE! error message. The error showed up
even when I broke it down to the minimums of d*e. I did save it as
an array, so I don't think format is the problem. It's just a
doozy, it appears.




:

Hi
try the array formula (entered with CTRL+SHIFT+ENTER):
=MAX((B1:B1000="R")*(C1:C1000="H")*(D11000=33)*(E1:E1000))

--
Regards
Frank Kabel
Frankfurt, Germany


mdoyle13 wrote:
Thanks to all. I have tried all of them and keep coming up with
the same result I was getting before, namely it giving me the
largest number every time. Maybe an example of what I'm trying to
get will help.

A B C D E
1 R V 11 3
1 R V 24 7
1 P V 7 22
2 R H 20 -2
2 R H 33 17
2 R H 20 21
2 R H 33 4
2 P H 7 13

What I'm trying to do is get the largest number from E when the
other conditions would be R, H and 33. The result should be 17,
but I'm always getting 22. For R,H and 20, I want the result to
be 21, but it is coming up
22.

Did I mention this would be reading from about 2000 rows? That's why
I'm looking for a formula to extract the largest for each
condition rather than having to go through each row for multiple
conditions.

Thanks again.

:

Hi Bob
it also does not work for me )
lets exchange a test file <vbg>

--
Regards
Frank Kabel
Frankfurt, Germany


Bob Phillips wrote:
Tom,

It works for me. I only did it because Frank's respomnse didn't
work for me (sic!)

This data

A B C D E
1 text1 text2 5 10
1 text1 text2 5 15
1 text1 text2 6 25

gives me 15.

confused RP


You must have left something out Bob. That doesn't work.

--
Regards,
Tom Ogilvy

message or

=MAX(--(A1:A10=1),--(B1:B10="text1"),--(C1:C10="text2"),--(D110=5)*E1

E10)

still array entered, Ctrl-SHift-Enter.

Change the values to suit.

--

HTH

RP

Hi
try the following array formula (entered with
cTRL+SHIFT+ENTER):

=MAX(IF(A1:A10="cond1")*(B1:B10="cond2")*(C1:C10="cond3"),E1:E10))

or the array formula

=MAX((A1:A10="cond1")*(B1:B10="cond2")*(C1:C10="cond3")*(E1:E10))


--
Regards
Frank Kabel
Frankfurt, Germany


mdoyle13 wrote:
I had posted a counting and summing question here a couple
weeks ago and received a great answer and introduction to
the sumproduct function. I'm looking for one more item,
this one extracting the largest number from a range but
only if conditions are met for other ranges.

The help I received before with the sumproduct function
allowed me to count and sum results from cell ranges e1:e10
when conditions were true in ranges a1:a10, b1:b10, c1:c10 and
d1:d10. So if a4, b4, c4 and d4 conditions were true, i was
able to count e4 and sum e4. Same for row 6, etc.

What I'm looking for is a way to identify the largest number
in e1:e10 when the first four conditions are true. Thus, in my
previous example, if e4 was 10 and e6 was 6, I want my
result to be 10, even if e8 [which should be ignored if
there is a false condition in a8, b8, c8 or d8] is 15. I
have tried working MAX or LARGE into some IF functions, but
it would always return the largest number [i.e., 15 from
e8] in the whole range rather than the ranges with true
conditions.

Is what I'm looking for possible? Did this make sense?

Thanks in advance.

 

[Guest]

Frank and all.

Thanks for the help on this problem. After changing the formula in the e
column to place a zero instead of "" for a false condition, I was able to get
the result I was seeking. Hopefully I'll know a little better in the future
what can go in the cells. Thanks to all for the contributions.

Mickey

Hi
yes this is causing this error.

--
Regards
Frank Kabel
Frankfurt, Germany

All of the cells in E have a formula to get a number. But if the
condition is false, it returns "". Perhaps that is causing the error?

Hi
any chance you have a #VALUE error in your data range or are there
text entries in column E?

--
Regards
Frank Kabel
Frankfurt, Germany


mdoyle13 wrote:
Frank, thanks for the tips and help. The problem that has arisen
with this attempt is #VALUE! error message. The error showed up
even when I broke it down to the minimums of d*e. I did save it as
an array, so I don't think format is the problem. It's just a
doozy, it appears.




:

Hi
try the array formula (entered with CTRL+SHIFT+ENTER):
=MAX((B1:B1000="R")*(C1:C1000="H")*(D11000=33)*(E1:E1000))

--
Regards
Frank Kabel
Frankfurt, Germany


mdoyle13 wrote:
Thanks to all. I have tried all of them and keep coming up with
the same result I was getting before, namely it giving me the
largest number every time. Maybe an example of what I'm trying to
get will help.

A B C D E
1 R V 11 3
1 R V 24 7
1 P V 7 22
2 R H 20 -2
2 R H 33 17
2 R H 20 21
2 R H 33 4
2 P H 7 13

What I'm trying to do is get the largest number from E when the
other conditions would be R, H and 33. The result should be 17,
but I'm always getting 22. For R,H and 20, I want the result to
be 21, but it is coming up
22.

Did I mention this would be reading from about 2000 rows? That's
why
I'm looking for a formula to extract the largest for each
condition rather than having to go through each row for multiple
conditions.

Thanks again.

:

Hi Bob
it also does not work for me )
lets exchange a test file <vbg>

--
Regards
Frank Kabel
Frankfurt, Germany


Bob Phillips wrote:
Tom,

It works for me. I only did it because Frank's respomnse didn't
work for me (sic!)

This data

A B C D E
1 text1 text2 5 10
1 text1 text2 5 15
1 text1 text2 6 25

gives me 15.

confused RP


You must have left something out Bob. That doesn't work.

--
Regards,
Tom Ogilvy

message or






=MAX(--(A1:A10=1),--(B1:B10="text1"),--(C1:C10="text2"),--(D110=5)*E1
E10)

still array entered, Ctrl-SHift-Enter.

Change the values to suit.

--

HTH

RP

Hi
try the following array formula (entered with
cTRL+SHIFT+ENTER):

=MAX(IF(A1:A10="cond1")*(B1:B10="cond2")*(C1:C10="cond3"),E1:E10))

or the array formula

=MAX((A1:A10="cond1")*(B1:B10="cond2")*(C1:C10="cond3")*(E1:E10))


--
Regards
Frank Kabel
Frankfurt, Germany


mdoyle13 wrote:
I had posted a counting and summing question here a couple
weeks ago and received a great answer and introduction to
the sumproduct function. I'm looking for one more item,
this one extracting the largest number from a range but
only if conditions are met for other ranges.

The help I received before with the sumproduct function
allowed me to count and sum results from cell ranges e1:e10
when conditions were true in ranges a1:a10, b1:b10, c1:c10
and
d1:d10. So if a4, b4, c4 and d4 conditions were true, i was
able to count e4 and sum e4. Same for row 6, etc.

What I'm looking for is a way to identify the largest number
in e1:e10 when the first four conditions are true. Thus, in
my
previous example, if e4 was 10 and e6 was 6, I want my
result to be 10, even if e8 [which should be ignored if
there is a false condition in a8, b8, c8 or d8] is 15. I
have tried working MAX or LARGE into some IF functions, but
it would always return the largest number [i.e., 15 from
e8] in the whole range rather than the ranges with true
conditions.

Is what I'm looking for possible? Did this make sense?

Thanks in advance.