Disregard Changing Null Values so Average can be Taken

Hello,

I am working with a dynamic data set from which I need to calculate averages.

After downloading the "Example" worksheet", you will see that in Sheet2 I have two sets of data. This is from a database that I periodically load. At the moment, there are no values for "Grapes" or "Bananas". Consequently, I cannot take the average quality of nothing (see Sheet1). Only when I comment out the ".AverageIfs(a, b, "Grapes/Bananas")" in my VBA code does it work.

However, I cannot do this forever because I later on might have Grapes or Bananas values.
I have tried using:

Please Login or Register  to view this content.

I have tried a similar IfError statement in an Excel cell and it worked.

I would appreciate any ideas to skip the null value SOMETIMES. And, if I would love ideas that do not resort to coding around every single fruit.

Thanks!
Kevin

It would be worth reconsidering the approach but to answer the specific question...

Please Login or Register  to view this content.

Per the earlier note - you should revise your approach.

If the criteria values in the Summary table reflect the values in the Source table (ie consistent) you can just apply the formulae to the ranges en masse and subsequently apply results.

Using your sample file:

Please Login or Register  to view this content.

Your VBA is then much simplified:

Please Login or Register  to view this content.

though why you're using VBA rather than just using formulae in cells escapes me given the fruit listing is not dynamic and the values in D are themselves populated by in cell formulae

Anyway, I hope the above is of some use...

Originally Posted by kchen

I hope my situation makes more sense.

To an extent - but the point would be the formulae you're using are not hugely expensive and are seemingly relatively limited in number (ie per "fruit").
Leaving the formulae in place and referencing either the source columns in their entirety or using a Dynamic Named Range should not have too adverse an effect on performance.

If you're saying you want the figures to remain unchanged when the source data updates (until such time as you invoke the sub routine) then there is obvious merit in avoiding in cell formulae.
However, even then from a code perspective it's better (IMO) to calculate the range en masse via formulae, subsequently override the formulae with the results thereof, at which point the values become "static".
(this being the approach outlined in the example code)

Hey DonkeyOte,

If you're saying you want the figures to remain unchanged when the source data updates (until such time as you invoke the sub routine) then there is obvious merit in avoiding in cell formulae.

Yup, that's pretty much why I wanted to avoid cell formulae.

However, I have gone ahead and tried it your way. It works for most cells, I am still figuring out some kinks. But, I think it should be good.

At the same time, I would like to know why I am unable to use something like this (I have tried all variations:

Please Login or Register  to view this content.

Thanks!