>4.5

[mellybean]

Hello I have a worksheet that I need to find the value of cells greater than
4.5 i have tried =AVERAGE(IF(A1:A30>=4.5,A1:A30)) and it works but i only
need certain cells for example i only need cells A1,A2,A3,A4,A10,A23,A34 but
only if they are >4.5 what is the formula?

 

[reitanospa1]

It looks like you're trying to find the average of the cells. If so,
use SUMIF and then divide by COUNTIF:

=SUMIF(B3:B27,">4.5")/COUNTIF(B3:B27,">4.5")

Also, you said greater than; make sure you don't mean to include the
4.5 (change the range to ">=4.5").

 

[Roger Govier]

Hi

You need to enter is as an array formula
To Create or Edit, array formulae, use Control+Shift+Enter (CSE) not just
Enter.
When you use CSE, Excel will insert curly braces { } around your
formula. Do not type them yourself.

When you use CSE, it will look like this
{=AVERAGE(IF(A1:A30>=4.5,A1:A30))}

 

[Don Guillett]

a macro
Sub averagearray()
For Each c In Array(1, 2, 3, 10, 23, 34)
If Cells(c, "a") > 4.5 Then
ms = ms + Cells(c, "a")
cc = cc + 1
End If
Next c
MsgBox ms
MsgBox cc
MsgBox ms / cc
End Sub