Help Please with If statement / vlookup

  • Thread starter Thread starter Donna
  • Start date Start date
D

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
 
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
 
=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"...
 
=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
 
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
 
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.
 
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.
 
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...
 
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)
 
Back
Top