random %

oddworld

Board Regular
Joined
May 31, 2005
Messages
248
hi does anyone know how i could do the following
i have multable colour name criteria in column a ie

red/green/orange in column a
what i am trying to do is in column b apply a percentage breakup of the 3 colours to column b based on a percentage

look in coumn a "red/green/orange" count how may times this parameter occours and then apply the following percetages to column b.
red,14% - green between 14% and 34% and orange >35%

so if red/green/orange occurs 20 times in column a the break in column b would be red recorded 3 times and green would be recorded 7 times and oranges would be recoreded 13 times as per the percentages(approx)

any ideas

should this be in a case statement i am not sure?
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Dufus

Board Regular
Joined
Aug 19, 2006
Messages
176
oddworld,

Maybe it's just late here but, I can't figure out if the colors are combined as three in one cell or if each cell only holds one color?

Dufus
 

oddworld

Board Regular
Joined
May 31, 2005
Messages
248
re

hi each cell in column a has


red/green/orange
red/green/orange
red/green/orange
red/green/orange
red/green/orange
red/green/orange
red/green/orange
red/green/orange
 

Dufus

Board Regular
Joined
Aug 19, 2006
Messages
176
Would three conditional formats work? Try this for conditional format number 1 for the entire range and set the color to red background:

Code:
=IF(ROW(A1)<=ROUND(COUNTIF($A$1:$A$30,"red/green/orange")*0.14,0),TRUE,FALSE)

Dufus
 

Derek

Well-known Member
Joined
Feb 16, 2002
Messages
1,592
Hi there
You can try this (adapt to suit). It is set up for the 7 colors of the rainbow. It will recognise any combination / duplication of those 7 colors.


Sub CountColors()
' Macro recorded 20/09/2006 by DEREK
Dim str1 As String
Dim str2 As String
Dim str3 As String
Dim str4 As String
Dim str5 As String
Dim str6 As String
Dim str7 As String

Dim Red As Long
Dim Orange As Long
Dim Yellow As Long
Dim Green As Long
Dim Blue As Long
Dim Indigo As Long
Dim Violet As Long

Red = 0
Orange = 0
Yellow = 0
Green = 0
Blue = 0
Indigo = 0
Violet = 0

str1 = "red"
str2 = "orange"
str3 = "yellow"
str4 = "green"
str5 = "blue"
str6 = "indigo"
str7 = "violet"

For Each cell In Range("A1", Range("A65536").End(xlUp))
Red = Red + (Len(cell) - Len(WorksheetFunction.Substitute(cell, str1, ""))) / 3
Orange = Orange + (Len(cell) - Len(WorksheetFunction.Substitute(cell, str2, ""))) / 6
Yellow = Yellow + (Len(cell) - Len(WorksheetFunction.Substitute(cell, str3, ""))) / 6
Green = Green + (Len(cell) - Len(WorksheetFunction.Substitute(cell, str4, ""))) / 5
Blue = Blue + (Len(cell) - Len(WorksheetFunction.Substitute(cell, str5, ""))) / 4
Indigo = Indigo + (Len(cell) - Len(WorksheetFunction.Substitute(cell, str6, ""))) / 6
Violet = Violet + (Len(cell) - Len(WorksheetFunction.Substitute(cell, str7, ""))) / 6
Next

[b1].Value = "Red = " & Red
[b2].Value = "Orange = " & Orange
[b3].Value = "Yellow = " & Yellow
[b4].Value = "Green = " & Green
[b5].Value = "Blue = " & Blue
[b6].Value = "Indigo = " & Indigo
[b7].Value = "Violet = " & Violet

End Sub

regards
Derek
 

Forum statistics

Threads
1,136,618
Messages
5,676,844
Members
419,655
Latest member
pd2021vb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top