Max Values
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 |
Max Values
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 |
Max Values
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 |
Max Values
.... 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 |
Max Values
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 |
Max Values
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 |
Max Values
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 - |
All times are GMT +1. The time now is 08:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com