Help Please with If statement / vlookup

[Donna]

How do I write an if statement if I only want to get the averages of the
first five cells with a value? I will need to look in about 10 cells, but
only avg the FIRST 5 with a value. Some will be blank and Some will be on a
different sheet

 

[Billy Liddel]

Donna,

The easiest way to handle this is with a User Defined Function (UDF). The
following will work for you.

Function AvgFirstFiveVals(ParamArray args() As Variant) As Double

'11th March 2010
'Author: Peter Atherton

Application.Volatile

Dim i As Variant, _
tmpRange As Range, _
cell As Range, _
tmpSum As Double, _
tmpCount As Integer, _
iLim As Integer

iLim = 5

For i = 0 To UBound(args)

If Not IsMissing(args(i)) Then

Set tmpRange = Intersect(args(i).Parent.UsedRange, args(i))

For Each cell In tmpRange

If IsNumeric(cell) And cell <> 0 And _
Len(cell) <> 0 Then
tmpCount = tmpCount + 1
tmpSum = tmpSum + cell
AvgFirstFiveVals = tmpSum / tmpCount

If tmpCount = iLim Then Exit Function
End If

Next cell

End If

Next i

End Function


This has to be copied in to the Visual basic Editor before it can be used.
Press ALT + F11, Insert, Module then paste the code in the Module. Press ALT
+ Q to quit the VBE and return to the spreadsheet.

Enter the function as you would for a SUM e.g.

=AVGFIRSTFIVEVALS(A1,C34,Sheet2!A67...Sheet3!B34:B40)

You can also link it to another workbook if you like.

HTH
Peter

 

[מיכ×ל (מיקי) ×בידן]

I didn't understand the part regarding the "other sheet" - however in order
to calculate the first 5 non empty values in range A1:A10 you can use the
following Array-Formula:
{=AVERAGE(SMALL(A1:A10,ROW(1:5)))}
*** The formula is to be confirmed with CTRL+SHIFT+ENTER rather than with
simply ENTER.
The curly brackets {} are not to be typed manually, those are entered by the
“Excelâ€, when the formula is entered as an Array formula.
Micky

 

[Donna]

=AVGFIRSTFIVEVALS(K6:K7,Feb!K16:K24)
Billy, I added the UDF to the sheet and this my formula but I am getting a
#value! in the cell
I have two sheets, the other sheet is "Feb"...

 

[Donna]

=AVERAGE(SMALL(K9:K10,ROW($1:$5)))
How do I add a range of cells from another sheet in the workbook

the works great, but I need to look at a range in another sheet along with
the range in this sheet

 

[Billy Liddel]

Donna,

Is therre an error in the data? I insert an +NA() into the data and this
produced a #VALUE! error. Correct this and it will work.

HTH
Peter

 

[Donna]

Billy, I don't understand the insert an +NA() in the data

{=AVERAGE(SMALL(K7:K20,ROW($1:$5)))}
This formulal works great to find an average for the first five cells that
have a value, however If want to look at cells from two different sheets how
would I write the formula.
I tried this, {=AVERAGE(SMALL((K6:K19,Feb!K19:K24),ROW($1:$5)))} but I get a
#value! ( one of the sheets is Mar and one is Feb)
***Basically I need to get an average from the first five cells that have
values, But I need to look at cells in two different sheets.

 

[Billy Liddel]

I was replying to you regarding the UDF. There is an Excel function called NA
that produces the #N/A! value. I entered this into the test data '=NA()',to
produce a #VALUE! error like the one you describe. The UDF would also produce
a value error if there was a division by zero. If the data is numeric with no
errors it will give the average

Perhaps Micky can help you with the other solution.

 

[Donna]

Hi Billy, I am sorry, I am trying both things at once.
there isn't a error in any of the data, but some of the cells are blank...

 

[Billy Liddel]

Blank Cells make no difference, they are ignored. If you want to send me the
workbook I'll look at it. Insert a new sheet saying precisely what you want
and would expect from a set of data.

Send to (e-mail address removed)