|
Complicated vlookup/min-max query...please help
I have the following set of data:
shop1 shop2 shop3 shop4 shop5 0.00015 0.23 0.09 0.1 0.69 vod 0.5 0.00005 0.999 0.26 0.95 tesco 0.93 1.56 0.94 2.5 0.32 c&a 0.0003 0.73 5.69 0.47 0.00001 dixons I want to baisically form a summary page that shows the highest and lowest values and then tells me what shop and what company the figure is from. I have succesfully done this so far for the min and max values: Company Shop max value dixons shop3 min value dixons shop5 using the following formulas: Max Value Company =INDEX(F2:F5,MAX((A2:E5=MAX(A2:E5))*ROW(A2:E5)-MIN(ROW(A2:E5))+1)) Max Value Shop Number =INDEX(A1:E1,MAX((A1:E5=MAX(A1:E5))*COLUMN(A1:E5)-MIN(COLUMN(A1:E5))+1)) Min Value Company =INDEX(F2:F5,MIN(IF(A2:E5=MIN(A2:E5),ROW(A2:E5)-MIN(ROW(A2:E5))+1))) Min Value Shop Number =INDEX(A1:E1,MIN(IF(A1:E5=MIN(A1:E5),COLUMN(A1:E5)-MIN(COLUMN(A1:E5))+1))) And these are working fine. Thing is i want the top five max values, and top ten min values and their corresponding shop numbers....does anyone have any idea how to do this? I tried using the B59=LARGE($B$59:$Y$82,10) and B59<=SMALL($B$59:$Y$82,10) formulas but no luck at all.....would really really appreciate someones thoughts.....thank you so much anyone - i know this is a complicated query... Sam |
Complicated vlookup/min-max query...please help
Sam,
These formula look familiar, I think I did these a couple of days ago!! Anyway, you want the largest 10 shops and companies:- Largest 10 shops array enter (see below) and drag down 9 rows. =INDEX($A$1:$E$1,MAX(($A$1:$E$5=LARGE($A$1:$E$5,RO W(A1)))*COLUMN($A$1:$E$5)-MIN(COLUMN($A$1:$E$5))+1)) Largest 10 Companies array enter and drag down 9 rows =INDEX($F$2:$F$5,MAX(($A$2:$E$5=LARGE($A$2:$E$5,RO W(A1)))*ROW($A$2:$E$5)-MIN(ROW($A$2:$E$5))+1)) Having done these 2 I think you should be able to fathom out how to do the MIN 10 but if you struggle then post back. This is an array formula which must be entered with CTRL+Shift+Enter and NOT 'just enter. If you do it correctly then Excel will put curly brackets around 'the formula{}. You can't type these yourself. If you Edit the ranges 'then you must re-enter as An array Mike "SAM" wrote: I have the following set of data: shop1 shop2 shop3 shop4 shop5 0.00015 0.23 0.09 0.1 0.69 vod 0.5 0.00005 0.999 0.26 0.95 tesco 0.93 1.56 0.94 2.5 0.32 c&a 0.0003 0.73 5.69 0.47 0.00001 dixons I want to baisically form a summary page that shows the highest and lowest values and then tells me what shop and what company the figure is from. I have succesfully done this so far for the min and max values: Company Shop max value dixons shop3 min value dixons shop5 using the following formulas: Max Value Company =INDEX(F2:F5,MAX((A2:E5=MAX(A2:E5))*ROW(A2:E5)-MIN(ROW(A2:E5))+1)) Max Value Shop Number =INDEX(A1:E1,MAX((A1:E5=MAX(A1:E5))*COLUMN(A1:E5)-MIN(COLUMN(A1:E5))+1)) Min Value Company =INDEX(F2:F5,MIN(IF(A2:E5=MIN(A2:E5),ROW(A2:E5)-MIN(ROW(A2:E5))+1))) Min Value Shop Number =INDEX(A1:E1,MIN(IF(A1:E5=MIN(A1:E5),COLUMN(A1:E5)-MIN(COLUMN(A1:E5))+1))) And these are working fine. Thing is i want the top five max values, and top ten min values and their corresponding shop numbers....does anyone have any idea how to do this? I tried using the B59=LARGE($B$59:$Y$82,10) and B59<=SMALL($B$59:$Y$82,10) formulas but no luck at all.....would really really appreciate someones thoughts.....thank you so much anyone - i know this is a complicated query... Sam |
Complicated vlookup/min-max query...please help
mike...you are a legend.
"Mike H" wrote: Sam, These formula look familiar, I think I did these a couple of days ago!! Anyway, you want the largest 10 shops and companies:- Largest 10 shops array enter (see below) and drag down 9 rows. =INDEX($A$1:$E$1,MAX(($A$1:$E$5=LARGE($A$1:$E$5,RO W(A1)))*COLUMN($A$1:$E$5)-MIN(COLUMN($A$1:$E$5))+1)) Largest 10 Companies array enter and drag down 9 rows =INDEX($F$2:$F$5,MAX(($A$2:$E$5=LARGE($A$2:$E$5,RO W(A1)))*ROW($A$2:$E$5)-MIN(ROW($A$2:$E$5))+1)) Having done these 2 I think you should be able to fathom out how to do the MIN 10 but if you struggle then post back. This is an array formula which must be entered with CTRL+Shift+Enter and NOT 'just enter. If you do it correctly then Excel will put curly brackets around 'the formula{}. You can't type these yourself. If you Edit the ranges 'then you must re-enter as An array Mike "SAM" wrote: I have the following set of data: shop1 shop2 shop3 shop4 shop5 0.00015 0.23 0.09 0.1 0.69 vod 0.5 0.00005 0.999 0.26 0.95 tesco 0.93 1.56 0.94 2.5 0.32 c&a 0.0003 0.73 5.69 0.47 0.00001 dixons I want to baisically form a summary page that shows the highest and lowest values and then tells me what shop and what company the figure is from. I have succesfully done this so far for the min and max values: Company Shop max value dixons shop3 min value dixons shop5 using the following formulas: Max Value Company =INDEX(F2:F5,MAX((A2:E5=MAX(A2:E5))*ROW(A2:E5)-MIN(ROW(A2:E5))+1)) Max Value Shop Number =INDEX(A1:E1,MAX((A1:E5=MAX(A1:E5))*COLUMN(A1:E5)-MIN(COLUMN(A1:E5))+1)) Min Value Company =INDEX(F2:F5,MIN(IF(A2:E5=MIN(A2:E5),ROW(A2:E5)-MIN(ROW(A2:E5))+1))) Min Value Shop Number =INDEX(A1:E1,MIN(IF(A1:E5=MIN(A1:E5),COLUMN(A1:E5)-MIN(COLUMN(A1:E5))+1))) And these are working fine. Thing is i want the top five max values, and top ten min values and their corresponding shop numbers....does anyone have any idea how to do this? I tried using the B59=LARGE($B$59:$Y$82,10) and B59<=SMALL($B$59:$Y$82,10) formulas but no luck at all.....would really really appreciate someones thoughts.....thank you so much anyone - i know this is a complicated query... Sam |
Complicated vlookup/min-max query...please help
Hi,
First, and unrelated, I simplified and made your formulas consistent: =INDEX(F1:F5,MAX((A2:E5=MAX(A2:E5))*ROW(A2:E5))) =INDEX(A1:E1,MAX((A1:E5=MAX(A1:E5))*COLUMN(A1:E5)) ) =INDEX(F1:F5,MAX((MIN(A2:E5)=A2:E5)*ROW(A2:E5))) =INDEX(A1:E1,MAX((A1:E5=MIN(A1:E5))*COLUMN(A1:E5)) ) Notice I expanded the Index range to F1. Second, there will be a problem if there are two items with the same max or min Third the basic idea for doing top or bottom 5 or 10 would look like this: =INDEX(F$1:F$5,MAX((LARGE(A$2:E$5,ROW(A1))=A$2:E$5 )*ROW(A$2:E$5))) You can copy this down for as many rows as you need. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "SAM" wrote: I have the following set of data: shop1 shop2 shop3 shop4 shop5 0.00015 0.23 0.09 0.1 0.69 vod 0.5 0.00005 0.999 0.26 0.95 tesco 0.93 1.56 0.94 2.5 0.32 c&a 0.0003 0.73 5.69 0.47 0.00001 dixons I want to baisically form a summary page that shows the highest and lowest values and then tells me what shop and what company the figure is from. I have succesfully done this so far for the min and max values: Company Shop max value dixons shop3 min value dixons shop5 using the following formulas: Max Value Company =INDEX(F2:F5,MAX((A2:E5=MAX(A2:E5))*ROW(A2:E5)-MIN(ROW(A2:E5))+1)) Max Value Shop Number =INDEX(A1:E1,MAX((A1:E5=MAX(A1:E5))*COLUMN(A1:E5)-MIN(COLUMN(A1:E5))+1)) Min Value Company =INDEX(F2:F5,MIN(IF(A2:E5=MIN(A2:E5),ROW(A2:E5)-MIN(ROW(A2:E5))+1))) Min Value Shop Number =INDEX(A1:E1,MIN(IF(A1:E5=MIN(A1:E5),COLUMN(A1:E5)-MIN(COLUMN(A1:E5))+1))) And these are working fine. Thing is i want the top five max values, and top ten min values and their corresponding shop numbers....does anyone have any idea how to do this? I tried using the B59=LARGE($B$59:$Y$82,10) and B59<=SMALL($B$59:$Y$82,10) formulas but no luck at all.....would really really appreciate someones thoughts.....thank you so much anyone - i know this is a complicated query... Sam |
Complicated vlookup/min-max query...please help
For Max shop # and value,
Enter this *array* formula in say H2 for shop #: =INDEX(A$1:E$1,MAX(IF(A$2:E$5=LARGE(A$2:E$5,ROWS($ 1:1)),COLUMN(A:E)))) And this formula right next to it in I2: =LARGE(A$2:E$5,ROWS($1:1)) *After* the CSE entry of H2, select both H2 and I2 and copy down that 2 cell selection as needed. For Min shop # and value, Enter this *array* formula in say J2 for shop #: =INDEX(A$1:E$1,MAX(IF(A$2:E$5=SMALL(A$2:E$5,ROWS($ 1:1)),COLUMN(A:E)))) And this formula right next to it in K2: =SMALL(A$2:E$5,ROWS($1:1)) *After* the CSE entry of J2, select both J2 and K2 and copy down that 2 cell selection as needed. -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, CSE *must* be used when revising the formula. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "SAM" wrote in message ... I have the following set of data: shop1 shop2 shop3 shop4 shop5 0.00015 0.23 0.09 0.1 0.69 vod 0.5 0.00005 0.999 0.26 0.95 tesco 0.93 1.56 0.94 2.5 0.32 c&a 0.0003 0.73 5.69 0.47 0.00001 dixons I want to baisically form a summary page that shows the highest and lowest values and then tells me what shop and what company the figure is from. I have succesfully done this so far for the min and max values: Company Shop max value dixons shop3 min value dixons shop5 using the following formulas: Max Value Company =INDEX(F2:F5,MAX((A2:E5=MAX(A2:E5))*ROW(A2:E5)-MIN(ROW(A2:E5))+1)) Max Value Shop Number =INDEX(A1:E1,MAX((A1:E5=MAX(A1:E5))*COLUMN(A1:E5)-MIN(COLUMN(A1:E5))+1)) Min Value Company =INDEX(F2:F5,MIN(IF(A2:E5=MIN(A2:E5),ROW(A2:E5)-MIN(ROW(A2:E5))+1))) Min Value Shop Number =INDEX(A1:E1,MIN(IF(A1:E5=MIN(A1:E5),COLUMN(A1:E5)-MIN(COLUMN(A1:E5))+1))) And these are working fine. Thing is i want the top five max values, and top ten min values and their corresponding shop numbers....does anyone have any idea how to do this? I tried using the B59=LARGE($B$59:$Y$82,10) and B59<=SMALL($B$59:$Y$82,10) formulas but no luck at all.....would really really appreciate someones thoughts.....thank you so much anyone - i know this is a complicated query... Sam |
Complicated vlookup/min-max query...please help
Hi again,
I see they would be even more consistant if I reversed two of the arguments in the 3rd formula: =INDEX(F1:F5,MAX((A2:E5=MAX(A2:E5))*ROW(A2:E5))) =INDEX(A1:E1,MAX((A1:E5=MAX(A1:E5))*COLUMN(A1:E5)) ) =INDEX(F1:F5,MAX((A2:E5=MIN(A2:E5))*ROW(A2:E5))) =INDEX(A1:E1,MAX((A1:E5=MIN(A1:E5))*COLUMN(A1:E5)) ) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "SAM" wrote: I have the following set of data: shop1 shop2 shop3 shop4 shop5 0.00015 0.23 0.09 0.1 0.69 vod 0.5 0.00005 0.999 0.26 0.95 tesco 0.93 1.56 0.94 2.5 0.32 c&a 0.0003 0.73 5.69 0.47 0.00001 dixons I want to baisically form a summary page that shows the highest and lowest values and then tells me what shop and what company the figure is from. I have succesfully done this so far for the min and max values: Company Shop max value dixons shop3 min value dixons shop5 using the following formulas: Max Value Company =INDEX(F2:F5,MAX((A2:E5=MAX(A2:E5))*ROW(A2:E5)-MIN(ROW(A2:E5))+1)) Max Value Shop Number =INDEX(A1:E1,MAX((A1:E5=MAX(A1:E5))*COLUMN(A1:E5)-MIN(COLUMN(A1:E5))+1)) Min Value Company =INDEX(F2:F5,MIN(IF(A2:E5=MIN(A2:E5),ROW(A2:E5)-MIN(ROW(A2:E5))+1))) Min Value Shop Number =INDEX(A1:E1,MIN(IF(A1:E5=MIN(A1:E5),COLUMN(A1:E5)-MIN(COLUMN(A1:E5))+1))) And these are working fine. Thing is i want the top five max values, and top ten min values and their corresponding shop numbers....does anyone have any idea how to do this? I tried using the B59=LARGE($B$59:$Y$82,10) and B59<=SMALL($B$59:$Y$82,10) formulas but no luck at all.....would really really appreciate someones thoughts.....thank you so much anyone - i know this is a complicated query... Sam |
Complicated vlookup/min-max query...please help
|
| All times are GMT +1. The time now is 03:02 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com