Question description:
This user has given permission to use the problem statement for this
blog.
I am having issues creating the one way data table to show the means and standard deviations when you change the proportion of portfolio 1. I have the mean formula and the standard deviation formula at the top and -1 through 1 down the left side. I highlight all of it and create a data table. I reference the "proportion of portfolio 1" cell in the column input box but when the data table comes out it has all the same numbers all they way down the table.
Solved by G. Q. in 49 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
06/04/2018 - 11:39
Hello?
Excelchat Expert
06/04/2018 - 11:39
hello!
Excelchat Expert
06/04/2018 - 11:39
Good day! How are you?
User
06/04/2018 - 11:39
frustrated to be honest
User
06/04/2018 - 11:39
can't get this data table to work
Excelchat Expert
06/04/2018 - 11:39
It's okay, i'll do my best to help you
Excelchat Expert
06/04/2018 - 11:40
are you able to provide me a sample data with could work with on the document preview on the right?
User
06/04/2018 - 11:40
yes one second
Excelchat Expert
06/04/2018 - 11:42
sure, let me know if you're done. Would appreciate if you could describe to me what needs to be achieved?
User
06/04/2018 - 11:44
I have two stock portfolios with different expected returns and variances. I created formulas to calculate the mean and standard deviation when 54% of the money is in portfolio 1 and 46% is in portfolio 2. I need to create a one way data table to show the mean and std. dev. for when i change the percentages of how much money is in each portfolio.
User
06/04/2018 - 11:45
-1 means no money in portfolio 1 and all of it portfolio 2
Excelchat Expert
06/04/2018 - 11:45
got it
Excelchat Expert
06/04/2018 - 11:46
are you able to input the actual formula to compute the mean and std. dev.? this is required to be able to create our one way data table
User
06/04/2018 - 11:46
yes
Excelchat Expert
06/04/2018 - 11:46
cool!
Excelchat Expert
06/04/2018 - 11:48
do you mean by =C38*C1+C4*C2 > =C3*C1+C4*C2?
Excelchat Expert
06/04/2018 - 11:49
you could actually use AVERAGE.WEIGHTED() to achieve a similar but more intuitive and fail-safe result for this
User
06/04/2018 - 11:50
How do i create a data table?
Excelchat Expert
06/04/2018 - 11:51
okay, give me a moment
User
06/04/2018 - 11:51
I want substitute cell C3 with B8-B28
User
06/04/2018 - 11:53
when i do it i get the same values in cells C7 and D7 all the way down the table
Excelchat Expert
06/04/2018 - 11:53
okay, noted on this. will get back to you asap
User
06/04/2018 - 11:55
I highlighted B7:D28 and do a data table with cell C3 in the column input and it gives me the same numbers all the way down
Excelchat Expert
06/04/2018 - 11:57
I had to do it offline because I can't seem to find the data table function via Google Sheets
[Uploaded an Excel file]
Excelchat Expert
06/04/2018 - 11:57
please see attached
Excelchat Expert
06/04/2018 - 11:58
I made a few changes - to show that Proportion 2 = 100% - Proportion 1
Excelchat Expert
06/04/2018 - 11:58
The result should look like with the data on the left. feel free to open the excel file i sent you to see the actual data table
Excelchat Expert
06/04/2018 - 11:59
The result should look like with the data on the *PREVIEW ON THE RIGHT. feel free to open the excel file i sent you to see the actual data table
User
07/04/2018 - 12:00
What happened to the equations
Excelchat Expert
07/04/2018 - 12:03
I'm sorry i had to refresh my connection
Excelchat Expert
07/04/2018 - 12:03
please download the attachment to my message earlier.
Excelchat Expert
07/04/2018 - 12:04
I had to do it offline on my Excel because I can't seem to find the data table function via Google Sheets
User
07/04/2018 - 12:04
Okay. What did you enter into the input cells?
User
07/04/2018 - 12:05
And what cells did you highlight before clicking Data table
Excelchat Expert
07/04/2018 - 12:05
okay so follow these steps:
Excelchat Expert
07/04/2018 - 12:06
step1: highlight cells B10:C20 and select Data>What-If-Analysis>DataTable...
Excelchat Expert
07/04/2018 - 12:07
step 2: input in Column Input Cell =C3
Excelchat Expert
07/04/2018 - 12:07
that is for the Mean analysis
User
07/04/2018 - 12:08
That is what i am doing and it puts the same number all the way down.
Excelchat Expert
07/04/2018 - 12:08
uhm, were you able to download the file i sent you?
User
07/04/2018 - 12:08
can you do a table for the mean and Standard dev at the same time.
User
07/04/2018 - 12:08
yes i am looking at it
Excelchat Expert
07/04/2018 - 12:08
they cannot be done at the same time, but you can create a table to combine the results.
Excelchat Expert
07/04/2018 - 12:08
let me resend the file
Excelchat Expert
07/04/2018 - 12:09
Here you go
[Uploaded an Excel file]
User
07/04/2018 - 12:10
When i did exactly what you said it turned all the numbers into .11
Excelchat Expert
07/04/2018 - 12:10
try to hit F9 to refresh calculations
Excelchat Expert
07/04/2018 - 12:12
any success so far?
User
07/04/2018 - 12:12
When i highlight cells b10:c20 go to data table and put $c$3 in the column input cell and hit enter, all the numbers change to .11 and even all of the standard dev. numbers turn to .078740079
User
07/04/2018 - 12:12
Maybe it is because I am on a Mac?
Excelchat Expert
07/04/2018 - 12:13
can you please check if c10 is linked to c6, and f10 is linked to c7
Excelchat Expert
07/04/2018 - 12:13
i've done it before in Mac and i remember it to work in my case
User
07/04/2018 - 12:13
they are
User
07/04/2018 - 12:14
it has worked for me on previous projects
Excelchat Expert
07/04/2018 - 12:14
That's pretty unusal. try to restart your excel?
Excelchat Expert
07/04/2018 - 12:14
Troubleshooting excel is really not my cup of tea, i'm sorry
User
07/04/2018 - 12:16
that did not work
Excelchat Expert
07/04/2018 - 12:17
Use the Formula->Trace Dependents to check what cells are linking to your input cells. Make sure all cells that are supposed to link to your input cell is correct. Then go to each cell to make sure the linking and the formula is correct.
User
07/04/2018 - 12:19
How do i use that?
Excelchat Expert
07/04/2018 - 12:19
or you can click Formula>Show Formulas
Excelchat Expert
07/04/2018 - 12:19
try to compare the results if the same with this image i attached
[Uploaded an Excel file]
User
07/04/2018 - 12:20
they all look right
Excelchat Expert
07/04/2018 - 12:21
oh my,
Excelchat Expert
07/04/2018 - 12:21
I'm out of suggestions
User
07/04/2018 - 12:22
okay well thank you
Excelchat Expert
07/04/2018 - 12:22
well you can always try to do it manually
Excelchat Expert
07/04/2018 - 12:22
instead of using data table
User
07/04/2018 - 12:22
Its for a project and I have to use data table
Excelchat Expert
07/04/2018 - 12:23
i'm sorry to hear that
User
07/04/2018 - 12:23
it wont let me attach the actual file to this
Excelchat Expert
07/04/2018 - 12:24
maybe because it's on xlsm format?
Excelchat Expert
07/04/2018 - 12:24
can you check the extension of your excel file?
Excelchat Expert
07/04/2018 - 12:25
here's also a link that might help you with the troubleshooting
Excelchat Expert
07/04/2018 - 12:25
https://excelhelphq.com/why-your-excel-data-table-does-not-work-and-how-you-can-fix/
User
07/04/2018 - 12:25
I solved it
Excelchat Expert
07/04/2018 - 12:25
great! how did you do it?
User
07/04/2018 - 12:25
my calculation options was set to automatic besides data tables
Excelchat Expert
07/04/2018 - 12:26
that's a relief
User
07/04/2018 - 12:26
I guess thats a default setting i didn't know about
Excelchat Expert
07/04/2018 - 12:26
Oh yes, there's that
Excelchat Expert
07/04/2018 - 12:27
is it okay if we mark this problem ANSWERED?
User
07/04/2018 - 12:27
yes
Excelchat Expert
07/04/2018 - 12:27
or you need anything else?
User
07/04/2018 - 12:27
thank you
Excelchat Expert
07/04/2018 - 12:27
You're most welcome. You're very cool having found out that part which I honestly missed.
Excelchat Expert
07/04/2018 - 12:28
Please feel free to end this session. Any rating and feedback is greatly appreciated
User
07/04/2018 - 12:28
thanks haha only if i had figured it out 2 hours ago...
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
