Average Calculations
 

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

Average Calculations
 

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.

"TLAngelo" wrote:

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

Average Calculations
 

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)

"TLAngelo" wrote in message
...
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

Average Calculations
 

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

"Bob Phillips" wrote:

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)

"TLAngelo" wrote in message
...
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

Average Calculations
 

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

"TLAngelo" wrote in message
...
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

"Bob Phillips" wrote:

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)

"TLAngelo" wrote in message
...
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

Average Calculations
 

No, it returns a value of 0.

Tania

"Bob Phillips" wrote:

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

"TLAngelo" wrote in message
...
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

"Bob Phillips" wrote:

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)

"TLAngelo" wrote in message
...
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

Average Calculations
 

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.

TLAngelo wrote:

No, it returns a value of 0.

Tania

"Bob Phillips" wrote:

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

"TLAngelo" wrote in message
...
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

"Bob Phillips" wrote:

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)

"TLAngelo" wrote in message
...
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







--

Dave Peterson

Average Calculations
 

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

"Dave Peterson" wrote:

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.

TLAngelo wrote:

No, it returns a value of 0.

Tania

"Bob Phillips" wrote:

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

"TLAngelo" wrote in message
...
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

"Bob Phillips" wrote:

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)

"TLAngelo" wrote in message
...
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







--

Dave Peterson

Average Calculations
 

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))



Tania wrote:

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

"Dave Peterson" wrote:

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.

TLAngelo wrote:

No, it returns a value of 0.

Tania

"Bob Phillips" wrote:

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

"TLAngelo" wrote in message
...
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

"Bob Phillips" wrote:

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)

"TLAngelo" wrote in message
...
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







--

Dave Peterson


--

Dave Peterson