Conditional formatting if cell contains formula

Question

Level 2

222 points

Conditional formatting if cell contains formula

I've created a spreadsheet of all my finances, with projections down each column to calculate when debts will have been paid off. As I receive statements for each account, I replace the text in the cell with the actual value.

At the moment, I have the projected values (formulas) in grey italics, and the real values as black plain text. But I have to change the formatting manually each time.

I would like a way to be able to format each cell based upon if it contains a formula or not, therefore saving me the manual part of the process. In Excel, I believe this is achieved with the ISFORMULA function.

Is there any way of doing it in Numbers?

MacBook Pro with Retina display, macOS Sierra (10.12.5), 16 GB RAM, 512 GB Flash storage

Posted on Jul 13, 2017 12:50 AM

Reply

Answer 1

Jowie Author

Level 2

222 points

Jul 14, 2017 1:17 AM in response to Yellowbox

Hi Ian, thanks for your help!

Here is a small snippet of my spreadsheet:

It is true that I could put the projections in separate columns, but that would make things look rather messy... Right now I can see everything in its rightful place. Only other thing I can think of is that because the calculated values are almost always calculated to more than 2 decimal places, I could maybe do some kind of "rounding comparison" and format the field if its value contains fractions of a penny. What do you think?

Reply

Answer 2

Barry

Level 9

60,035 points

Jul 15, 2017 5:37 AM in response to Jowie

Hi Jowie,

This might do what you are looking for:

All cells in column B are formatted as currency.

B2 and B3 have been entered directly.

B4 is produced by a formula.

The base formula, chosen only because it is simple and produces a value near the one in A2, is:

=PI()/3*B2

To enable conditional formatting to apply, the result is converted to a text string by using the concatenation operator ( & ) to append two spaces to it's beginning:

=" "&PI()/3*B2

The conditional highlighting rule shown (there are two spaces typed in the box) detects the two spaces and applies a grey colour to the text. Automatic alignment for text is to the left margin, providing another indication that this is a projected amount. The two spaces prevent the text from visually aligning at the left margin.

When the text is replaced by an entered number, the two spaces disappear (with the rest of the text, and the formula) and alignment jumps to the right margin of the cell, the normal alignment for numeric values.

Regards,

Barry

Reply

Answer 3

Yellowbox

Level 7

21,951 points

Jul 13, 2017 8:30 AM in response to Jowie

Hi Jowie,

I do not think that the ISFORMULA function exists in Numbers.

Please post a screen shot of a relevant part of your table (column headings and row numbers will help!) to help us understand your overall aim.

projections down each column to calculate when debts will have been paid off

That makes we wonder if projections should occupy their own column(s) separately from payment column(s). Or perhaps separate tables for projections and payments. Then Conditional Formatting (now called Conditional Highlighting in Numbers 6) will become an easy matter of comparing values between two cells.

Looking forward to more information,

Regards,

Ian.

Reply

Answer 4

Yellowbox

Level 7

21,951 points

Jul 14, 2017 10:27 PM in response to Jowie

Hi Jowie,

I can't think of a way to this without extra columns.

It is true that I could put the projections in separate columns, but that would make things look rather messy.

1. I find it best to work on the logic of a problem without worrying about the final appearance. Numbers is very good at "presentations". For example, we could hide certain columns or "hide" a whole table of intermediate calculations on another sheet.

2. I don't understand how you calculate the projections.

Regards,

Ian.

Reply

Answer 5

Jowie Author

Level 2

222 points

Jul 15, 2017 5:36 AM in response to Yellowbox

Hi Ian,

1. It's probably not worth dealing with worksheets and presentation sheets separately because this is just a personal spreadsheet and I'm not presenting it to anyone. A single view designed to be easy to read and update is the way to go for me.

2. The projections are based upon my monthly payments minus any interest depending on the card.

Reply

Answer 6

Jowie Author

Level 2

222 points

Jul 15, 2017 5:40 AM in response to Barry

Hi Barry,

That is an ingenious solution! The only issue is that I also have a running total column, which is a sum of all the outstanding debts. That too is used to show calculated projections. So I could use your solution, however I guess it won't allow me to sum up all the values, if some of the values are actually strings.

Reply