I have in a row (adjacent coloumns)months & quarters (Jan, Feb, March,
quarter 1...upto Quarter 4 and then year total). I want to calculate the
annual average ignoring the quarter figures and the blank cells also. Pl help

change your quarterly averages to use

=Subtotal(1,monthly cells)

and your annual average to

=Subtotal(1, all cells)

The subtotal() function will ignore any other subtotal() formulas in the
range, and appears to ignore empty cells, too



"HARSH BAHAL" wrote:

I have in a row (adjacent coloumns)months & quarters (Jan, Feb, March,
quarter 1...upto Quarter 4 and then year total). I want to calculate the
annual average ignoring the quarter figures and the blank cells also. Pl help

Thanks Duke But it has not solved my problem. Actually there are three rows
which total upto to a fourth row which is the sum of three rows, and the
annual average is to be defined for the the fourth row, in which the returned
value of average is not matching to that of individula rows. May be I am not
being clear, if you can give me your ID , I will be able to explain you
better with attachments.

Regards,
Harsh Bahal




"Duke Carey" wrote:

change your quarterly averages to use

=Subtotal(1,monthly cells)

and your annual average to

=Subtotal(1, all cells)

The subtotal() function will ignore any other subtotal() formulas in the
range, and appears to ignore empty cells, too



"HARSH BAHAL" wrote:

I have in a row (adjacent coloumns)months & quarters (Jan, Feb, March,
quarter 1...upto Quarter 4 and then year total). I want to calculate the
annual average ignoring the quarter figures and the blank cells also. Pl help

Just show some numbers in your reply and we can try to diagnose from there

"HARSH BAHAL" wrote:

Thanks Duke But it has not solved my problem. Actually there are three rows
which total upto to a fourth row which is the sum of three rows, and the
annual average is to be defined for the the fourth row, in which the returned
value of average is not matching to that of individula rows. May be I am not
being clear, if you can give me your ID , I will be able to explain you
better with attachments.

Regards,
Harsh Bahal




"Duke Carey" wrote:

change your quarterly averages to use

=Subtotal(1,monthly cells)

and your annual average to

=Subtotal(1, all cells)

The subtotal() function will ignore any other subtotal() formulas in the
range, and appears to ignore empty cells, too



"HARSH BAHAL" wrote:

I have in a row (adjacent coloumns)months & quarters (Jan, Feb, March,
quarter 1...upto Quarter 4 and then year total). I want to calculate the
annual average ignoring the quarter figures and the blank cells also. Pl help

Thanks Duke, but I was able to solve it during the interim.

"Duke Carey" wrote:

Just show some numbers in your reply and we can try to diagnose from there

"HARSH BAHAL" wrote:

Thanks Duke But it has not solved my problem. Actually there are three rows
which total upto to a fourth row which is the sum of three rows, and the
annual average is to be defined for the the fourth row, in which the returned
value of average is not matching to that of individula rows. May be I am not
being clear, if you can give me your ID , I will be able to explain you
better with attachments.

Regards,
Harsh Bahal




"Duke Carey" wrote:

change your quarterly averages to use

=Subtotal(1,monthly cells)

and your annual average to

=Subtotal(1, all cells)

The subtotal() function will ignore any other subtotal() formulas in the
range, and appears to ignore empty cells, too



"HARSH BAHAL" wrote:

I have in a row (adjacent coloumns)months & quarters (Jan, Feb, March,
quarter 1...upto Quarter 4 and then year total). I want to calculate the
annual average ignoring the quarter figures and the blank cells also. Pl help