Here's my table:

A B C D
Nuts 21 8 Pins
Bolts 11 13 Screws
Screws 12 11 Nuts
Pins 6 14 Bolts
Caps 4 19 Nuts

I'm using =MAX(B1:B5)+(C1:C5) to get the max value returned on cell F1. What
i need is to be able to display the following:
cell F2 to show the value from column B of the MAX combination
cell F3 to show the value from column C of the MAX combination

From example above, MAX should return 29 from row 1. How do i then display
B1 and C1 (in different cells) that adds up to the MAX value? Thanks in
advance for any assistance!

Try this array formula** :

=INDEX(B$1:C$5,MATCH(MAX(B$1:B$5+C$1:C$5),B$1:B$5+ C$1:C$5,0),ROWS(F$2:F2))

Copy down to F3

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"andrew" wrote in message
...
Here's my table:

A B C D
Nuts 21 8 Pins
Bolts 11 13 Screws
Screws 12 11 Nuts
Pins 6 14 Bolts
Caps 4 19 Nuts

I'm using =MAX(B1:B5)+(C1:C5) to get the max value returned on cell F1.
What
i need is to be able to display the following:
cell F2 to show the value from column B of the MAX combination
cell F3 to show the value from column C of the MAX combination

From example above, MAX should return 29 from row 1. How do i then display
B1 and C1 (in different cells) that adds up to the MAX value? Thanks in
advance for any assistance!

Thanks. Another question on the same table, but expanded a bit:
Nuts 21 8 Pins
Bolts 11 13 Screws
Screws 12 11 Nuts
Pins 6 14 Bolts
Caps 4 19 Nuts
Screws 12 11 Nuts
Pins 8 12 Bolts
Caps 4 19 Screws
Bolts 12 11 Nuts
Screws 12 11 Pins

How do i use check for column B and C for the most frequent occurence of
pairing? Table above shows 12and11 occurring 4 times. I want to:
a) show the total of the most frequent occurence (which is 4 per above);
b) show which pairing that resulted the above (i.e. 12 and 11) within a cell.

"T. Valko" wrote:

Try this array formula** :

=INDEX(B$1:C$5,MATCH(MAX(B$1:B$5+C$1:C$5),B$1:B$5+ C$1:C$5,0),ROWS(F$2:F2))

Copy down to F3

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"andrew" wrote in message
...
Here's my table:

A B C D
Nuts 21 8 Pins
Bolts 11 13 Screws
Screws 12 11 Nuts
Pins 6 14 Bolts
Caps 4 19 Nuts

I'm using =MAX(B1:B5)+(C1:C5) to get the max value returned on cell F1.
What
i need is to be able to display the following:
cell F2 to show the value from column B of the MAX combination
cell F3 to show the value from column C of the MAX combination

From example above, MAX should return 29 from row 1. How do i then display
B1 and C1 (in different cells) that adds up to the MAX value? Thanks in
advance for any assistance!

b) show which pairing that resulted the above (i.e. 12 and 11) within a
cell.

Try this array formula** :

=INDEX(B1:B10&"-"&C1:C10,MODE(MATCH(B1:B10&"-"&C1:C10,B1:B10&"-"&C1:C10,0)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

This will return the result in the form 12-11. Note that there *must* be at
least 2 instances of matching pairs for this to work. Otherwise the formula
will return an error.

a) show the total of the most frequent occurence (which is 4 per above)

This formula is based on the result of the above formula. Assume the above
formula is entered in cell E1.

=SUMPRODUCT(--(B1:B10&"-"&C1:C10=E1))


--
Biff
Microsoft Excel MVP


"andrew" wrote in message
...
Thanks. Another question on the same table, but expanded a bit:
Nuts 21 8 Pins
Bolts 11 13 Screws
Screws 12 11 Nuts
Pins 6 14 Bolts
Caps 4 19 Nuts
Screws 12 11 Nuts
Pins 8 12 Bolts
Caps 4 19 Screws
Bolts 12 11 Nuts
Screws 12 11 Pins

How do i use check for column B and C for the most frequent occurence of
pairing? Table above shows 12and11 occurring 4 times. I want to:
a) show the total of the most frequent occurence (which is 4 per above);
b) show which pairing that resulted the above (i.e. 12 and 11) within a
cell.

"T. Valko" wrote:

