I have a spread sheet with 5000 lines.
Column A has 1000 different part numbers (P/N)
Column C has MTBF data for the 5000 line.
Column D has “Actual” or “Estimated”
Column E has max MTBF value from “C” for the P/N in “A” {=MAX(IF(A$2:A
$5050=A3,C$2:C$5050))}

What I am now trying to do unsuccessfully is obtain the “Actual” or
“Estimated” in Column F based on the MAX values returned in Column
“E”. Can someone help?

Also, if the MAX values returned for a given part number has both
“Actual” and “Estimated” the default should be “Actual”.

On the face of it, think you could try an index n match,
like this, array-entered*, then copied down:
=INDEX(F$2:F$5050,MATCH(MAX(IF(A$2:A$5050=A3,C$2:C $5050)),IF(A$2:A$5050=A3,C$2:C$5050),0))

*press CTRL+SHIFT+ENTER to confirm the formula

And if you need to include an additional criteria based on what's in col D,
eg: Actual, then try something like this, array-entered:
=INDEX(F$2:F$5050,MATCH(MAX(IF((A$2:A$5050=A3)*(D$ 2:D$5050="Actual"),C$2:C$5050)),IF((A$2:A$5050=A3) *(D$2:D$5050="Actual"),C$2:C$5050),0))

Likewise for "Estimated"
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---
"B~O~B" wrote in message
...
I have a spread sheet with 5000 lines.
Column A has 1000 different part numbers (P/N)
Column C has MTBF data for the 5000 line.
Column D has “Actual” or “Estimated”
Column E has max MTBF value from “C” for the P/N in “A” {=MAX(IF(A$2:A
$5050=A3,C$2:C$5050))}

What I am now trying to do unsuccessfully is obtain the “Actual” or
“Estimated” in Column F based on the MAX values returned in Column
“E”. Can someone help?

Also, if the MAX values returned for a given part number has both
“Actual” and “Estimated” the default should be “Actual”.

B~O~B wrote:
I have a spread sheet with 5000 lines.
Column A has 1000 different part numbers (P/N)
Column C has MTBF data for the 5000 line.
Column D has “Actual” or “Estimated”
Column E has max MTBF value from “C” for the P/N in “A” {=MAX(IF(A$2:A
$5050=A3,C$2:C$5050))}

What I am now trying to do unsuccessfully is obtain the “Actual” or
“Estimated” in Column F based on the MAX values returned in Column
“E”. Can someone help?

Also, if the MAX values returned for a given part number has both
“Actual” and “Estimated” the default should be “Actual”.

This is not complete--I don't have the tie-breaker figured out yet.


First off, I'm using cells E2 and F2, and a range of data in A2:D5001.

E2 is Max MTBF by p/n:
{=MAX(IF($A2=$A$2:$A$5001,$C$2:$C$5001))}

This provides the data row where the Max was found:
{=MATCH(1,($A2=$A$2:$A$5001)*($E2=$C$2:$C$5001),0) }

Rolling that into F2, we get the Actual or Estimated category from D:
{=INDEX($D$2:$D$5001,MATCH(1,($A2=$A$2:$A$5001)*($ E2=$C$2:$C$5001),0),1)}

Now, hmm...

Max wrote:
On the face of it, think you could try an index n match,
like this, array-entered*, then copied down:
=INDEX(F$2:F$5050,MATCH(MAX(IF(A$2:A$5050=A3,C$2:C $5050)),IF(A$2:A$5050=A3,C$2:C$5050),0))

*press CTRL+SHIFT+ENTER to confirm the formula

And if you need to include an additional criteria based on what's in col D,
eg: Actual, then try something like this, array-entered:
=INDEX(F$2:F$5050,MATCH(MAX(IF((A$2:A$5050=A3)*(D$ 2:D$5050="Actual"),C$2:C$5050)),IF((A$2:A$5050=A3) *(D$2:D$5050="Actual"),C$2:C$5050),0))

Likewise for "Estimated"

Aha, following the formula auditor at last I see what I was doing wrong.

