I can think of two ways to handle your problem as i understand it. (The
first problem, not the VBA one.)

Assuming you have 5 cells in cells A1 to E1, you could enter the
following formula in, say, H1, and then copy it to I1 thru L1 5:

=if(isblank(A1),0,[your formula operating on A1])

Then in cell F1, you can enter:

= AVERAGE(H1:L1)

The other, more complex way, one which you may not want to mess with,
is to enter an array formula. So, for example, if your formula
operation is to square each cell value and add 5 to it, enter this
formula in a cell and press Ctrl+Shift+Enter:

=AVERAGE(IF(NOT(ISBLANK(A1:E1)),(A1:E1)^2+5,0))

The formula will be entered *by Excel* (not by you!) with curly braces
"{}' when you finish entering it by pressing Ctrl+Shift+Enter. Note the
portion where your formula operation is, i.e., the "(A1:E1)^2+5" part.
You didn't specify what your formula is, but if you enter "(A1:E1)"
where you would normally enter a single cell address, it should do the
job.

Clear as mud? Then stick with the first solution. I probably would!

I hope this helps,

Gary