Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOKUP
I have a list with different colors and in the list it calculates how much
each color represents of the total. I'd like to create another list below this one that will pull in any category that represents 10% or greater of the total. I've been trying to use {=INDEX($G$3:$G$16,MAX(IF($I$3:$I$160.1,ROW($I$3: $I$16)-MIN(ROW($I$3:$I$16))+1)))}, but I need to adjust it so that it doesn't pull the same color more than once. Any suggestions would be greatly appreciated. Color Name Total Tools Percent to Total Orange 4 2% Purple 45 26% Yellow 8 5% Red 10 6% Blue 9 5% Green 10 6% Brown 35 20% Black 6 3% White 16 9% Grey 8 5% Pink 5 3% Tan 5 3% Cream 6 3% Gold 6 3% Total 173 100% Any color which represents 10% or greater to total. Purple 26.0% Brown 20.0% |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOKUP
OK. suppose you have you data in the below format in Col A:C starting from
row1 with headers in row1 Col A Col B Col C Color Total % to total Orange 4 2% Purple 45 26% Yellow 8 5% Red 10 6% Blue 9 5% Green 10 6% Brown 35 20% Black 6 3% White 16 9% Grey 8 5% Pink 5 3% Tan 5 3% Cream 6 3% Gold 6 3% Total 173 100% In the first row itself in Col E;F G type in the headers and the serial numbers in Col E 1,2,3,4,5,... Col E Col F Col G Sl .No Color % in total 1 =formula1 =formula2 2 3 4 5 In place of Formula1 try =IF(ISERROR(INDEX($A$1:$C$16,SMALL(IF($C$2:$C$150 .1,ROW($C$2:$C$15)),$E2),1)),"",INDEX($A$1:$C$16,S MALL(IF($C$2:$C$150.1,ROW($C$2:$C$15)),$E2),1)) In place of Formula2 try =IF(ISERROR(INDEX($A$1:$C$16,SMALL(IF($C$2:$C$150 .1,ROW($C$2:$C$15)),$E2),1)),"",INDEX($A$1:$C$16,S MALL(IF($C$2:$C$150.1,ROW($C$2:$C$15)),$E2),3)) and copy down.. If this post helps click Yes --------------- Jacob Skaria "Nate" wrote: I have a list with different colors and in the list it calculates how much each color represents of the total. I'd like to create another list below this one that will pull in any category that represents 10% or greater of the total. I've been trying to use {=INDEX($G$3:$G$16,MAX(IF($I$3:$I$160.1,ROW($I$3: $I$16)-MIN(ROW($I$3:$I$16))+1)))}, but I need to adjust it so that it doesn't pull the same color more than once. Any suggestions would be greatly appreciated. Color Name Total Tools Percent to Total Orange 4 2% Purple 45 26% Yellow 8 5% Red 10 6% Blue 9 5% Green 10 6% Brown 35 20% Black 6 3% White 16 9% Grey 8 5% Pink 5 3% Tan 5 3% Cream 6 3% Gold 6 3% Total 173 100% Any color which represents 10% or greater to total. Purple 26.0% Brown 20.0% |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOKUP
Hi Jacob,
Sorry for the delay in response time. I got busy with some other stuff. I wasn't able to get this to work, but I might not be adjusting the formula correctly when I paste it into my spreadsheet. Did it work for you? "Jacob Skaria" wrote: OK. suppose you have you data in the below format in Col A:C starting from row1 with headers in row1 Col A Col B Col C Color Total % to total Orange 4 2% Purple 45 26% Yellow 8 5% Red 10 6% Blue 9 5% Green 10 6% Brown 35 20% Black 6 3% White 16 9% Grey 8 5% Pink 5 3% Tan 5 3% Cream 6 3% Gold 6 3% Total 173 100% In the first row itself in Col E;F G type in the headers and the serial numbers in Col E 1,2,3,4,5,... Col E Col F Col G Sl .No Color % in total 1 =formula1 =formula2 2 3 4 5 In place of Formula1 try =IF(ISERROR(INDEX($A$1:$C$16,SMALL(IF($C$2:$C$150 .1,ROW($C$2:$C$15)),$E2),1)),"",INDEX($A$1:$C$16,S MALL(IF($C$2:$C$150.1,ROW($C$2:$C$15)),$E2),1)) In place of Formula2 try =IF(ISERROR(INDEX($A$1:$C$16,SMALL(IF($C$2:$C$150 .1,ROW($C$2:$C$15)),$E2),1)),"",INDEX($A$1:$C$16,S MALL(IF($C$2:$C$150.1,ROW($C$2:$C$15)),$E2),3)) and copy down.. If this post helps click Yes --------------- Jacob Skaria "Nate" wrote: I have a list with different colors and in the list it calculates how much each color represents of the total. I'd like to create another list below this one that will pull in any category that represents 10% or greater of the total. I've been trying to use {=INDEX($G$3:$G$16,MAX(IF($I$3:$I$160.1,ROW($I$3: $I$16)-MIN(ROW($I$3:$I$16))+1)))}, but I need to adjust it so that it doesn't pull the same color more than once. Any suggestions would be greatly appreciated. Color Name Total Tools Percent to Total Orange 4 2% Purple 45 26% Yellow 8 5% Red 10 6% Blue 9 5% Green 10 6% Brown 35 20% Black 6 3% White 16 9% Grey 8 5% Pink 5 3% Tan 5 3% Cream 6 3% Gold 6 3% Total 173 100% Any color which represents 10% or greater to total. Purple 26.0% Brown 20.0% |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU | Excel Discussion (Misc queries) | |||
Lookup looks to the prior column if zero appears in the lookup col | Excel Discussion (Misc queries) | |||
Get Cell Address From Lookup (Alternative to Lookup) | Excel Worksheet Functions | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) |