Average

Eskimo2

New Member
I need and Average to fill in column H8:h100 to calculate a range of cells in column G and one that will ignore blank cells, 0's and formula, while returning a blank cell/s if no data at the remaning row of cells!
ie
range is G8:G100
range data currently fills rows from G8:G50 at this point of time

so therefore I require the average shown at H51 to return a blank (or even a zero would do)
(I can get average to work all the way to H100 but it shows the data calculated to G50)

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
hi - welcome to the board!

I think I know what you're after, but I'm not convinced. can you post back but be more specific - take use through the details of what you've got & what you need - examples help.

Column G
21.90
22.54
Blank
Bank
Blank
Blank

Colulm H (average of g)
21.90
22.22
22.22
22.22
22.22
22.22

As you can see the column H contains additional values even though rows/cells in G are blank. I would like the cells to show blank in H until the next blank cells in G has data

please post the formula you're currently using

Do you mean, if G51 is blank, you want H51 and down to also be blanks? If you do, you can use an IF statement to do it, put the following formula in column H, row 1, and copy down to H100.

=IF(G1="","",AVERAGE(\$G\$1:\$G\$100))

This'll return a blank if the cell to the left is blank, or it'll return an average of the entire range

I think the op wants a running average:

Perhaps:
=IF(G1="","",AVERAGE(\$G\$1:G1))
Copy down.

Thank You very Much Hottpepper

Replies
0
Views
372
Replies
3
Views
138
Replies
6
Views
219
Replies
4
Views
166
Replies
2
Views
172

1,219,570
Messages
6,149,044
Members
450,853
Latest member
xtiinctt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back