I know, I know. How the tables have turned. Literally!

I havent used Excel for years and in that time it appears MS have joined the 21st century and put excel online. Bravo.

I want to do some analysis which I used to do all the time in Excel, but not for many years and so have never done it in Google Sheets and not sure I can even do it without writing scripts.

In Excel:
Assume a simple function:
Variable X = x
Variable Y = y
Z = X*Y

I used to be able to “Insert Data Table” where I’d enter in the tops of each column an array of x values and the left of each row an array of y values. At the “1,1” of the table in the gap between these two arrays would be a reference to a formula that used those variables (=X*Y) in this instance.

Excel would then ask which set of variables to substitute for “X” and which for “Y” (you then select the correct arrays. The undo and select the other set of arrays because I always got that wrong first time) and it would populate the grid with all the values based on those variables and the formula, and it would maintain that calculation.

How do I do this in Excel online as everything has moved or disappeared?

Ah, I think I may have found why I cant…

Because add-in programs aren’t supported in Excel Online, you won’t be able to use the Solver add-in to run what-if analysis on your data to help you find optimal solutions.

If you have the Excel desktop application, you can use the Open in Excel button to open your workbook to use the Solver add-in there.

Perhaps tell us what you are actually trying to do and we can point you in the right direction.

Are you trying to fit a function to a series of data?
Is there a reason you really want to use excel?

for simple example as you said I’d use the $ sign to lock either the row or the column as shown in example below.

= $B1 * A$2

Copy paste over the area you’re after will keep some elements fixed but keep some elements relative. Ie fix the column on one elemt but fix the row on the other.

For fancier formulas that the addin may help with this may be too basic

Are you talking about array formulas ?

not array formulae.

The functionality was in the desktop version of Excel, under “What if Anaylsis” and DATA TABLE.

It turns out that isnt an add on made available for online Excel which is the only Excel I have access to.

Fortunatley a very clever chappie has created a script to mimic the data table function using a macro script that edits, copies and pastes within google sheets so I can get roughly the same kind of performance.
https://chrome.google.com/webstore/detail/sensitivity-macro/kdmhkpflemcfpkkmaajfpggfhoeeffie?utm_source=permalink

I have my data table now
cheers