Can conditional formatting detect formulas?

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

This isn't exactly what you want, but it might do.

Set the cell format to "Number" then set the format for negative numbers to red, no minus.

Set a conditional format to non-italic for greater than or equal to 0, and to italic for less than 0.

Multiply all your formulas by -1.

This way, if the value is from a formula it will appear red and italic (not grey, sorry, I can't figure that one out) because it will be negative, and if it is entered directly it will appear black and non-italic because it is positive.

This assumes that none of the values (calculated or direct entry) would ever be negative, but that doesn't seem to be an issue in this situation since it is length of time to pay off debts.

"This assumes that none of the values (calculated or direct entry) would ever be negative, but that doesn't seem to be an issue in this situation since it is length of time to pay off debts."

Won't work that easily, John. The values calculated are future payments; they're replaced with amounts that are actual payments. The amount in the cell is used in further calculations that determine the number of payments needed to complete repayment of the debt.

To make it work, assuming the payments are all positive values, the OP would have to multiply the formulas by -1, as you suggest, then track down any formulas that used the value in the cell and revise those formulas to use the absolute value (eg. ABS(A1) ).

Regards,

Barry

Good point, I missed that. Another way round the problem my approach creates would be to have a new column with the ABS values and have the formulas refer to that column (which could be hidden for neatness). Might be a little less work depending on the structure of the sheet.

Can't you create a new column which contains the ACTUAL amount you paid which will override the projecgted payment?

In this case so long as the cells in the "Amount Paid" column are blank the Balance is reduced by the projected amount. When you enter an Actual Amount the balance is reduced by the actual amount.

E3=C2-IF(ISBLANK(E3), D3, E3)

fill down

Jowie,

Your original question about formatting and your problem statement about forecasting are two different issues, in my mind. And, of course, perfection is rarely achieved. I can imagine some effective forecasting enhancements to your document that don't involve detecting whether a cell has directly entered data or not, but chasing perfection isn't usually a satisfying effort.

Adressing only your format question, here's my approach. In your calculation, display a non-printing character that can be detected by Conditional Format. One example is to add a leading space, usually not noticed by the reader, and then Conditional Format based on the presence of "Begins With" a space character.

Jerry

I rather like Wayne's solution.

It gives a clear indication where a payment has been entered, requires no messing around with formatting, and takes a fairly simple change to the formula in the Balance cells.

Regards,

Barry

Jowie wrote:

No the forecasting is sorted... No problems at all there. I've got the forward calculations very accurate, and they are based on APR and payments made. The only problem I have is reformatting... It's only a niggle really... When I get the actual value from a bill, I enter it over the top of the calculation/formula already in the cell, then change it to black and non-italic... It's just a step I was hoping to avoid.

The problem with the "begins with a space" solution is that it won't work because my cells are formatted for currency. If I add a space, the next time I edit it the space is gone again.

I think you missed my point. The leading space is in the formula, not the override.

Here's an example:

Jerry

Jowie,

I may never have met someone so resistant to suggestion. Have you tried? Here's an example of a formula performing addition with a string:

A1 contains typed input.

A2 is a reference to A1, converted to a String by concatenation, and amenable to the Conditional Format.

A3 is a formula performing addition with the string in A2.

Jerry

deleted