|
Find the largest number
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. |
Find the largest number
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="co nd3")*(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. |
Find the largest number
or
=MAX(--(A1:A10=1),--(B1:B10="text1"),--(C1:C10="text2"),--(D1:D10=5)*E1:E10) still array entered, Ctrl-SHift-Enter. Change the values to suit. -- HTH RP "Frank Kabel" wrote in message ... 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="co nd3")*(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. |
Find the largest number
You must have left something out Bob. That doesn't work.
-- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... or =MAX(--(A1:A10=1),--(B1:B10="text1"),--(C1:C10="text2"),--(D1:D10=5)*E1:E10) still array entered, Ctrl-SHift-Enter. Change the values to suit. -- HTH RP "Frank Kabel" wrote in message ... 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="co nd3")*(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. |
Find the largest number
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 "Tom Ogilvy" wrote in message ... You must have left something out Bob. That doesn't work. -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... or =MAX(--(A1:A10=1),--(B1:B10="text1"),--(C1:C10="text2"),--(D1:D10=5)*E1:E10) still array entered, Ctrl-SHift-Enter. Change the values to suit. -- HTH RP "Frank Kabel" wrote in message ... 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="co nd3")*(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. |
Find the largest number
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 "Tom Ogilvy" wrote in message ... You must have left something out Bob. That doesn't work. -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... or =MAX(--(A1:A10=1),--(B1:B10="text1"),--(C1:C10="text2"),--(D1:D10=5)*E1 :E10) still array entered, Ctrl-SHift-Enter. Change the values to suit. -- HTH RP "Frank Kabel" wrote in message ... 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="co nd3")*(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. |
Find the largest number
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 "Bob Phillips" wrote in message ... 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 "Tom Ogilvy" wrote in message ... You must have left something out Bob. That doesn't work. -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... or =MAX(--(A1:A10=1),--(B1:B10="text1"),--(C1:C10="text2"),--(D1:D10=5)*E1:E10) still array entered, Ctrl-SHift-Enter. Change the values to suit. -- HTH RP "Frank Kabel" wrote in message ... 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="co nd3")*(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. |
Find the largest number
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. "Frank Kabel" wrote: 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 "Tom Ogilvy" wrote in message ... You must have left something out Bob. That doesn't work. -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... or =MAX(--(A1:A10=1),--(B1:B10="text1"),--(C1:C10="text2"),--(D1:D10=5)*E1 :E10) still array entered, Ctrl-SHift-Enter. Change the values to suit. -- HTH RP "Frank Kabel" wrote in message ... 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="co nd3")*(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. |
Find the largest number
Hi
try the array formula (entered with CTRL+SHIFT+ENTER): =MAX((B1:B1000="R")*(C1:C1000="H")*(D1:D1000=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. "Frank Kabel" wrote: 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 "Tom Ogilvy" wrote in message ... You must have left something out Bob. That doesn't work. -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... or =MAX(--(A1:A10=1),--(B1:B10="text1"),--(C1:C10="text2"),--(D1:D10=5)*E1 E10) still array entered, Ctrl-SHift-Enter. Change the values to suit. -- HTH RP "Frank Kabel" wrote in message ... 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="co nd3")*(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. |
Find the largest number
I think he omitted the multiplication, i.e. should be
=MAX((A1:A10=1)*(B1:B10="text1")*(C1:C10="text2")* (D1:D10=5)*E1:E10)) On Sun, 3 Oct 2004 10:48:17 -0400, "Tom Ogilvy" wrote: =MAX(--(A1:A10=1),--(B1:B10="text1"),--(C1:C10="text2"),--(D1:D10=5)*E1:E10) |
Find the largest number
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. "Frank Kabel" wrote: Hi try the array formula (entered with CTRL+SHIFT+ENTER): =MAX((B1:B1000="R")*(C1:C1000="H")*(D1:D1000=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. "Frank Kabel" wrote: 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 "Tom Ogilvy" wrote in message ... You must have left something out Bob. That doesn't work. -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... or =MAX(--(A1:A10=1),--(B1:B10="text1"),--(C1:C10="text2"),--(D1:D10=5)*E1 E10) still array entered, Ctrl-SHift-Enter. Change the values to suit. -- HTH RP "Frank Kabel" wrote in message ... 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="co nd3")*(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. |
Find the largest number
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. "Frank Kabel" wrote: Hi try the array formula (entered with CTRL+SHIFT+ENTER): =MAX((B1:B1000="R")*(C1:C1000="H")*(D1:D1000=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. "Frank Kabel" wrote: 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 "Tom Ogilvy" wrote in message ... You must have left something out Bob. That doesn't work. -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... or =MAX(--(A1:A10=1),--(B1:B10="text1"),--(C1:C10="text2"),--(D1:D10=5)*E1 E10) still array entered, Ctrl-SHift-Enter. Change the values to suit. -- HTH RP "Frank Kabel" wrote in message ... 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="co nd3")*(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. |
Find the largest number
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? "Frank Kabel" wrote: 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. "Frank Kabel" wrote: Hi try the array formula (entered with CTRL+SHIFT+ENTER): =MAX((B1:B1000="R")*(C1:C1000="H")*(D1:D1000=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. "Frank Kabel" wrote: 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 "Tom Ogilvy" wrote in message ... You must have left something out Bob. That doesn't work. -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... or =MAX(--(A1:A10=1),--(B1:B10="text1"),--(C1:C10="text2"),--(D1:D10=5)*E1 E10) still array entered, Ctrl-SHift-Enter. Change the values to suit. -- HTH RP "Frank Kabel" wrote in message ... 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="co nd3")*(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. |
Find the largest number
Hi
yes this is causing this error. -- Regards Frank Kabel Frankfurt, Germany mdoyle13 wrote: 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? "Frank Kabel" wrote: 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. "Frank Kabel" wrote: Hi try the array formula (entered with CTRL+SHIFT+ENTER): =MAX((B1:B1000="R")*(C1:C1000="H")*(D1:D1000=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. "Frank Kabel" wrote: 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 "Tom Ogilvy" wrote in message ... You must have left something out Bob. That doesn't work. -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... or =MAX(--(A1:A10=1),--(B1:B10="text1"),--(C1:C10="text2"),--(D1:D10=5)*E1 E10) still array entered, Ctrl-SHift-Enter. Change the values to suit. -- HTH RP "Frank Kabel" wrote in message ... 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="co nd3")*(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. |
Find the largest number
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 "Frank Kabel" wrote: Hi yes this is causing this error. -- Regards Frank Kabel Frankfurt, Germany mdoyle13 wrote: 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? "Frank Kabel" wrote: 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. "Frank Kabel" wrote: Hi try the array formula (entered with CTRL+SHIFT+ENTER): =MAX((B1:B1000="R")*(C1:C1000="H")*(D1:D1000=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. "Frank Kabel" wrote: 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 "Tom Ogilvy" wrote in message ... You must have left something out Bob. That doesn't work. -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... or =MAX(--(A1:A10=1),--(B1:B10="text1"),--(C1:C10="text2"),--(D1:D10=5)*E1 E10) still array entered, Ctrl-SHift-Enter. Change the values to suit. -- HTH RP "Frank Kabel" wrote in message ... 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="co nd3")*(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. |
| All times are GMT +1. The time now is 01:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com