new formula in numbers 5.1 greyed out

Question

new formula in numbers 5.1 greyed out

Trying to calculate hundreds of STDEVs for a pilot study. But even doing one is proving awful.

The instructions on the Apple guide to Numbers say to click on the + on the toolbar and a dropdown will appear where you can add a function. No, it's just greyed out. There are a handful of default simple functions and nothing else is accessible.

So I've been hand typing (or copy-pasting) =STDEV(x2:x25) etc into each and every cell in which I want the SD for the range of cells in the column above it. But I have literally hundreds to do and a tight deadline.

First question is: Why is New Formula greyed out in the + dropdown?

Next question is: Is it possible to do multiple calculations like you can in Excel, by making the formula apply to a whole series of rows or columns of data points? That just needed one click in each results cell and the STDEV would be there immediately.

iMac 27″ 5K, macOS 10.12

Posted on Aug 1, 2020 2:13 AM

Reply

Answer 1

Best reply

Badunit

Level 7

26,620 points

Aug 1, 2020 7:16 PM in response to diannetrussell

If you have one or more cells selected, at the bottom of the Numbers window are a few functions that you can drag and drop in a table. You can add STDEV to that list by clicking on the gear-looking icon down there. Unlike Excel, you will have to select the data but, once you have done that, you can drag the STDEV and place it where you want in the table. The formula will get pasted, not just the numeric result.

Reply

Answer 2

Aug 1, 2020 7:00 PM in response to Badunit

Thanks - I did eventually get the New Formula to show, but it had to be in a cell that's never been used for a formula before, and deleting the cell did not seem to clear it. Anyway, I've now got enough to work with.

Re Excel, the last version I had, you could do this:

a bunch of data columns, each with many data rows
I wanted the STDEV of every column (say all rows in column B to all rows in column G)
in the next blank row right below the data-filled rows all I had to do was click a cell, click STDEV on the righthand sidebar and the value was immediately filled without having to type in row and column numbers. It was fast.

Reply

Answer 3

Badunit

Level 7

26,620 points

Aug 1, 2020 6:48 PM in response to diannetrussell

I don't know why you are having this problem. If I have selected a cell in a table, "new formula" and the several default formulas are available in that menu. If I have not selected a cell, all are greyed out.

An easier way is to select a cell and press the = key. You can then either type in the formula or choose it (double click on it) in the sidebar on the right. But if you are having problems getting that to work with the "insert" icon, I don't know if this is going to work either. Please let us know.

Can you describe in more detail what you would do in Excel to "do multiple calculations" and "make it apply to a whole series of columns or data points" with one click? I'm not sure what you are talking about so I cannot say if Numbers has the equivalent or not.

Reply

Answer 4

Aug 1, 2020 10:41 PM in response to Badunit

Oh Badunit you GoodUnit! That works beautifully and no more headaches! I like being able to select just the data I want, as I usually have many tables within my table and need to be free of the 'last row' effect.

(If Apple discussions had emojis I'd give you hearts and thumbs up!)

Reply

Answer 5

Barry

Level 9

59,885 points

Aug 2, 2020 1:59 AM in response to diannetrussell

Hi dianne,

"I usually have many tables within my table and need to be free of the 'last row' effect."

In Numbers, those 'tables within tables' would be separate tables on a Sheet, and could be placed and moved around as desired.

"(If Apple discussions had emojis I'd give you hearts and thumbs up!)"

👍❤️💖❤️

Like these?

You'll find them in the Character Viewer (re-named a while back as "Emojis and Symbols." Drag from the viewer and drop on your message (or into a document).

Regards,

Barry

Reply

Answer 6

Aug 2, 2020 3:16 AM in response to Barry

Thanks Barry,

Each table that contains tables is for each participant in a study I'm doing. They need to be on one table so my supervisor can scope the full data all at once, and so we can do multivariate analyses. But now that I have BadUnit's method, it's no longer a problem.

Thanks for the emojis tip!

❤️ 😊

Reply

Answer 7

Yellowbox

Level 7

20,501 points

Aug 2, 2020 4:14 AM in response to diannetrussell

Hi Dianne,

If your supervisor is using Excel, the usual advice in this forum is to use Excel to avoid the inevitable import/export glitches between Numbers and Excel. Perhaps Barry or Badunit can advise you on the choice between the various (free with a donation) Excel look-alikes.

Good luck with your thesis! 👍

Regards,

Ian.

Reply

Answer 8

SGIII

Level 8

35,504 points

Aug 1, 2020 6:43 AM in response to diannetrussell

Not sure what "Apple guide" you are referring to, but in your menu I suggest looking at Help > Numbers Help to get a quick overview of how to do things in Numbers. Search for 'Autofill'.

SG

Reply