Forum Discussion
Multiplication and Division formula Excel '#Value' error
Dear All!
I'm trying to create a trackable percentage increase in a spreadsheet, i am following a tutorial which uses the following formula:
=SUM(ITEMS_PERCENT*ITEMS_WORKDAYS)/SUM(ITEMS_WORKDAYS
in terms of the spreadsheet that is
=SUM(%field*workdays)/SUM(workdays)
OR
=SUM(W16:W19*R16:R19)/SUM(R16:R19)
however, I get a #VALUE! error.
I'm not very good at excel (hence the tutorial!) but I can't work out where it is going wrong or whether something using =PRODUCT would work too?
All advice sought!
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.
4 Replies
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?!
- Thank you! I have since discovered i needed to array the formula!
