Excel - COLUMN Function Problem - Expert Solution

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
Excelchat Expert 06/04/2018 - 11:39
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
Excelchat Expert 06/04/2018 - 11:46
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
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
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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc