A cell in a Numbers spreadsheet I was using suddenly started displaying a blue triangle in upper left corner a column of cells. It is basically a sequence of dates. I have been using this spreadsheet for years and never had the blue mark, suddenly it appeared.:
What does it mean and why is it there? How can I remove it?
The cell formula is:
iMac, OS X Mavericks (10.9.2), 16 GB RM; 1 TB HD; 3.2 GZ Intel
Hi Gregory,
There are several reasons for a blue triangle. It indicates a non-fatal flaw in a formula. Sometimes comparing a date to text will do it but I can't get one right now. Without knowing what the other cells your formula refers to and how they are formatted I can only guess.
You can click on it and it will tell you what is going on. What does it say?
quinn
Hi Gregory,
There are several reasons for a blue triangle. It indicates a non-fatal flaw in a formula. Sometimes comparing a date to text will do it but I can't get one right now. Without knowing what the other cells your formula refers to and how they are formatted I can only guess.
You can click on it and it will tell you what is going on. What does it say?
quinn
At first I got nothing by clicking on the cell. However clicking right on the triangle it says:
"DATE month argument should be between 1 and 12." Daaa!
The entire amortization table prints out ok and there are no months greater than 12 so I can't figure out what is wrong with the formula .
All the references are ok and until I updated the spreadsheet to this latest version of numbers it never had the blue triangles. It works OK and all the cells are computing OK so I guess I can get buy, its just puzzling.
Hi Gregory,
So what values are in R13? Are they calculated?
Another way to test R18 would be =IF(len(R18)>0,etc.). But that doesn't seem to be the issue.
My attitude toward blue triangles is evolving. In the past I did not care. Now I want to tidy them up. They still don't make much difference.
I am still in Numbers 2.3 and Mavericks so it is actually not surprizing I can't duplicate your issue. Even something like =YEAR("january") gives me no complaints.
quinn
Actually $R$13 is the years for the loan. It is 15 (calculated from a formula "Enter Values::B4" whatever that means!
$R$18 is is the payment date. It is blank and has no formula but I think it should have a day of the month. I tried 15
This next one kills me, it is "Number of Payments" $R$15 which comes from the formula :
"Match 0.01,R5,-1)+1"
The parentheses is actually a faint crescent moon, not sure what those are? There is no matching one facing the other way!
R5 is this:
it is called extra payments
This just goes on and on, I give up! the problem is not very obvious. I guess one of these terms somewhere has a bug in it according to "NUMBERS" but since the answers are OK why bother !
Thanks for trying to help, I just don't think it is worth any more of our time!
Gregory,
Your Date argument for Month is computed. The computation is: MONTH($R$13)+(A2+1)*12
Find a spare cell in that same table and enter:
=MONTH($R$13)+(A2+1)*12
See what it evaluates to. I'll bet it is greater than 12.
Jerry
Hi Gregory,
Gregory Lazarchik wrote:
The parentheses is actually a faint crescent moon, not sure what those are? There is no matching one facing the other way!
The crescent moon is the Numbers 3 way of showing a parenthesis (bracket).
To see the whole formula, you can enlarge the edit box by dragging any side.
Regards,
Ian.
Thanks, that is what I thought but I cannot discern any facing the other way to pair the parenthesis. It is a really poor way to do it!
Gerald you missed one term: divided by $R$15
=MONTH($R$13)+(A2+1)*12 / $R$15
But even so I did as you suggested and it does give bigger than 12 numbers however in the spreadsheet itself the month labels in the amortization table never go over 12..
$R$13 is loan years, in this case 15
A2 is 1
$R$15 is the number of payments which is the formula = MATCH 0.01,R5,-1)+1
It seems to me it should be =MATCH ( 0.01,R5,-1)+1 But the Match Function requires an array for the second term like (R5:R12) so it doesn't make sense to me.
IT has to be something to do with the R5 which is a "WHAT IF..AND" formula with about 13 terms. ( see my reply above 1/18/15 9:29 PM)
The one facing the other way that you can't find would be the one containing the name of the function, wouldn't it?
Hi Jeff,
It would.
I really like the way it works and looks, seems very clear. It is easy to move functions within a formula with cut and paste- one click and you have the function.
quinn
Gregory,
Sorry, I miss a lot with my old eyes. Glad you were able to fill in the blanks!.
The reason that the month labels don't go past December is that Numbers doesn't care whether you have a value greater than 12 in the Date function month argument. It will adjust because it knows that there are only 12 months in the year and it guesses that if you give it 15, you intend that to be treated as though it is a 3. The blue triangle is just an alert, not an error. It wants to warn you, not to block the calculation.
Jerry
The Crescent moons (parenthesis) in my formulas are faint outlines not filled shapes as you show in your post. Very hard to see. How do you get them to be filled?
Hi Gregory,
When you select the function (I clicked SUM) thats when the function highlights. I use it often when I am building a complex formula. If I built the AND() function in your example above separately and wanted to cut and past it into the IF() I could click it and cut/paste.
quinn
thanks,never noticed that!🙂
Hi Gregory,
Glad to help. I think you will like it more and more as you use it.
quinn
What does Blue triangle in corner of NUMBERS cell mean?
