Multiplication of percentage & currency returning incorrect answers

Copper Contributor

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 1Portfolio 2Combined Portfolios    
 $264,705$50,000$314,705    
ACCT 118.0%21.7%18.6%$58,491 CHECK$58,535.15
ACCT 236.3%7.2%31.7%$99,785   
ACCT 30.0%8.1%1.3%$4,025   
TOTAL54.3%36.9%51.6%$162,302   
INV 130.6%28.4%30.2%$95,069   
INV 29.8%28.6%12.8%$40,268   
INV 30.4%6.1%1.3%$4,219   
TOTAL40.8%63.1%44.3%$139,557   
PROPERTY4.9%0.0%4.1%$12,941   
TOTAL4.9%0.0%4.1%$12,941   
        
 100%100%100%$314,800   
        
2 Replies

@mariareyes 

If you cannot attach a workbook to a post or reply, upload it to a cloud service such as OneDrive, Google Drive or Dropbox, obtain a link to the uploaded workbook and post the link in a reply.

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

 

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

 

If the forum does not allow that (yet), upload the Excel file to a file-sharing website, and post the download URL in a reponse here.  I like box.net/files; others like dropbox.com.  You might like onedrive.live.com because it uses the same login as this forum.  In any case, be sure that the link that you provide does not require that we log in to the file-sharing website and that anyone can download the file using that link.  In the case of onedrive, also be sure that the link does not allow us to edit the file.

 

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