• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Problem With Averageif

C

Certificates!

Member
I'm having a problem with an Average formula.

If I key this I get a Value error: AVERAGEIF('8 April 2023:25 April 2023'!H9,"<>0")

If I just use the straight average, AVERAGE('8 April 2023:25 April 2023'!H9) it works fine.

I do need to exclude the zeros from the average though. Teh formula looks fine, but all I have is numbers in the cells.

I have tried testing this by using =ISNUMBER and when I look at each cell individually I get TRUE , but if I use =ISNUMBER('8 April 2023:25 April 2023'!H9) I get FALSE.

I'm confused. I can post a worksheet if required but I wonder first if I am just misunderstanding how the formulae work
 
Thanks for that. So neither AVERAGEIF or ISNUMBER work under these circumstances.

Can you suggest a method of doing what I should like to do please? Average a specific cell over a number of worksheets excluding zero.

Thank you.
 
Certificates!
You replied that ... it can be ... I tried to get an answer ... what is?
Has there been any reason - why do You try to use those Average functions with one cell?
Isn't Average always same value as it can be? ... eg with 1, it'll be 1.
What do You really would like to solve?
 
So far I have 12 worksheets. At the end of the year there will be about 100.

In cell h9 i have the following numbers, 4,5,5,3,0,4,2,0,6,7,4,5

I want to average the numbers excluding the zeros.

With twelve sheets is just about practical to use Countif'Sheet1'!h9<>0,+Countif'Sheet2'!h9<>0,+ etc, but for 100 it wouldn't be.
 
p45cal
I see ... but then that range have to be more than 99% correct too.
It would be okay ... if someone would like to use something like that.
 
Certificates!
So far I have 12 worksheets. At the end of the year there will be about 100.
Why do You've 12 worksheets? ... and few worksheets more later?
Could You have one worksheet?
About Average for me ...
... if there could be values 1 and 2 then Average is 1.5
... if there could be values 0 and 2 then Average is 1
... if there could be ... empty ... and 2 then Average is 2
... 0 (zero) could be as valid value as any other value
 
I have been doing some googling and this seems to do the trick.

I have added the Rounding: =ROUND(SUM('8 April 2023:25 April 2023'!H17)/INDEX(FREQUENCY('8 April 2023:Last'!H17,0),2),1)

Thanks for your efforts anyway.
 
You must log in or register to reply here.
Back
Top