For "Actual":
=INDEX(C$2:C$5050,MATCH(MAX(IF((A$2:A$5050=A3)*(D$ 2:D$5050="Actual"),C$2:C$5050)),IF((A$2:A$5050=A3) *(D$2:D$5050="Actual"),C$2:C$5050),0))
^ ^

And, as you say, likewise for "Estimated"

On Aug 19, 9:40*pm, smartin wrote:
Max wrote:
On the face of it, think you could try an index n match,
like this, array-entered*, then copied down:
=INDEX(F$2:F$5050,MATCH(MAX(IF(A$2:A$5050=A3,C$2:C $5050)),IF(A$2:A$5050=A3,*C$2:C$5050),0))

*press CTRL+SHIFT+ENTER to confirm the formula

And if you need to include an additional criteria based on what's in col D,
eg: Actual, then try something like this, array-entered:
=INDEX(F$2:F$5050,MATCH(MAX(IF((A$2:A$5050=A3)*(D$ 2:D$5050="Actual"),C$2:C$*5050)),IF((A$2:A$5050=A3 )*(D$2:D$5050="Actual"),C$2:C$5050),0))

Likewise for "Estimated"

Aha, following the formula auditor at last I see what I was doing wrong.

For "Actual":
=INDEX(C$2:C$5050,MATCH(MAX(IF((A$2:A$5050=A3)*(D$ 2:D$5050="Actual"),C$2:C$*5050)),IF((A$2:A$5050=A3 )*(D$2:D$5050="Actual"),C$2:C$5050),0))
* * * * ^ * ^

And, as you say, likewise for "Estimated"

Max and Smartin,
Thanks you both for all your help. I was able to get it working just
need to make sure the data is sorted in ascending order, but, smartin
I think your “=INDEX(C$2:C$5050,MATCH(MAX(IF((A$2:A$5050=A3)*(D $2:D
$5050="Actual"),C$2:C$5050)),IF((A$2:A$5050=A3)*(D $2:D$5050="Actual"),C
$2:C$5050),0))” will help with that it keeps returning #N/A.

When I have some more time I will look into some more, but, thanks you
again…

Welcome, Bob

.. just need to make sure the data is sorted in ascending order ..

The above shouldn't be necessary since the MATCH is set to look for an exact
match (by virtue of the zero)

And you could also improve the robustness of the matching for col D by using
TRIM, viz. change: (D$2:D$5050="Actual") to: (TRIM(D$2:D$5050)="Actual")
within the expression
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---
"B~O~B" wrote
Max and Smartin,
Thanks you both for all your help. I was able to get it working just
need to make sure the data is sorted in ascending order, but, smartin
I think your “=INDEX(C$2:C$5050,MATCH(MAX(IF((A$2:A$5050=A3)*(D $2:D
$5050="Actual"),C$2:C$5050)),IF((A$2:A$5050=A3)*(D $2:D$5050="Actual"),C
$2:C$5050),0))” will help with that it keeps returning #N/A.

When I have some more time I will look into some more, but, thanks you
again…

B~O~B wrote:
On Aug 19, 9:40 pm, smartin wrote:
For "Actual":
=INDEX(C$2:C$5050,MATCH(MAX(IF((A$2:A$5050=A3)*(D$ 2:D$5050="Actual"),C$2:C$*5050)),IF((A$2:A$5050=A3 )*(D$2:D$5050="Actual"),C$2:C$5050),0))
^ ^

Max and Smartin,
Thanks you both for all your help. I was able to get it working just
need to make sure the data is sorted in ascending order, but, smartin
I think your “=INDEX(C$2:C$5050,MATCH(MAX(IF((A$2:A$5050=A3)*(D $2:D
$5050="Actual"),C$2:C$5050)),IF((A$2:A$5050=A3)*(D $2:D$5050="Actual"),C
$2:C$5050),0))” will help with that it keeps returning #N/A.

When I have some more time I will look into some more, but, thanks you
again…

Glad to know you got things working.

FWIW, the last formula I posted does need to be entered as an array formula.