Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

# Multiplication of percentage & currency returning incorrect answers

Copper Contributor

# Multiplication of percentage & currency returning incorrect answers

Hi, I have recreated my table with just simple data but still getting the incorrect answer. Column E results are wrong. Can anyone please assist? Thank you

Not sure how to attach my file to this post.

 Portfolio 1 Portfolio 2 Combined Portfolios \$264,705 \$50,000 \$314,705 ACCT 1 18.0% 21.7% 18.6% \$58,491 CHECK \$58,535.15 ACCT 2 36.3% 7.2% 31.7% \$99,785 ACCT 3 0.0% 8.1% 1.3% \$4,025 TOTAL 54.3% 36.9% 51.6% \$162,302 INV 1 30.6% 28.4% 30.2% \$95,069 INV 2 9.8% 28.6% 12.8% \$40,268 INV 3 0.4% 6.1% 1.3% \$4,219 TOTAL 40.8% 63.1% 44.3% \$139,557 PROPERTY 4.9% 0.0% 4.1% \$12,941 TOTAL 4.9% 0.0% 4.1% \$12,941 100% 100% 100% \$314,800
2 Replies

# Re: Multiplication of percentage & currency returning incorrect answers

@mariareyes  wrote:  ``Not sure how to attach my file to this post``

Click "browse" near the bottom of the post or reply window.

IMHO, do not use Google Drive, and especially Google Docs.  In general, I worry that Google might change some details of the files.

-----

From the data that you posted, it is difficult to know what values are facts and what values are calculated, perhaps incorrectly.

Note that 21.70%, 7.20% and 8.10% (C3:C5) sum to 37.00%, not 36.90%.  So I suspect a rounding error in the recording (presentation), not in the calculation, of some numbers.

For example, if \$58,491 (sic) in E3 is supposed to be \$58,535.15, which is indeed about 18.60% of \$314,705, then perhaps B3 and C3 should be about 18.02135% and 21.66348% respectively.

Those percentages were estimated using Solver.  Other pairs of percentages are possible.  Also note that we must use the unrounded percentages -- or rounded as shown for these examples -- in order to calculate the expected result.  But the point is....

Note that they round to 18.0% and 21.7%. So I suspect that your recorded percentages were actually rounded to 1 percentage decimal place, not 2, for presentation.

We can apply the same analysis to the rest of your data if and when you provide additional "check" amounts.

But I suspect that if and when we do, that will explain why your calculations are so different.

The formulas in column D should be of the form (e.g. D3):

=SUMPRODUCT(B3:C3, B\$2:C\$2) / \$D\$2

And the formulas in column E should be of the form (e.g. E3):

=D3 * \$D\$2