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...

Charting

excel

Formulas and Functions

JoeUser2004
Mar 29, 2023
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.

JoeUser2004

Bronze Contributor

Mar 29, 2023

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

Copper Contributor

Mar 30, 2023

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?!

Forum Discussion

Multiplication and Division formula Excel '#Value' error

Resources