IF OR problem

[wadeer]

I have a range of cells that are presently unpopulated. I will populate these cells over time. I want to have this range produce an average. I repeat several ranges after the first range and eventually get an average of all the ranges together. If I use the formula =average(B8:G8) I get #DIV/)! because all the cells in that range are unpopulated. I also get that same message for the future ranges which effects the average of all ranges. When I populate any cell in the first range the formula works but not for all the ranges until they are populated as well. So I used this formula =IF(OR(B8:G8)="","",Average(B8:G8)). I was hoping I would get a blank result if all the cells were unpopulated but I get #VALUE! so my running average is still affected. so my basic question is why isn't the cell containing the second formula not giving me the desired empty cell look until a cell in the range is populated?

 

[iggydarsa]

give this a shot:

=IFERROR(average(B8:G8), "")

 

[Joe4]

It is because this is not a valid calculation:

OR(B8:G8)=""

You can not check a whole range of cells like that.
You could use:

=IF(COUNT(B8:G8)=0,"",Average(B8:G8))

But I think it is easier to just use IFERROR to handle the errors, as shown in the previous post.

 

[Peter_SSs]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

So I used this formula =IF(OR(B8:G8)="","",Average(B8:G8)). I was hoping I would get a blank result if all the cells were unpopulated

You could also try this variation of your attempt

=IF(AND(B8:G8=""),"",AVERAGE(B8:G8))

 

[wadeer]

give this a shot:

=IFERROR(average(B8:G8), "")

Thank You for the quick accurate response, it works perfectly.

 

[wadeer]

It is because this is not a valid calculation:

OR(B8:G8)=""

You can not check a whole range of cells like that.
You could use:

=IF(COUNT(B8:G8)=0,"",Average(B8:G8))

But I think it is easier to just use IFERROR to handle the errors, as shown in the previous post.

thank you for your response