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%

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%

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%