MAX then return value?

[andrew]

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!

 

[T. Valko]

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)

 

[andrew]

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]

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))

 

[andrew]

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?

 

[andrew]

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]

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.

 

[andrew]

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]

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

=INDEX(B1:B21&"-"&C1:C21,MODE(IF((B1:B21<>"")*(C1:C21<>""),MATCH(B1: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)

 

[andrew]

Thanks Biff, it worked.

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

=INDEX(B1:B21&"-"&C1:C21,MODE(IF((B1:B21<>"")*(C1:C21<>""),MATCH(B1: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)

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Thanks Biff, it worked.