| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average by color VBA
I have read many posts on how to Count cells with fill color and how
to sum based on fill color but how do you do an average? Here is the code I have now. Function AVGColor(rColor As Range, Range As Range) Dim rCell Dim iCol As Integer Dim C As Range Dim vResult As Double iCol = rColor.Interior.ColorIndex For Each rCell In Range If rCell.Interior.ColorIndex = iCol Then vResult = WorksheetFunction.Sum(rCell, vResult) End If Next rCell For Each C In Range If C.Interior.ColorIndex = rColor.Interior.ColorIndex Then Count = Count + 1 End If Next C If AVGColor = vResult / Count = Numeric Then AVGColor = vResult / Count Else: AVGColor = "" End If End Function Thanks for the help, Jay |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average by color VBA
Function AVGColor(rColor As Range, rng As Range)
Dim iCol As Integer Dim C As Range Dim vResult As Double Dim Count As Long iCol = rColor.Interior.ColorIndex For Each C In rng If C.Interior.ColorIndex = iCol Then vResult = WorksheetFunction.Sum(C, vResult) Count = Count + 1 End If Next rCell If Count = 0 Then AVGColor = "" Else AVGColor = vResult / Count End If End Function -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "jlclyde" wrote in message oups.com... I have read many posts on how to Count cells with fill color and how to sum based on fill color but how do you do an average? Here is the code I have now. Function AVGColor(rColor As Range, Range As Range) Dim rCell Dim iCol As Integer Dim C As Range Dim vResult As Double iCol = rColor.Interior.ColorIndex For Each rCell In Range If rCell.Interior.ColorIndex = iCol Then vResult = WorksheetFunction.Sum(rCell, vResult) End If Next rCell For Each C In Range If C.Interior.ColorIndex = rColor.Interior.ColorIndex Then Count = Count + 1 End If Next C If AVGColor = vResult / Count = Numeric Then AVGColor = vResult / Count Else: AVGColor = "" End If End Function Thanks for the help, Jay |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average by color VBA
should be
Function AVGColor(rColor As Range, rng As Range) Dim iCol As Integer Dim C As Range Dim vResult As Double Dim Count As Long iCol = rColor.Interior.ColorIndex For Each C In rng If C.Interior.ColorIndex = iCol Then vResult = vResult + C.Value Count = Count + 1 End If Next rCell If Count = 0 Then AVGColor = "" Else AVGColor = vResult / Count End If End Function -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "jlclyde" wrote in message oups.com... I have read many posts on how to Count cells with fill color and how to sum based on fill color but how do you do an average? Here is the code I have now. Function AVGColor(rColor As Range, Range As Range) Dim rCell Dim iCol As Integer Dim C As Range Dim vResult As Double iCol = rColor.Interior.ColorIndex For Each rCell In Range If rCell.Interior.ColorIndex = iCol Then vResult = WorksheetFunction.Sum(rCell, vResult) End If Next rCell For Each C In Range If C.Interior.ColorIndex = rColor.Interior.ColorIndex Then Count = Count + 1 End If Next C If AVGColor = vResult / Count = Numeric Then AVGColor = vResult / Count Else: AVGColor = "" End If End Function Thanks for the help, Jay |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average by color VBA
On Aug 30, 12:49 pm, "Bob Phillips" wrote:
should be Function AVGColor(rColor As Range, rng As Range) Dim iCol As Integer Dim C As Range Dim vResult As Double Dim Count As Long iCol = rColor.Interior.ColorIndex For Each C In rng If C.Interior.ColorIndex = iCol Then vResult = vResult + C.Value Count = Count + 1 End If Next rCell If Count = 0 Then AVGColor = "" Else AVGColor = vResult / Count End If End Function -- HTH Bob The formula needs to skip blank cells liek the Average formula in Excel. This Function does what mine does, just more efficiently. Jay |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average by color VBA
Wouldn't the blanks not be coloured?
Function AVGColor(rColor As Range, rng As Range) Dim iCol As Integer Dim C As Range Dim vResult As Double Dim Count As Long iCol = rColor.Interior.ColorIndex For Each C In rng If C.Interior.ColorIndex = iCol And IsNumeric(C.Value) Then vResult = vResult + C.Value Count = Count + 1 End If Next rCell If Count = 0 Then AVGColor = "" Else AVGColor = vResult / Count End If End Function -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "jlclyde" wrote in message oups.com... On Aug 30, 12:49 pm, "Bob Phillips" wrote: should be Function AVGColor(rColor As Range, rng As Range) Dim iCol As Integer Dim C As Range Dim vResult As Double Dim Count As Long iCol = rColor.Interior.ColorIndex For Each C In rng If C.Interior.ColorIndex = iCol Then vResult = vResult + C.Value Count = Count + 1 End If Next rCell If Count = 0 Then AVGColor = "" Else AVGColor = vResult / Count End If End Function -- HTH Bob The formula needs to skip blank cells liek the Average formula in Excel. This Function does what mine does, just more efficiently. Jay |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average by color VBA
On Aug 30, 1:13 pm, "Bob Phillips" wrote:
Wouldn't the blanks not be coloured? Function AVGColor(rColor As Range, rng As Range) Dim iCol As Integer Dim C As Range Dim vResult As Double Dim Count As Long iCol = rColor.Interior.ColorIndex For Each C In rng If C.Interior.ColorIndex = iCol And IsNumeric(C.Value) Then vResult = vResult + C.Value Count = Count + 1 End If Next rCell If Count = 0 Then AVGColor = "" Else AVGColor = vResult / Count End If End Function -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "jlclyde" wrote in message oups.com... On Aug 30, 12:49 pm, "Bob Phillips" wrote: should be Function AVGColor(rColor As Range, rng As Range) Dim iCol As Integer Dim C As Range Dim vResult As Double Dim Count As Long iCol = rColor.Interior.ColorIndex For Each C In rng If C.Interior.ColorIndex = iCol Then vResult = vResult + C.Value Count = Count + 1 End If Next rCell If Count = 0 Then AVGColor = "" Else AVGColor = vResult / Count End If End Function -- HTH Bob The formula needs to skip blank cells liek the Average formula in Excel. This Function does what mine does, just more efficiently. Jay- Hide quoted text - - Show quoted text - This is what the code shoudl be. Thank you for turing me onto the right answer. Jay If C.Interior.ColorIndex = iCol And IsNumeric(C.Value) And Not IsEmpty(C.Value) Then |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find monthly average but have average automatically configured | Excel Discussion (Misc queries) | |||
| average cells, show 0 if nothing to average | Excel Discussion (Misc queries) | |||
| Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
| Weighed Average of a weiged average when there are blanks | Excel Discussion (Misc queries) | |||
| how does one convert text to a formula "average(A:A)" to =average( | Excel Worksheet Functions | |||
Linear Mode
