Formula not copying down correctly

"I don't know what the blue triangles mean. "

The blue triangles are 'warning' triangles. They flag one or more warning messages regarding the formula. Warnings are about conditions that won't keep the formula from producing a result.

The red triangles are 'error' triangles. They flag errors that will prevent the formula from working.

Clicking on either type of triangle will reveal the warning(s) or the error that the triangle is flagging.

Click the blue triangle in the bottom row cell displaying 'K35 hand', and you should see the warning message(s) for that cell. In the box at the bottom of the document window, you'll als0 see the formula that has been copied down from the cells above.

Nothing wrong with your nested IF formula. There are done differences from the way I'd write it, but none of them are essential.

• It could be shortened by renaming "Table 1" on the sheet named "Curtains" to give the Table a name distinct from all other Tables in the document. If only one table in a document has that name, the Sheet name is not necessary, and Numbers will automatically drop it from the cell references in the document.
• IF you are not filling the same formula left or right into other columns, the absolute reference operators ( $ ) preserving the columns aren't necessary.
• $R references a range of cells—all of the non-header, non-footer cells in column R and in column D respectively. In many cases like his one, where the comparison operator ( = ) is expecting a single value, not the list of values in all of column R, will detect that and treat the column reference as a reference to the cell on 'this row' of column R. In cses where it doesn't, you'll need to include the row number of the referenced cell as well as the column letter.

I suspect your spreadsheet is working as it is designed to, including the two cells containing warning triangles. The warning message on those cells may explain how K35 hand got into them. My guess is that it was the clisest available value in column R of the referenced column.

Regards,

Barry

Hi Reece!

I am very impressed with that formula! I suspect one of the reasons your table works a little wonky is Numbers is having to work very hard to parse your formulas. I think that may be the reason it is glitching.

I looks to me like you intend to test the value in column AH for a value G 1 thru G 8 and then return a value from a different cell. I don't see what your IFs return if False. I suspect nothing is designated. It would be easier to parse if I could examine the actual text of the formula. Could you copy and paste it here?

Again, I think the reason for Numbers misbehavior is that it is overburdened with unnessasary calculations and once these formulas are cleaned up things will work more smoothly.

quinn

Hi Reece,

I'd also like to get a text version of that formula to analyze.

To present it here as text:

• double click the cell containing the formula to open the Formula Editor.
• Press command-A to select all
• Press command-C to copy

In a new message here:

• Click where you want to place the formula
• Press command-V to paste

Having a text version makes it easier to separate the formula into its parts for a better view of its structure.

Regards,

Barry

Hi Reece,

The blue triangle is surely a relevant clue. If you click on it it will tell you why it is there. It usually indicates that the formula(!) in the cell is comparing things of differing natures.

=IF(B1>C1,"TRUE","FALSE")

So your formula is still there but we don't know what it is looking at. Double clicking the cell would confirm this.

I don't believe your formula construction follows what might be called "best practices". If I was constructing the formula in the k35 hand column it would have started with Curtains::Table 1::$R1 and been filled down so at row 96 would read Curtains::Table 1::$R96. While this may not improve the functionality of your spreadsheet (I guess it is working fine for you), it will remove the ambiguity as to where the cell contents are coming from.

quinn

Oh man, the forum did not take my informative and concise post. Let me reconstruct.

"The formula contains an invalad reference" suggests that it is looking for an address that doesn't exist. This would be easier for you to debug on your Mac. Clicking the ADDRESS function would show Function Result at the bottom of your window.

It is looking for a table with the name found in AH5 in the sheet (tab) named "Roman". Do you have those? I suspect the MATCH formulas are not retuning anything useful as they are searching the G 2 area of the Romans table.

The formula on my machine returns Roman::G-1::E5. Cell E5 in table G-1 on sheet Roman.

Hope this helps, I am off to brunch!

quinn

What is the warning flagged by the blue triangles?

What is the formula in the cell third from the bottom containing "K35 hand"?

What is the formula in the cell fourth from the bottom containing "K35 hand"?

Ditto for the last two cells displaying "Lndy"

Regards,

Barry

I cannot explain why the "K35 Hand" column is doing what it is doing. If there are enough rows in Curtains::Table 1 that the reference is valid, it should result in a "0". If there are not enough rows, it should be an error trianlge for an invalid reference. I am unable to duplicate the problem.

The typical way to construct a long formula like you posted, with a whole bunch of smaller formulas all added together, is to break it up into smaller pieces then add the pieces together. It makes it easier to write, easier to read, and far and away easier to debug. Twenty IF statements would use 21 columns, one for each of the 20 IF statements and one to add up all those columns. Then you hide the 20 columns, leaving visible only the one column that has the result. You could group a few IF statements in each column to reduce the column count, if desired. But you said something about there being 250 formulas and I don't know if that means 250 columns of formulas or something else. If you have 250 columns of formulas, you are just about at the limit for number of columns and will not be able to break the formulas into smaller pieces using this method. Given that each of your IF statements start with" IF($AH=", I wonder if there is a much more concise way to write the entire thing, that would take just the one column, maybe with a separate lookup table.

If you are interested, and you are willing to share the file, your spread sheet could be reworked to simplify it. If you can save it to dropbox (without personal info) and share the link, I would be happy to help with that.

I am also interested in your workflow. How is it that you need to delete rows with such regularity? There should be ways around this.

It may be that quite a few problems can be eliminated by structuring your tables with a data table (or tables) and a report table(s) that gathers the info you need in a simpler form.

quinn

Hi Reece,

A little insomnia here so I am back. I do think the reason for your glitches is the massive load this sheet is putting on the program. You may end up graduating to a database program. In the interests of postponing that day, I am looking to simplify and use what I think of as lower order functions. I used to run a spreadsheet off an iPod that really had a hard time keeping up so I thought a lot about what sort of demand different functions would ask of the program and the machine. I like the smaller tables because it makes Numbers job simpler. Numbers is built to prefer tables to designated areas of larger tables. ADDRESS and INDIRECT are simpler functions than INDEX. I believe there are less calculations involved.

Anyway, I realized there were some things missing from the last formula. It did not take into account the value in BH$1 and it did not address the sheet for the ADDRESS function. The sheet that the G1 table is on is "Roman". It shows up in my "style" column.

=INDIRECT(ADDRESS(MATCH(E2,'G-1'::A,0),MATCH(D2,'G-1'::$1:$1,0),addr-type,addr-style,B2&"::"&C2))×F2

I hope this makes sense to you and offers a way forward to simplify other formulas and tables in your spreadsheet.

I will be interested to hear your reaction.

quinn

Hi Reece,

From your screenshot I cannot tell where your formula is landing but you should be able to see what value it is picking up and where it should be. I do suggest selecting entire rows and columns rather than the way you are doing it. Again, this simplifies the work you are asking the program and your machine to do.

Get rid of unused rows and columns in your tables. Use your headers. I think that is part of it.

quinn

Hi Reese,

Select the entire row in your MATCH formulas.

q