I need an If statement that allows me to calculate a value without having to
have an entry in each cell to calculate with. Then I need to be able to
figure an average using all cells included in the range and have that number
fill the cells in the column that correspond to the cells in the range used
for the average. I.E. If I have a value on day 1, but don't have a value to
enter until day 5, I need to use those two values to calculate a value. Now I
need to average that value using all of the days.

Can an If statement do that?

As it says in Help, the AVERAGE function ignores blank cells.

On Tue, 1 Mar 2005 21:09:04 -0800, jbaker
wrote:

I need an If statement that allows me to calculate a value without having to
have an entry in each cell to calculate with. Then I need to be able to
figure an average using all cells included in the range and have that number
fill the cells in the column that correspond to the cells in the range used
for the average. I.E. If I have a value on day 1, but don't have a value to
enter until day 5, I need to use those two values to calculate a value. Now I
need to average that value using all of the days.

Can an If statement do that?

In addition, the worksheet has text cells to differentiate the months in the
column with values. I have a simple VB script that works in a macro until it
encounters the text cells. Also, I would like this to run automatically
without having to envoke a macro. Is there a way to do this?

Thanks!

"jbaker" wrote:

I need an If statement that allows me to calculate a value without having to
have an entry in each cell to calculate with. Then I need to be able to
figure an average using all cells included in the range and have that number
fill the cells in the column that correspond to the cells in the range used
for the average. I.E. If I have a value on day 1, but don't have a value to
enter until day 5, I need to use those two values to calculate a value. Now I
need to average that value using all of the days.

Can an If statement do that?

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