MS Excel 2007 If Then Formula Troubles

07
2014-03
  • JabberwockyDecompiler

    This is a simplified case of an issue that I am seeing using MS Excel 2007.

    Format your sheet to be currency with 2 decimal places.

    Cell A1 type 25

    Cell A2 type 20.13

    Cell A3 type 4.87

    CELL B3 enter the formula =A1-A2

    Cell A4 enter the formula =IF(A1-A2=A3,"TRUE","FALSE")

    Cell B4 enter the formula =IF(A3=B3,"TRUE","FALSE")

    ERROR is A4 displays FALSE and B4 displays FALSE

    NOTE: I first noticed a precision error with the simplified version. So first I changed the precision to calculate by display using this guide. That fixed my simple version, but I still saw the issue on my page. I changed the display of decimals to be 12 decimal places, everything after the second decimal place is a zero on all values on the sheet.

    Does anyone have any suggestions on what else I can try to fix my formula/worksheet.

    • Answers
  • Rik

    When using floating-point arithmetic and using the outcome to do evaluations or further calculations it is often best to make sure your outcome has the right/expected precision.

    The best method of making sure you have the right precision is using ROUND() in your formula. In this case it would be =IF(ROUND(A1-A2,2)=ROUND(A3,2),"TRUE","FALSE").

    The second method advised by microsoft here only ensures that the cells are displayed with the correct decimals. Internally they can still be slightly off. Using it to do further re-calculations will likely result in larger inaccuracy. For evaluations this setting has no effect.

    Note Use of the Precision as Displayed option can have cumulative calculation effects that can make your data increasingly inaccurate over time. Use this option only if you are certain that the displayed percision will maintain the accuracy of your data.

    So the best advice is to always use ROUND() in you formulas where it comes to expected precision during evaluations.


    • Related Question

    worksheet function - Excel 2007 conditional formatting formula problem
  • user48604

    I have the following problem. I have a sheet where the current month should be highlighted. For this I used the formula for conditional formatting. The formula for the first three months is like this:

    #M1
=IF(AND(TODAY()>=DATE(YEAR($B$5);MONTH($B$5);DAY($B$5));TODAY()<=DATE(YEAR($B$5);MONTH($B$5)+1;    DAY(B5)));TRUE;FALSE)

#M2
=IF(AND(TODAY()>=DATE(YEAR($B$5);MONTH($B$5)+1;DAY($B$5)+1);TODAY()<=DATE(YEAR($B$5);    MONTH($B$5)+2;DAY($B$5)));TRUE;FALSE)

#M3
=IF(AND(TODAY()>=DATE(YEAR($B$5);MONTH($B$5)+2;DAY($B$5)+1);TODAY()<=DATE(YEAR($B$5);    MONTH($B$5)+3;DAY($B$5)));TRUE;FALSE)

    B5 is a cell which contains the start date. To write all these changing formulas into cells I used a macro.

    My problem is that when I want to add an additional cell and manually change the formula, it doesn't work anymore. Suddenly the range is different and what-not. I have to write these formulas again with the macro as well as the additional month. It screws up every time I want to do it manually.

    Does anyone know why this happens and how to fix it?


    • Related Answers
  • AdamV

    One thing that would simplify your formula considerably and make it easier for you (and others) to troubleshoot is to simply drop the surrounding "IF". Just use the AND and this will naturally return True or False.

    When editing formulas in the conditional formatting dialog, press F2 to go into edit mode (same as for a cell) then you can use arrow keys etc to move through the formula without it inserting extra cell ranges based on where the active cell is. This might be what you mean by "Suddenly the range is different and what-not" but I am not sure.

    As it stands, your formula does not highlight the current (or next) month, but rather the days between now and the same date of next month. This could be what you want but it is not clear. So it would highlight from 6 September to 5 October rather than 1 Sep to 30 Sep for example. If you want to highlight calendar months, don't use the DAY part of your start data, just use a 1 (and for the last day of the month use Month+1, Day=1, and then take -1 from the resulting DATE)

    Do you want to highlight lots of cells based on this one single start date cell? Or are you trying to highlight rows based on a different value in each row (in column 5)? If the latter, you need to drop the $ from the 5 so you have a relative reference, but make sure you edit this conditional formula while in a cell on row 5 otherwise you are referencing a row above/below for each case.