Try this array formula** :

=INDEX(B$1:C$5,MATCH(MAX(B$1:B$5+C$1:C$5),B$1:B$5+ C$1:C$5,0),ROWS(F$2:F2))

Copy down to F3

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"andrew" wrote in message
...
Here's my table:

A B C D
Nuts 21 8 Pins
Bolts 11 13 Screws
Screws 12 11 Nuts
Pins 6 14 Bolts
Caps 4 19 Nuts

I'm using =MAX(B1:B5)+(C1:C5) to get the max value returned on cell F1.
What
i need is to be able to display the following:
cell F2 to show the value from column B of the MAX combination
cell F3 to show the value from column C of the MAX combination

From example above, MAX should return 29 from row 1. How do i then
display
B1 and C1 (in different cells) that adds up to the MAX value? Thanks in
advance for any assistance!

Hi Biff, doesn't seem to be working. The rows are pretty long (up to 423) in
my actual worksheet, and i noticed that some cells are blank. Does this pose
a problem?

"T. Valko" wrote:

b) show which pairing that resulted the above (i.e. 12 and 11) within a
cell.

Try this array formula** :

=INDEX(B1:B10&"-"&C1:C10,MODE(MATCH(B1:B10&"-"&C1:C10,B1:B10&"-"&C1:C10,0)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

This will return the result in the form 12-11. Note that there *must* be at
least 2 instances of matching pairs for this to work. Otherwise the formula
will return an error.

a) show the total of the most frequent occurence (which is 4 per above)

This formula is based on the result of the above formula. Assume the above
formula is entered in cell E1.

=SUMPRODUCT(--(B1:B10&"-"&C1:C10=E1))


--
Biff
Microsoft Excel MVP


"andrew" wrote in message
...
Thanks. Another question on the same table, but expanded a bit:
Nuts 21 8 Pins
Bolts 11 13 Screws
Screws 12 11 Nuts
Pins 6 14 Bolts
Caps 4 19 Nuts
Screws 12 11 Nuts
Pins 8 12 Bolts
Caps 4 19 Screws
Bolts 12 11 Nuts
Screws 12 11 Pins

How do i use check for column B and C for the most frequent occurence of
pairing? Table above shows 12and11 occurring 4 times. I want to:
a) show the total of the most frequent occurence (which is 4 per above);
b) show which pairing that resulted the above (i.e. 12 and 11) within a
cell.

"T. Valko" wrote:

Try this array formula** :

=INDEX(B$1:C$5,MATCH(MAX(B$1:B$5+C$1:C$5),B$1:B$5+ C$1:C$5,0),ROWS(F$2:F2))

Copy down to F3

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"andrew" wrote in message
...
Here's my table:

A B C D
Nuts 21 8 Pins
Bolts 11 13 Screws
Screws 12 11 Nuts
Pins 6 14 Bolts
Caps 4 19 Nuts

I'm using =MAX(B1:B5)+(C1:C5) to get the max value returned on cell F1.
What
i need is to be able to display the following:
cell F2 to show the value from column B of the MAX combination
cell F3 to show the value from column C of the MAX combination

From example above, MAX should return 29 from row 1. How do i then
display
B1 and C1 (in different cells) that adds up to the MAX value? Thanks in
advance for any assistance!

By the way, do you think this variation will work?

=IF(ISERROR(MODE(B11:B20)&" : "&MODE(C11:C20)),"--",MODE(B11:B20)&" -
"&MODE(C11:C20))


"T. Valko" wrote:

b) show which pairing that resulted the above (i.e. 12 and 11) within a
cell.

Try this array formula** :

=INDEX(B1:B10&"-"&C1:C10,MODE(MATCH(B1:B10&"-"&C1:C10,B1:B10&"-"&C1:C10,0)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

This will return the result in the form 12-11. Note that there *must* be at
least 2 instances of matching pairs for this to work. Otherwise the formula
will return an error.

a) show the total of the most frequent occurence (which is 4 per above)

This formula is based on the result of the above formula. Assume the above
formula is entered in cell E1.

=SUMPRODUCT(--(B1:B10&"-"&C1:C10=E1))


--
Biff
Microsoft Excel MVP


"andrew" wrote in message
...
Thanks. Another question on the same table, but expanded a bit:
Nuts 21 8 Pins
Bolts 11 13 Screws
Screws 12 11 Nuts
Pins 6 14 Bolts
Caps 4 19 Nuts
Screws 12 11 Nuts
Pins 8 12 Bolts
Caps 4 19 Screws
Bolts 12 11 Nuts
Screws 12 11 Pins

How do i use check for column B and C for the most frequent occurence of
pairing? Table above shows 12and11 occurring 4 times. I want to:
a) show the total of the most frequent occurence (which is 4 per above);
b) show which pairing that resulted the above (i.e. 12 and 11) within a
cell.

