Average

Eskimo2

New Member
Joined
Sep 14, 2006
Messages
3
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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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.
 

Eskimo2

New Member
Joined
Sep 14, 2006
Messages
3
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
 

bill_reinwald

New Member
Joined
Sep 19, 2005
Messages
22

ADVERTISEMENT

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
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I think the op wants a running average:

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

Forum statistics

Threads
1,136,878
Messages
5,678,312
Members
419,754
Latest member
LordEddard

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top