Convert Variance to a Percentage Using SUMPRODUCT

nflsales kindly came up with a formula to calculate the difference between two sets of columns:

Please Login or Register  to view this content.

I now need to convert this to a percentage. Normally this would simply be

Please Login or Register  to view this content.

for example if '$BK2:$BW2' = 40 and '$AF2:$AR2' = 10, the variance would be (10-40)/40 = 75%

so I tried

Please Login or Register  to view this content.

but that doesn't work

Does anyone know how to to this?

Many thanks

Can you send me a single file?
Regards

Hi José

File attached. In place in column AA is a SUMPRODUCT formula that calculates the variance between the current month YTD (determined by there being no entries beyond the current month) and the Forecast YTD, so in the attached example the current month is month 3 because there are no entires in month 4 (column Q). Note however there can be zero entries in the current month (see Month 2 and 3, columns O and P).

The challenge is how to adapt the SUPPRODUCT formula in column AA to produce the variance as a percentage in column AB. The expected answers appear in column AD.

Many thanks

I should have added that if there is an alternative to using SUMPRODUCT to achieve the same thing, that is also good.

Yes. Replace SUMPRODUCT by SUM.

Hi José,

THanks for your help, sadly that doesn't work! I'm going to repost and see if anyone else can chip in...