Previous 1 2 Next 20 Replies Latest reply: Nov 14, 2011 1:14 PM by Jerrold Green1
Jowie Level 2 Level 2 (205 points)

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
  • Barry Level 7 Level 7 (29,210 points)

    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:

    Picture 23.png

    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

  • John De Banzie Level 2 Level 2 (460 points)

    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.

  • Barry Level 7 Level 7 (29,210 points)

    "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

  • John De Banzie Level 2 Level 2 (460 points)

    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.

  • Jowie Level 2 Level 2 (205 points)

    Lots of interesting solutions here, many thanks for the thought and effort! It's a shame that a perfect solution can't be found... There are quite a few columns of data so I can imagine it would get really complicated if I started adding hidden columns with extra data just for formatting purposes, and knowing me I could break something in the process!

     

    Unless there's something I can do with macros?

  • Wayne Contello Level 6 Level 6 (15,220 points)

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

     

    Screen shot 2011-11-13 at 8.27.38 AM.png

    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

  • Jerrold Green1 Level 7 Level 7 (29,925 points)

    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

  • Jowie Level 2 Level 2 (205 points)

    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.

  • Barry Level 7 Level 7 (29,210 points)

    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 Level 2 Level 2 (205 points)

    It is a nice solution and if my spreadsheet was smaller I could see it possibly working. But unfortunately, it overcomplicates things for the size of spreadsheet I have. I already have as many columns as I can fit onto the screen to see it all at a glance, and by adding an extra column next to each one that already exists will mean I can't see all the correct values next to each other. Not only that, it defeats the object of using conditional formatting to make the numbers easier to see, because it introduces another column of numbers.

     

    It also means that the graphs I have set up will no longer work, because extra columns have been inserted which don't add up in the same way. I also have a lot of SUM() calculations for rows, which also wouldn't work with two columns per debt.

  • Jerrold Green1 Level 7 Level 7 (29,925 points)

    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:

     

    Screen Shot 2011-11-13 at 7.59.50 PM.png

     

    Jerry

  • Jowie Level 2 Level 2 (205 points)

    Nice idea! But that doesn't work in my case unfortunately, because each month is calculated from the previous month, and due to the " " added it therefore becomes a String rather than a Number and cannot be calculated upon.

  • Jerrold Green1 Level 7 Level 7 (29,925 points)

    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:

     

    Screen Shot 2011-11-14 at 10.01.51 AM.png

     

    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

  • Badunit Level 6 Level 6 (11,400 points)

    deleted

Previous 1 2 Next