Apple Event: May 7th at 7 am PT

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

Level 2

222 points

Can conditional formatting detect formulas?

I have a spreadsheet for home accounts/debts etc, and I like to put forward calculations in columns so I can get an idea of how long it will take to pay off debts. These forward calculations are formulas based on APRs etc, and I manually format them in grey italics. When a bill comes in with the actual amount on it, that formula gets replaced with the bill amount, and I switch the format to black plain text so I can see it's an actual value.

Is there any way, using conditional formatting or otherwise, I can automate this so that when I type in a real value it can detect it's not a formula and reformat the cell?

Thanks!

MacBook Pro, Mac OS X (10.6.8), 13" Core 2 Duo 2.4 GHz

Posted on Nov 12, 2011 4:37 AM

Best reply

Barry

Level 9

59,861 points

Posted on Nov 12, 2011 11:36 AM

No.

Conditional format rules depend on comparing the value contained in the cell, and pay no attention to how that value got there.

That said, it might be possible to examine the value and determine whether it was entered directly or formula generated. If the value in the cell is NOT equal to the value in the cell rounded to two decimal places, then chances are the value was generated by a formula. Not perfect, as it will not detect generated values that are exact to zero, one or two decimal places (eg. 12.00000, 12.10000, 12,12000, etc), and won't find any values where the formula itself actually rounds (as opposed to 'displays') its result to two (or fewer) decimal places.

Here's an example:

As indicated in column A. the values in B2 and B3 are entered numbers, those in B4 and B5 are calculated. Column B is formatted to DISPLAY these numbers as Currency, with the usual two decimal places.

Column C uses the formula =B to transfer the actual values in column B. Formatting is set to automatic.

Column D uses the formula =ROUND(B,2) which rounds the values in column B to two decimal places. Formatting is set to automatic, so what is shown is the actual value.

The conditional formatting rule, shown for cell B5, compares the actual value in column B with its rounded counterpart in column D. If they are the same, the assumption (wrong, in this case) is that the value was entered, and the rule applies bold style to the text and a light grey fill to the cell. If they are different, no change is made.

Note that there's some tedium involved in setting this up, as the rule for each cell uses a different comparison cell, and these must be set (or reset) individually.

Regards,

Barry

View in context

20 replies

Badunit

Level 7

26,012 points

Nov 14, 2011 9:22 AM in response to Jowie

If the string " "&355.24 is in a cell, not typed directly into the formula, the formula will accept it. 355.24&" " will not work.

If you changed the formula to be =" "&([cell reference] - 355.24) it would work.

Jowie Author

Level 2

222 points

Nov 14, 2011 9:52 AM in response to Badunit

I do beg your pardon (and Jerry). Had I put it in exactly as you'd suggested, it would've worked for me first time. Sorry!

Looks like that works... Great solution, thanks 🙂

Nov 14, 2011 10:05 AM in response to Jowie

No problem. Numbers does try to do the right thing. Sometimes we have to figure out what it needs, but it usually isn't a difficult mystery. Basically, if it lookes like a number, it will be treated like a number, most of the time. If not, we can usually force it. For example, if we write in column A: "25.25 Dollars", numbers will be upset if we try the equation =A+10. But, if we then try: =LEFT(A,5) + 10, Numbers will be happy. We're adding a numeric value to a string, but it's not ambiguous.

Jerry

Jowie Author

Level 2

222 points

Nov 14, 2011 11:21 AM in response to Jerrold Green1

Cool 🙂 the only weird thing is that sometimes it automatically puts the £ sign in, and sometimes it doesn't. So in some columns I've had to do =" £"&[FORMULA] whereas others I've just put =" "&[FORMULA] otherwise it adds the extra & symbol and fails on the calculation.

Not really a big issue because I can work around it, but I wonder what's causing it...

Nov 14, 2011 1:14 PM in response to Jowie

Jowie,

If your cells are formatted as Currency, and you insert or calculate a numeric value, the currency sign will be prepended. If you reference a cell containing a currency format in a cell with a string formula, Numbers will retain the currency sign of the referenced value. It generally does what makes sense. This may explain the behavior you described in your last post.

Jerry

Can conditional formatting detect formulas?