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

"Donna" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

=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" wrote:

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