Forum Discussion
Multiplication and Division formula Excel '#Value' error
potofteatohavein wrote: =SUM(W16:W19*R16:R19)/SUM(R16:R19)
It is possible that in your version of Excel, you must array-enter the formula. That is, press ctrl+shift+Enter instead of just Enter.
But a better solution is:
=SUMPRODUCT(W16:W19, R16:R19) / SUM(R16:R19)
which is entered by pressing just Enter as usual.
For all the esoteric uses of SUMPRODUCT, this is exactly what the function was designed for.
potofteatohavein wrote: =SUM(W16:W19*R16:R19)/SUM(R16:R19)
It is possible that in your version of Excel, you must array-enter the formula. That is, press ctrl+shift+Enter instead of just Enter.
But a better solution is:
=SUMPRODUCT(W16:W19, R16:R19) / SUM(R16:R19)
which is entered by pressing just Enter as usual.
For all the esoteric uses of SUMPRODUCT, this is exactly what the function was designed for.
- Thank you Joe User, the ctrl+shift+enter worked (as well as your formula suggestion!)
You have saved me hours of anguish! - how did you even know this was a thing?!
