Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Question:

Question: Numbers for mac will not let me add more than 15 conditional highlighting rules

Numbers 4.3.1 (5249) for High Sierra 10.13.3 running on a Mac Pro (Late 2013) 3.5GHz 6 Processor 32GB.


I was typing in 18 rules, and it let me, but rule #16 would not execute. I deleted all rules and started over. When I hit rule #15 it would not respond when I tried to add another rule. Nothing complicated about the rules, just set a cell color when the number is between two numbers. It creates a color contour map. What could cause this behavior? It feels like the program hit a bug and perhaps corrupted the internal database. Is this a known bug?

Mac Pro, Mac OS X (10.6.7)

Posted on

Reply

Page content loaded

Mar 16, 2018 7:12 PM in response to see3d In response to see3d

Hi see'


A limitation is not necessarily a bug.


15 is 2^4-1. Omit the 0, and there are sufficient values from 0001 to 1111 (binary) to index 15 rules.


My interpretation would be that someone asked during the design stage, 'How many CH rules do we need?' and the response was 'Fewer than 20' or 'Fewer than two dozen' and the decision was made that it would be as many as could be indexed using a half-byte.


Regards,

Barry


PS: If you think it needs to be expanded, you need to talk to Apple. Here, you're talkig with users of the software, not developers of the software. Use the Provide Numbers Feedbak item in the Numbers menu to send a Feature Enhancement request to Apple.

B.

Mar 16, 2018 7:12 PM

Reply Helpful

Mar 16, 2018 7:52 PM in response to Barry In response to Barry

Thanks for the response, but this is not a lack of a feature. I have typed in 20 rules before, so that is not a limitation. I have 18 rules right now, but rule 16 does not work. If I delete all rules and start over, then it won't take more than 15. I tried to export it to .xlsx and it does not support the kind of rules I have and only exports 10 rules. I tried exporting to Numbers (09) and it exports the bug with it or when reading it back in. This is so messed up. I am just trying to figure out how to save all this work and recover it now.

Mar 16, 2018 7:52 PM

Reply Helpful

Mar 16, 2018 9:43 PM in response to see3d In response to see3d

Hi see3d,


You can reduce the number of Conditional Highlight rules if you do a lookup first.

User uploaded file

Formula in Contour map B2 (and Fill right and Fill Down)

=VLOOKUP(Data::B2,Lookup::$A:$B,2,close-match)


Close-match allows for non-integer data.

The Header Row and Header Column allow labels for (say) latitude and longitude.


Now apply 8 (in this example) Conditional Highlighting rules to colour the text and cell fill:

User uploaded file

Regards,

Ian.

Mar 16, 2018 9:43 PM

Reply Helpful

Mar 17, 2018 4:49 AM in response to Yellowbox In response to Yellowbox

Interesting concept. I am trying to figure out how I can use it. I have 18 different colors, each corresponds to a different number range. So, total range is about -0.5 to +0.5 and a different color for each 0.05 increment. Each color is actually evenly spaced around the color wheel, but I don't think that helps the problem. I can obviously make a table of numbers and corresponding colors, but how can I make a rule that uses it?

Mar 17, 2018 4:49 AM

Reply Helpful

Mar 17, 2018 7:11 AM in response to see3d In response to see3d

Hi see3d,


Please post a screen shot (or better, copy and paste) an example of your data table to help us see what you see.

I have 18 different colors, each corresponds to a different number range. So, total range is about -0.5 to +0.5 and a different color for each 0.05 increment.

Just testing from here (and I could be wrong):

User uploaded file


I am not sure what you are trying to do. More information will lead to a solution.


Regards,

Ian.

Mar 17, 2018 7:11 AM

Reply Helpful

Mar 17, 2018 7:52 AM in response to Yellowbox In response to Yellowbox

Good point, here is a screenshot of my Numbers window. The table on the left is where the raw data is input. The middle colored table is the calculations (just basically doing some averaging and offsets to center the range). The octagonal arrangement of colored cells on the right is the contour map (Z height errors on a circular surface). The cells with the values in them are the actual data points. The other cells data are interpolated and their value's text are made small (1pt) so as to be almost invisible. I have selected one cell with a value of -0.31. That is rule 16. The other white cells also have rule 16 values. Just to make sure that there was not something corrupted in my Mac or copy of Numbers, I emailed a copy of my spreadsheet to another computer. It generated the exact same result. That would indicate that it is in the spreadsheet data itself. If it is a bug in Numbers, then an alternative approach to get the proper result would be appreciated. Thanks.


User uploaded file

Mar 17, 2018 7:52 AM

Reply Helpful

Mar 17, 2018 3:32 PM in response to see3d In response to see3d

Hi see'


"It is only rule 16 that does not execute. Rules 17 and 18 do execute. It does not matter what rule 16 is. I can rearrange the rules and it always ignores rule 16."


You might try bypassing the problem Rule 16 by setting it to a condition that will never be met (eg. greater than 500), moving the 'real' rule 16 and those that follow it up one step to positions 17, 18 and 19.


Regards,

Barry

Mar 17, 2018 3:32 PM

Reply Helpful

Mar 17, 2018 4:37 PM in response to see3d In response to see3d

I gave up and redesigned my contour map to only have 15 colors. Not quite as accurate (20 would be best), but at least Numbers lets me enter all 15 rules before calling it quits. Now I can get on with using the tool. Thanks to everyone who offered suggestions. It is heartening to have received responses of any kind in a short amount of time.


Perhaps there is a 15 rule limit. Perhaps I inadvertently got around the input checks the first time because I hit the add new rule button multiple times in a row before filling out the data in each one. It took them all, but perhaps its internal checks path did not account for inputing them that way. So, I had a data structure with 20 rules when it was only meant to have 15 max. Then every time I would delete a rule and try to add just one, it would not let me do it unless I was below 15 rules. That means the bug was not in executing the rules, but in not checking properly to limit the rules to 15. I looked for but did not find any reference to any limit on the number of rules allowed.

Mar 17, 2018 4:37 PM

Reply Helpful

Mar 17, 2018 5:05 PM in response to see3d In response to see3d

Back to my initial PS, then:


If you think it needs to be expanded, you need to talk to Apple. Here, you're talkig with users of the software, not developers of the software. Use the Provide Numbers Feedbak item in the Numbers menu to send a Feature Enhancement request to Apple.


Regards,

Barry

Mar 17, 2018 5:05 PM

Reply Helpful
User profile for user: see3d

Question: Numbers for mac will not let me add more than 15 conditional highlighting rules