How to avoid error if the cell is blank...?

[PP3321]

I am using below formula to average the dynamic range.

=AVERAGEIF(B4:INDIRECT("B"&LOOKUP(9^9,B:B,ROW(B:B)-1)),">0")

Problem is that it gets error if the value is all empty.

Can anyone please help me on this...?
Is there any Excel Function that prevents errors?

 

[PP3321]

When I delete all the values from the range,
I get Circular Reference Warning.

I want the formula to return 0 or empty...

 

[PP3321]

Sorry, this is the correct formula to averageif the dynamic range in B column,

*starting from B4.
*excluding value 0

=AVERAGEIF(B4:INDIRECT("B"&LOOKUP(9^9,B:B,ROW(B:B))),">0")

 

[Khalid NGO]

Hi,

You are getting circular ref because when there is no value in range, INDIRECT will return the cell reference B1, where the formula is entered.

Any specific reason why you are not using Table?

See the attached with simple formula:
=IFERROR(AVERAGEIF(Table1[column1],">0"),"")

You just need to extend the Table range if your data grows.

Regards,

 

[Ashhu]

i changed formula little bit, check if its ok!

=IFERROR(AVERAGEIF(B4:INDIRECT("B"&LOOKUP(9^9,B$4:B$1048576,ROW(B$4:B$1048576))),">0"),"")

 

[PP3321]

Dear Ashhu,
It works! Thanks!
So if it is absolute reference, direct functin() does not return the first cell?

Dear Khaled,
Thank you!
Table is OK also..thanks...
By the way, what if it is Pivot Table...
Can we do the same with Pivot Table...?

 

[PP3321]

I tried Pivot Table but this did not work.

=IFERROR(AVERAGEIF(PivotTable2[Sum of column2],">0"),"")

I will do more research on this...

 

[Khalid NGO]

Can we do the same with Pivot Table...?

Yes sure

Using file in the post # 4, follow these steps:

• Select any cell in the Table, Go to Insert > Pivot Table > Pivot Table
• Excel will automatically select your complete Table.
• Set the location where you want your Pivot, and Hit OK.
• Check Mark column1, and move it from Row Labels to Values
• Click on down arrow option > Value Field settings > change count to Average.

Note: If you enter new data you'll need to Refresh every time.

Regards,

 

[PP3321]

Khaled thanks but for Pivot Table Column, we need to do Sum.
This formula will be useful because it will also give us Average.

I want to do your formula with Pivot Table...
Is it possible...
=IFERROR(AVERAGEIF(PivotTable1[column1],">0"),"")

 

[Khalid NGO]

Not getting your point

 

[PP3321]

I do Sum in Pivot Table.

But, I also want to have Average from that column of Pivot Table on the same worksheet...

Please see below...

Your formula might help me achieve this...

 

[Khalid NGO]

So you want 20 (average) instead of 60 (sum) as Grand Total?

If so, change Value Field Settings from Sum to Average

I am not sure Pivot Tables can be referred like your post # 9.

Regards,

 

[PP3321]

I want to show both Sum & Average on the same column.
Pivot Table will show Sum Total
Formula will show Average...

 

[Khalid NGO]

for SUM : Use Pivot Table
for Average : Use Source data/Table

As I said "I am not sure Pivot Tables can be referred like your post # 9."

Regards,

 

[PP3321]

OK thanks...