Sep 14 2022 04:49 AM
Sep 14 2022 04:49 AM
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|
Sep 14 2022 05:02 AM
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.
Sep 14 2022 08:41 AM - edited Sep 14 2022 09:00 AM
@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