Level 1

8 points

Numbers arrays or ifstatement

I am working with a form of numerology where letters of the alphabet have numerical values. At the moment I am manually typing the equivalent number next to the letter and then doing the math. I know that in Excel you can make use of if statements and arrays to process this automatically, but I can't find a way to do this in Numbers and from the posts that I read it looks like Numbers does not support arrays.

In my manual approach rows A to D are my reference rows. I type the letters I need in row F and then manually feed in the appropriate value into row G.

What I am trying to create is to remove the reference rows and just be able to type the letters and for Numbers to automatically fill in the appropriate number into the next column. Is this possible?

MacOS Sierra 10.12.3

Numbers 4.0.5

MacBook Pro, macOS Sierra (10.12.3)

Posted on Mar 9, 2017 10:14 PM

26 replies

Barry

Level 9

60,023 points

Mar 11, 2017 10:33 PM in response to markusvdw

"…for some reason it didn't work with commas (,) but with semicolons (;)"

Explained in one of my recent posts—

the list separator is a comma in regions where the decimal separator is a period

the list separator is a semi colon in regions where the decimal separator is a comma

Sample functions:

Where twelve euro and 34 cents is written: €12,34—> MATCH(A1;A;0)

Where twelve dollars and thirty four cents is written: $12.34—> MATCH(A1,A,0)

Regards,

Barry

Level 9

60,023 points

Mar 12, 2017 4:39 AM in response to markusvdw

Hi Mark,

Missed this one which came in while I was composing the post above.

Suggestion: When developing a formula, and posting here, please turn OFF the Numbers preference setting to "Use header names as labels" choice. Column row references by letter and row references by number are always at the same location in the table, making them easier to find when trouble shooting. A mix of both naming conventions (as demonstrated in your formula above brings on an 'a-a-a-c-c-k!' moment. 😉

Looking back to my original formula: B2: =IF(LEN(A)<1,"",INDEX(Lookup::B,MATCH(A2,Lookup::A,0)))

The formula needs one change to make it work for your region and your tables—replace the commas with semi colons

IF(LEN(A)<1;"";INDEX(Lookup::B;MATCH(A2;Lookup::A;0)))

While my preference would be for the cleaner look of 'blank' cells where there is an empty cell in column A, and this was the purpose of the IF(LEN(A)<1;"";main formula) wrapper, you can place a zero there by replacing the null string ( "" ) with a zero:

IF(LEN(A)<1;0;INDEX(Lookup::B;MATCH(A2;Lookup::A;0)))

See my first reply for an example using this formula.

Regrds,

Barry

markusvdw Author

Level 1

8 points

Mar 12, 2017 11:56 PM in response to Barry

Wow Barry! Thank you for your patience and guidance! I found it very frustrating that Numbers used the header names... didn't realise there was a preference (which is now switched off). The formula works PERFECTLY!

THANK YOU A MILLION TIMES! 😍

Barry

Level 9

60,023 points

Mar 13, 2017 12:06 AM in response to markusvdw

Hi Markus,

You're welcome.

A thank you is always appreciated, as is the news that a solution arrived at here has been useful to the person asking the question.

Call again when you need further assistance.

Regards,

Barry

Level 9

60,023 points

Mar 13, 2017 2:10 PM in response to markusvdw

Hi Mark,

Images pasted into a cell become "image fill" in that cell. The data (text, numbers) in a cell are 'content' and can be copied and transferred using formulas. "Fill," whether a colour, a gradient, or an image, is 'formatting' applied to, rather than placed in, the cell. Formatting is not 'content,' and cannot be manipulated by formulas.

You may be able to find the symbols you are using in a custom set of unicode-coded characters. If so, the characters would be 'content' and could be placed into cells. A query in a numerology discussion group, or an internet search might uncover a source for such a font. (I did get several hits for numerology software on a quick search, but none from sites I recognized.)

Regards,

Barry

markusvdw Author

Level 1

8 points

Mar 11, 2017 2:19 AM in response to Barry

I still get the error.

markusvdw Author

Level 1

8 points

Mar 9, 2017 11:34 PM in response to To_Mi

Thank you for this. I have tried this, but I don't get exact match... Numbers only allow me to input EXACT... I also get ; instead of , between the bits of information.

To_Mi

Level 4

1,202 points

Mar 10, 2017 11:00 PM in response to markusvdw

Remove "B2:" at the top of the formula, which should begin with IF.

Barry wrote the formula as "B2 := …" to indicate it is for cell B2.

Barry

Level 9

60,023 points

Mar 11, 2017 1:06 AM in response to markusvdw

Hi Mark,

To_Me caught it.

B2: tells you where to enter the formula

= is what you type to open the formula editor

everything after = is the formula itself.

Regards,

Barry

markusvdw Author

Level 1

8 points

Mar 11, 2017 9:37 PM in response to To_Mi

Barry, this one also works! I just changed the error from a - to a 0. Thank you, thank you, thank you!!!

Yellowbox

Level 7

21,939 points

Mar 12, 2017 5:06 AM in response to To_Mi

Hi To_Mi,

I admire your replies. Please keep posting in Numbers for Mac 🙂.

Regards,

Ian.

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

Numbers arrays or ifstatement