4.5
 

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?

4.5
 

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").


On Jan 29, 11:04 am, mellybean
wrote:
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?

4.5
 

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))}

--

Regards
Roger Govier

"mellybean" wrote in message
...
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?

4.5
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"mellybean" wrote in message
...
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?