**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.*