Excel Question

[Tom Slick]

Is there a way to make an excel formula ignore empty cells until they are "filled"?

i.e. you write the formula =AVERAGE(A1:A8) but A1:A8 doesn't currently contain data you'll get an #DIV/0 error.

I am building a spreadsheet (Cpk) with several columns of data that each column gets averaged (x bar) then the average of the averages (x bar bar) is calc'd. I'd like to leave some empty columns for future input but don't want to put a "0" placeholder in the empty column.

[chopnhack]

cant divide by zero as you found out - its a limitation of the formula your using. I dont know of any others, the only thing i can offer you is to shorten your averaging list to the numbers you have and expand the field (a1:a12, etc) as you get those results.

[master53yoda]

excel formulas

if A1>.01,formula,""

if a1 is greater then.01, use the formula, if not "" leaves a blank

use the if formal it is pretty self explanatory to uses.



art

[LCHIEN]

excel treats empty cells as zeros for calculations. except for average which counts non-blank items for what to divide by.
The simplest thing is "if (count(a1.a8)=0, "", Average(A1.a8))"
that will put a empty field if there are no items, or the average if there are more than one item. The count function returns the number of non-blank items in the range.

I did this from memory, it'll be something like that , if not exactly syntactically correct.
I'm watching the BCS...

[Tom Slick]

Thanks guys!

It didn't dawn on me to write an IF statement (obviously).
Loring, you were right on except you have to define cell ranges with a colon, not a decimal.

anyone deal with process control charts, 6 sigma, and discrete data?

[LCHIEN]

Thanks guys!

It didn't dawn on me to write an IF statement (obviously).
Loring, you were right on except you have to define cell ranges with a colon, not a decimal.

anyone deal with process control charts, 6 sigma, and discrete data?

Actually, you can type it in with dot or colon, it works. Shows as a colon in the readback.
I usually start the range by moving the cursor with the arrow keys, hit a dot, then move the cursor to end of the range and close with ")".

[Tom Slick]

Thanks for the trick, it does save a keystroke. I usually hold down shift and use the arrow keys so it automatically fills in the ":"