# #NUM error when trying to average cells that also contain a formula

1. ## #NUM error when trying to average cells that also contain a formula

Im trying to get the average of data from 5 other sheets with

=AVERAGE('Period 1'!L4:L43,'Period 2'!L4:L43,'Period 3'!L4:L43,'Period 4'!L4:L43,'Period 5'!L4:L43)

the cells I want to find the average of (L4:L43......etc.) contain the "LN" function referencing the cell to their left, eg. L4 = LN(K4)
so i get a #NUM error in the cell containing

=AVERAGE('Period 1'!L4:L43,'Period 2'!L4:L43,'Period 3'!L4:L43,'Period 4'!L4:L43,'Period 5'!L4:L43)

Is there a way I can adjust this formula to work out the average rather than give #NUM error?

Thanks

2. ## Re: #NUM error when trying to average cells that also contain a formula

you could issue something like this first in your LN functions

Formula:
`Please Login or Register  to view this content.`

or you could change the "" to 0 to receive zero when error is met.

3. ## Re: #NUM error when trying to average cells that also contain a formula

You should try to fix the errors as Vlady noted.

However, just for the heck of it...

You could try this:

=SUMPRODUCT(SUMIF(INDIRECT("'Period "&{1,2,3,4,5}&"'!L4:L43"),"<1E100"))/COUNT('Period 1'!L4:L43,'Period 2'!L4:L43,'Period 3'!L4:L43,'Period 4'!L4:L43,'Period 5'!L4:L43)

4. ## Re: #NUM error when trying to average cells that also contain a formula

I put that in but it made no difference to the #NUM error in the cell with

=AVERAGE('Period 1'!L4:L43,'Period 2'!L4:L43,'Period 3'!L4:L43,'Period 4'!L4:L43,'Period 5'!L4:L43)

Im assuming the problem is because the above formula is recognising the LN function rather than the final value??

5. ## Re: #NUM error when trying to average cells that also contain a formula

Just got your reply Tony and your formula gets rid of the error but I don't really have any idea what any of it means....
Is it still finding the average?

If so then its gret, Thanks!!

6. ## Re: #NUM error when trying to average cells that also contain a formula

Yes, it gets the average in a roundabout way that ignores the errors in any of the ranges.

It gets the sum of all the ranges and then divides by the numeric count of all the ranges.

7. ## Re: #NUM error when trying to average cells that also contain a formula

Great! Have been doing a bit of QA and it all works perfectly.
Thanks for the help!!

8. ## Re: #NUM error when trying to average cells that also contain a formula

You're welcome. Thanks for the feedback!

9. ## Re: #NUM error when trying to average cells that also contain a formula

Originally Posted by Tony Valko
You should try to fix the errors as Vlady noted.

However, just for the heck of it...

You could try this:

=SUMPRODUCT(SUMIF(INDIRECT("'Period "&{1,2,3,4,5}&"'!L4:L43"),"<1E100"))/COUNT('Period 1'!L4:L43,'Period 2'!L4:L43,'Period 3'!L4:L43,'Period 4'!L4:L43,'Period 5'!L4:L43)
Improvement.

We can reduce that to:

=SUMPRODUCT(SUMIF(INDIRECT("'Period "&{1,2,3,4,5}&"'!L4:L43"),"<1E100"))/COUNT('Period 1:Period 5'!L4:L43)

There are currently 1 users browsing this thread. (0 members and 1 guests)