percentage of green cells in list
in [Functions]
|
Prev: unexpected file lock by user
Next: Calculating Average Salaries with Three Other Variables - Pivot Ta
From: rslc on 19 Feb 2010 03:37 Hi i have a list of dates coloured either red or green in column A and i want to express the green cells as a percentage of the total list ie 12/02/10 14/02/10 20/0210 etc this list is constantly being added to thanks for any help rslc
From: Jacob Skaria on 19 Feb 2010 07:21 There is no built-in excel function to do this. But you can use a UDF that looks at the range and returns the sum of color. But that function will not recalculate if you change color. Every time you change the color you will need to recalculate or wait excel to recalculate... To install the UDF (User Defined function). From workbook launch VBE using Alt+F11. From menu Insert a Module and paste the below function.Close and get back to workbook and try the below formula. Function COLORCOUNT(varRange As Range, varColor As Range) Dim cell As Range For Each cell In varRange If cell.Interior.ColorIndex = varColor.Interior.ColorIndex Then ColorCount = ColorCount + 1 End If Next End Function ITo use the as a formula in range A1:A10 try the below; which will count the number of blue cells....The second argument denotes the colored cell (to be queried). =COLORCOUNT(A1:A10,B1) -- Jacob "rslc" wrote: > Hi > i have a list of dates coloured either red or green in column A and i > want to express the green cells as a percentage of the total list ie > 12/02/10 > 14/02/10 > 20/0210 etc > this list is constantly being added to > > thanks for any > help > > rslc > > . >
From: Jacob Skaria on 19 Feb 2010 07:23 Forgot to mention that the below do not work for Conditional Formatted cells. -- Jacob "Jacob Skaria" wrote: > There is no built-in excel function to do this. But you can use a UDF that > looks at the range and returns the sum of color. But that function will not > recalculate if you change color. Every time you change the color you will > need to recalculate or wait excel to recalculate... > > To install the UDF (User Defined function). From workbook launch VBE using > Alt+F11. From menu Insert a Module and paste the below function.Close and get > back to workbook and try the below formula. > > Function COLORCOUNT(varRange As Range, varColor As Range) > Dim cell As Range > For Each cell In varRange > If cell.Interior.ColorIndex = varColor.Interior.ColorIndex Then > ColorCount = ColorCount + 1 > End If > Next > End Function > > ITo use the as a formula in range A1:A10 try the below; which will count the > number of blue cells....The second argument denotes the colored cell (to be > queried). > > =COLORCOUNT(A1:A10,B1) > -- > Jacob > > > "rslc" wrote: > > > Hi > > i have a list of dates coloured either red or green in column A and i > > want to express the green cells as a percentage of the total list ie > > 12/02/10 > > 14/02/10 > > 20/0210 etc > > this list is constantly being added to > > > > thanks for any > > help > > > > rslc > > > > . > >
From: rslc on 23 Feb 2010 04:17
Thanks Jacob unfortunately my list is conditionally formatted and i was hoping to use a formula as a conditional format to achieve this but thanks for the help rslc "Jacob Skaria" <JacobSkaria(a)discussions.microsoft.com> wrote in message news:CEA7E84E-D33C-4E63-82D3-6A9E72000CBF(a)microsoft.com... > Forgot to mention that the below do not work for Conditional Formatted > cells. > > -- > Jacob > > > "Jacob Skaria" wrote: > >> There is no built-in excel function to do this. But you can use a UDF >> that >> looks at the range and returns the sum of color. But that function will >> not >> recalculate if you change color. Every time you change the color you >> will >> need to recalculate or wait excel to recalculate... >> >> To install the UDF (User Defined function). From workbook launch VBE >> using >> Alt+F11. From menu Insert a Module and paste the below function.Close and >> get >> back to workbook and try the below formula. >> >> Function COLORCOUNT(varRange As Range, varColor As Range) >> Dim cell As Range >> For Each cell In varRange >> If cell.Interior.ColorIndex = varColor.Interior.ColorIndex Then >> ColorCount = ColorCount + 1 >> End If >> Next >> End Function >> >> ITo use the as a formula in range A1:A10 try the below; which will count >> the >> number of blue cells....The second argument denotes the colored cell (to >> be >> queried). >> >> =COLORCOUNT(A1:A10,B1) >> -- >> Jacob >> >> >> "rslc" wrote: >> >> > Hi >> > i have a list of dates coloured either red or green in column A >> > and i >> > want to express the green cells as a percentage of the total list ie >> > 12/02/10 >> > 14/02/10 >> > 20/0210 etc >> > this list is constantly being added to >> > >> > thanks for >> > any >> > help >> > >> > rslc >> > >> > . >> > |