Average works, subtotal on same range gives #DIV/0! error

[kcmuppet]

Hello

Does anybody have any idea why

=Subtotal(101, range)

would give #DIV/0! error, when

=Average(range)

works (i.e. returns a number) on exactly the same range?

 

[jasonb75]

I've tried with and without tables using all 3 formulas and a variety of cell formats, for me the results are consistent with all of them, either value or error depending on the content of the cell, but never a different result for one formula.

As I said in post 5, this is something that will be practically impossible to diagnose without the original data.

 

[kcmuppet]

I've tried with and without tables using all 3 formulas and a variety of cell formats, for me the results are consistent with all of them, either value or error depending on the content of the cell, but never a different result for one formula.

As I said in post 5, this is something that will be practically impossible to diagnose without the original data.

I've never seen it vary either. Unfortunately the data is confidential patient records so I can post here, but will update the thread if I ever find out what's going on. Meanwhile I'll switch to Aggregate instead of Subtotal

 

[jasonb75]

The only thing I've seen close to it was when the data used circular references, even if you did have them the test with the extra column of subtotal formulas checking each row should have returned the same value as the cell in that row rather than an error because it would be out of the circle.