Excel formula -> how to change SUMPRODUCT formula to skip null cells -


context is using adjusted sumproduct formula calculate weighted average.

problem is sumproduct includes empty cells in arrays. => weighted average calculated incorrectly.

question is how edit sumproduct exclude empty cells in arrays?

alternatively there clean , neat solution?

in sumproduct, exclude empty cells, suppose you're using on range a1:a100, following:

= sumproduct((a1:a100),--(a1:a100<>"")) 

that second criteria ensure you're looking @ cells have value in them...

as explanation (a1:a100<>"") return array of true false, where, if there value in cell, returns true, otherwise, false. then, including -- before it, converts true/false 1/0. so, in effect, you're multiplying empty cells 0 (excluding formula) , non-empty cells 1 (including them in formula).

the --(logical statement array) useful trick use sumproduct() in many different ways!!


Comments

Popular posts from this blog

How to remove text and logo OR add Overflow on Android ActionBar using AppCompat on API 8? -

html - How to style widget with post count different than without post count -

url rewriting - How to redirect a http POST with urlrewritefilter -