MAX then return value?
 

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!

MAX then return value?
 

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!

MAX then return value?
 

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!

MAX then return value?
 

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!

MAX then return value?
 

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!

MAX then return value?
 

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!

MAX then return value?
 

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!

MAX then return value?
 

Hi Biff, the blank cells are in both columns. The rows are absolute (i.e.
1000) but its updated with data over time (i.e. table shows 423 rows to date
but the remaining blanks will be updated until it reaches 1000).

"T. Valko" wrote:

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!

MAX then return value?
 

OK, try this array formula** for the mode:

=INDEX(B1:B21&"-"&C1:C21,MODE(IF((B1:B21<"")*(C1:C21<""),MATCH(B 1:B21&"-"&C1:C21,B1:B21&"-"&C1:C21,0))))

The count formula will still be the same and needs to reference the cell
that holds the above formula.

** 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
...
Hi Biff, the blank cells are in both columns. The rows are absolute (i.e.
1000) but its updated with data over time (i.e. table shows 423 rows to
date
but the remaining blanks will be updated until it reaches 1000).

"T. Valko" wrote:

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!

MAX then return value?
 

Thanks Biff, it worked.

"T. Valko" wrote:

OK, try this array formula** for the mode:

=INDEX(B1:B21&"-"&C1:C21,MODE(IF((B1:B21<"")*(C1:C21<""),MATCH(B 1:B21&"-"&C1:C21,B1:B21&"-"&C1:C21,0))))

The count formula will still be the same and needs to reference the cell
that holds the above formula.

** 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
...
Hi Biff, the blank cells are in both columns. The rows are absolute (i.e.
1000) but its updated with data over time (i.e. table shows 423 rows to
date
but the remaining blanks will be updated until it reaches 1000).

"T. Valko" wrote:

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!

MAX then return value?
 

You're welcome!

--
Biff
Microsoft Excel MVP


"andrew" wrote in message
...
Thanks Biff, it worked.

"T. Valko" wrote:

OK, try this array formula** for the mode:

=INDEX(B1:B21&"-"&C1:C21,MODE(IF((B1:B21<"")*(C1:C21<""),MATCH(B 1:B21&"-"&C1:C21,B1:B21&"-"&C1:C21,0))))

The count formula will still be the same and needs to reference the cell
that holds the above formula.

** 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
...
Hi Biff, the blank cells are in both columns. The rows are absolute
(i.e.
1000) but its updated with data over time (i.e. table shows 423 rows to
date
but the remaining blanks will be updated until it reaches 1000).

"T. Valko" wrote:

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!