How do you extract max values from a row of data.

Ex.
product dist a dist b dist c dist d
a 5 3 9 1
b 8 7 1 3
c 1 4 6 9

to display like
Product most in stock
a dist c
b dist a
c dist d

Any help would be greatly appreciated

I set up your data as in your example and then inserted a new column B
with "Most in Stock" as the header in B1. Then put this formula in B2:

=INDEX(C$1:F$1,MATCH(MAX(C2:F2),C2:F2,0))

and copied down.

Hope this helps.

Pete

On Oct 10, 10:06 am, THFish wrote:
How do you extract max values from a row of data.

Ex.
product dist a dist b dist c dist d
a 5 3 9 1
b 8 7 1 3
c 1 4 6 9

to display like
Product most in stock
a dist c
b dist a
c dist d

Any help would be greatly appreciated

Alternatively, if you want the layout as in your example, then put the
headings for the lower table in A10:B10 and a, b, c in A11:A13 and
this formula in B11:

=INDEX(B$1:E$1,MATCH(MAX(B2:E2),B2:E2,0))

and copy this down into B12:B13.

Hope this helps.

Pete

On Oct 10, 10:06 am, THFish wrote:
How do you extract max values from a row of data.

Ex.
product dist a dist b dist c dist d
a 5 3 9 1
b 8 7 1 3
c 1 4 6 9

to display like
Product most in stock
a dist c
b dist a
c dist d

Any help would be greatly appreciated

.... and then copy that formula down

"THFish" wrote:

How do you extract max values from a row of data.

Ex.
product dist a dist b dist c dist d
a 5 3 9 1
b 8 7 1 3
c 1 4 6 9

to display like
Product most in stock
a dist c
b dist a
c dist d

Any help would be greatly appreciated

I'm assuming here that you have the value 'product' in cell A1 her...

Type the following in F2:

=if(max($b2:$e2)=b2,b$1,if(max($b2:$e2)=c2,c$1,if( max($b2:$e2)=d2,d$1,$e1)))

"THFish" wrote:

How do you extract max values from a row of data.

Ex.
product dist a dist b dist c dist d
a 5 3 9 1
b 8 7 1 3
c 1 4 6 9

to display like
Product most in stock
a dist c
b dist a
c dist d

Any help would be greatly appreciated

that worked perfectly. thank you

"Pete_UK" wrote:

I set up your data as in your example and then inserted a new column B
with "Most in Stock" as the header in B1. Then put this formula in B2:

=INDEX(C$1:F$1,MATCH(MAX(C2:F2),C2:F2,0))

and copied down.

Hope this helps.

Pete

On Oct 10, 10:06 am, THFish wrote:
How do you extract max values from a row of data.

Ex.
product dist a dist b dist c dist d
a 5 3 9 1
b 8 7 1 3
c 1 4 6 9

to display like
Product most in stock
a dist c
b dist a
c dist d

Any help would be greatly appreciated

Thanks for feeding back.

Pete

On Oct 10, 11:04 am, THFish wrote:
that worked perfectly. thank you



"Pete_UK" wrote:
I set up your data as in your example and then inserted a new column B
with "Most in Stock" as the header in B1. Then put this formula in B2:

=INDEX(C$1:F$1,MATCH(MAX(C2:F2),C2:F2,0))

and copied down.

Hope this helps.

Pete

On Oct 10, 10:06 am, THFish wrote:
How do you extract max values from a row of data.

Ex.
product dist a dist b dist c dist d
a 5 3 9 1
b 8 7 1 3
c 1 4 6 9

to display like
Product most in stock
a dist c
b dist a
c dist d

Any help would be greatly appreciated- Hide quoted text -

- Show quoted text -