if statement on a range

R

rcr1991

New Member
Joined
Oct 26, 2005
Messages
37
i have a named range:

Set myrng = ActiveSheet.Range(Cells(x_rows, 2), Cells(y_rows, 2))

i want to get an average of the values in that range of all the cells where the cell is not colored red

i'm stuck.

concept is : average (myrng, cells where interior.color <>3)

suggestions?

thanks in advance,

rob
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
How about something like this (in a standard module):

Code: 
Sub getRedAvg()
'blank cells count as zeroes as written
Dim cell As Range
Dim myAvg As Variant
Dim myCount As Double
Dim mySum As Double

    For Each cell In Selection
        If IsNumeric(cell.Value) Then
            If cell.Font.ColorIndex <> 3 Then 'red
                mySum = mySum + cell.Value
                myCount = myCount + 1
            End If
        End If
    Next cell
    
    If myCount = 0 Then
        myAvg = "N/A"
    Else
        myAvg = mySum / myCount
    End If
    
    MsgBox myAvg
    
End Sub

Hope that helps!
 
Upvote 0
rcr1991 said:
i have a named range:

Set myrng = ActiveSheet.Range(Cells(x_rows, 2), Cells(y_rows, 2))

i want to get an average of the values in that range of all the cells where the cell is not colored red

i'm stuck.

concept is : average (myrng, cells where interior.color <>3)

suggestions?

thanks in advance,

rob
Click to expand...
Hi rob:

Formula wise, If you used Conditional Formatting, then apply the same condition to get the desired average.

Since you asked for a VBA solution, the following is a brute force method using rows 2, and 11 as the starting and ending rows of your range
Code: 
Sub y_4()
    intColor = 3
    myCount = 0
    mySum = 0
    Set myrange = ActiveSheet.Range(Cells(2, 2), Cells(11, 2))
    For Each cell In myrange
        If cell.Interior.Color <> intColor Then
             mySum = cell + mySum
             myCount = myCount + 1
        End If
    Next cell
    myAverage = mySum / myCount
    MsgBox myAverage
End Sub
 
Upvote 0
thank you v. much for suggestions.

however, i would like to use average(range) and stdev(range) or sqrt(range) etc. on the range of cells not including values of red cells.

while the average calculations will work, the stdev, sqrt, etc. solutions are not so obvious.

should i make a new range first that only includes non-red cells?

or other ideas?

thanks,

rob
 
Upvote 0
rcr1991 said:
while the average calculations will work, the stdev, sqrt, etc. solutions are not so obvious.
Click to expand...

You could use Application.WorksheetFunction.StDev(Selection) to get the standard deviation of the selected range.

How would you evaluate the square root of a range?
 
Upvote 0
tazguy,

yes, dumb example re: sqrt. just trying to say that i need to perform something besides simple math on the range, once i select the NON_RED cells in that range. the other helpful fellows solved how to identify the cells that are not red, and sum them up.

but i need to exclude those darn red colored cells before using:

Application.WorksheetFunction.StDev(Selection)

can you propose a way to make the cells identified in this code a selection that i can use in your example?

For Each cell In myrange
If cell.Interior.Color <> intColor Then
.......
End If
Next cell

tia,

Rob
 
Upvote 0
still stuck, would new array answer problem?

all of these suggestions are great for getting the average, but if i want to have a new group of cells thqat can be addressed by functions like stdev, then i need some way of groups these cells, not looping through them one at a time.

would creating a new array based on red/not red interior cell color work?

suggestions?
 
Upvote 0
Here is a small example.
Code: 
Sub test()
Dim rng1 As Range
Dim rng2 As Range
Dim c As Range

    Set rng1 = Range("A1:A3")
    
    For Each c In rng1.Cells
        If c.Interior.ColorIndex = 3 Then
            If rng2 Is Nothing Then
                Set rng2 = c
            Else
                Set rng2 = Union(rng2, c)
            End If
        End If
    Next c
    
    MsgBox Application.WorksheetFunction.StDev(rng2)
End Sub
 
Upvote 0
norie,

thanks, i see what you're doing. will try right away. the 'union' command may be just what i was missing.

thanks!

rob
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Multiple conditional formatting
Replies
6
Views
491
StephenCrump
S
I
  • Solved
In Excell VBA the set variable change without clear reason
Replies
5
Views
114
Micron
M
M
  • Question Question
VBA error not making sense
2
Replies
10
Views
267
Skyybot
Skyybot
W
  • Question Question
Create a Dynamic Line Chart for last 4 weeks of data
Replies
2
Views
161
StephenCrump
S
D
  • Solved
Conditional Formatting Using VBA
2
Replies
15
Views
707
DanteAmor
DanteAmor

Forum statistics

Threads
1,226,216
Messages
6,189,675
Members
453,563
Latest member
Aswathimsanil

Share this page

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
Back
Top