Average Calculations

T

TLAngelo

I did not get a response to this question before, so I thought I'd ask again.
Does anyone know if this is possible?

I have a spreadsheet created that receives data input throughout the month.
Once the whole month is filled in and the extra blank rows deleted there is a
calcuation at the end that tells the average of row Q. It always shows ####
until all the rows are either filled in and/or extra rows deleted. is there
a way to have it calculate the average on an ongoing basis and when new data
is input it
will consider that in the calcualtion as well?

Thanks,

Tania
 
D

dlw

instead of average function, you need to do a sumif on the row for cells that
have a value, then divide by a countif on cells that have values.
 
B

Bob Phillips

Tania,

Average should ignore empty cells, so it sounds as if they are not empty.

What is being counted in the average and what is in the other cells?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
T

TLAngelo

Hello Bob, it is calculating the total holding time that it took to process a
document. The spreadsheet currently has 360 rows with the calculations
already inserted. So when the employee fills in the data it performs that
calculations at that time. So I guess there is "something" in the column
that it is trying to average... a calculation. So it won't calculate the
average until all those 360 rows are filled in and calculated or deleted.

So currently it says =AVERAGE(Q4:Q360) but only rows 4 through 35 have data
in them, the rest just say ##### in Q and will say that until the data is
input.

Clear as mudd??!!

Tania
 
B

Bob Phillips

Does this wrk for you

=AVERAGE(IF(ISNUMBER(Q4:Q360),Q4:Q360))

this is an array formula, so after entering it/copying it to the formula
bar, hit Ctrl-Shift-Enter, not just Enter. Excel will add braces {...}
signifying an array formula.
 
T

TLAngelo

No, it returns a value of 0.

Tania

Bob Phillips said:
Does this wrk for you

=AVERAGE(IF(ISNUMBER(Q4:Q360),Q4:Q360))

this is an array formula, so after entering it/copying it to the formula
bar, hit Ctrl-Shift-Enter, not just Enter. Excel will add braces {...}
signifying an array formula.

--
__________________________________
HTH

Bob
Click to expand...
 
D

Dave Peterson

What's in Q4:Q360?

Try changing the range to something smaller (Q4:Q10) and do some testing.

Remember to array enter the formula, too.
 
T

Tania

Ok, I tried to make the range something smaller and it still returns 0. I
did enter the array formula by using Ctrl-Shift-Enter.

Q4:Q360 has =IF(A30="NA",(0),(NETWORKDAYS(B30,M30,$T$4:$T$9)-1)/2.4+(N30-C30))

Tania
 
D

Dave Peterson

So it looks like Q4:Q360 could contain nothing but 0's.

In that case, 0 would be the average.

What values do you see in Q4:Q10?

What do you get returned from this array formula:
=AVERAGE(IF(ISNUMBER(Q4:Q10),Q4:Q10))
 
Top