"T. Valko" wrote:

Try this array formula** :

=INDEX(B$1:C$5,MATCH(MAX(B$1:B$5+C$1:C$5),B$1:B$5+ C$1:C$5,0),ROWS(F$2:F2))

Copy down to F3

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"andrew" wrote in message
...
Here's my table:

A B C D
Nuts 21 8 Pins
Bolts 11 13 Screws
Screws 12 11 Nuts
Pins 6 14 Bolts
Caps 4 19 Nuts

I'm using =MAX(B1:B5)+(C1:C5) to get the max value returned on cell F1.
What
i need is to be able to display the following:
cell F2 to show the value from column B of the MAX combination
cell F3 to show the value from column C of the MAX combination

From example above, MAX should return 29 from row 1. How do i then
display
B1 and C1 (in different cells) that adds up to the MAX value? Thanks in
advance for any assistance!

i noticed that some cells are blank. Does this pose a problem?

Yes. Where are the blank cells? In both columns? How should blank cells be
handled?

do you think this variation will work?

No. The mode of the individual columns won't necessarily be the mode of
pairs from both columns.

I'll have to get back to this tomorrow. I'm getting ready to quit for the
day. Maybe someone else will reply in the meantime.

--
Biff
Microsoft Excel MVP


"andrew" wrote in message
...
By the way, do you think this variation will work?

=IF(ISERROR(MODE(B11:B20)&" : "&MODE(C11:C20)),"--",MODE(B11:B20)&" -
"&MODE(C11:C20))


"T. Valko" wrote:

b) show which pairing that resulted the above (i.e. 12 and 11) within a
cell.

Try this array formula** :

=INDEX(B1:B10&"-"&C1:C10,MODE(MATCH(B1:B10&"-"&C1:C10,B1:B10&"-"&C1:C10,0)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

This will return the result in the form 12-11. Note that there *must* be
at
least 2 instances of matching pairs for this to work. Otherwise the
formula
will return an error.

a) show the total of the most frequent occurence (which is 4 per above)

This formula is based on the result of the above formula. Assume the
above
formula is entered in cell E1.

=SUMPRODUCT(--(B1:B10&"-"&C1:C10=E1))


--
Biff
Microsoft Excel MVP


"andrew" wrote in message
...
Thanks. Another question on the same table, but expanded a bit:
Nuts 21 8 Pins
Bolts 11 13 Screws
Screws 12 11 Nuts
Pins 6 14 Bolts
Caps 4 19 Nuts
Screws 12 11 Nuts
Pins 8 12 Bolts
Caps 4 19 Screws
Bolts 12 11 Nuts
Screws 12 11 Pins

How do i use check for column B and C for the most frequent occurence
of
pairing? Table above shows 12and11 occurring 4 times. I want to:
a) show the total of the most frequent occurence (which is 4 per
above);
b) show which pairing that resulted the above (i.e. 12 and 11) within a
cell.

"T. Valko" wrote:

Try this array formula** :

=INDEX(B$1:C$5,MATCH(MAX(B$1:B$5+C$1:C$5),B$1:B$5+ C$1:C$5,0),ROWS(F$2:F2))

Copy down to F3

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"andrew" wrote in message
...
Here's my table:

A B C D
Nuts 21 8 Pins
Bolts 11 13 Screws
Screws 12 11 Nuts
Pins 6 14 Bolts
Caps 4 19 Nuts

I'm using =MAX(B1:B5)+(C1:C5) to get the max value returned on cell
F1.
What
i need is to be able to display the following:
cell F2 to show the value from column B of the MAX combination
cell F3 to show the value from column C of the MAX combination

From example above, MAX should return 29 from row 1. How do i then
display
B1 and C1 (in different cells) that adds up to the MAX value? Thanks
in
advance for any assistance!