Forum Discussion
Solved
Project Plan (If Formula) Query
Hello, I am trying to create a project plan on Excel. Currently the formula in N6 is simply ‘=L6+M6’ and N7 is the same methodology. The issue with that is the end date will show up as 00/01/1900 if column L and M are blank. But column O onwards are fine in this scenario and work as expected.
To mitigate this issue in N8 and N10 I added this formula ‘=IF(ISBLANK(L8), "",L8+M8)’ Again this works fine if column L and M have data to fill in column N. However, if I leave N10 blank; everything on column O onwards is filled in when I want it to be blank. The formula in O10 onwards follows the pattern of ‘=IF(AND(O$5>=$L10,O$5<=$N10),"X","")’ Is anyone able to suggest a formula for either column N or column O onwards which can mitigate these issues?
I've added a screenshot for context of the spreadsheet.
Thank you! 🙂
Re: ``Very simple formula checked against a calculate and the answer is significantly incorrect``
You neglect to provide even a single example of the Excel result vs the calculator result.
But we might guess that you are making a very common mistake: when you enter values into the calculator, you enter them as they are displayed in Excel.
But the displayed values might be different from the actual values; and Excel uses the actual values in calculations.
For example, the formula in G7 is =B7*C7*H7*F3. B7 is 1, C7 is 77.81, H7 is 4, and F3 appears to be 39. And the result in G7 appears to be $12,093.90.
But if we enter 1*77.81*4*39 into a calculator, the result is $12,138.36. That is indeed very different.
The problem is: F3 only appears to be 39 because of the cell format. Its actual value is 38.8571428571429. And that is the value that Excel uses in the calculation.
To demonstrate, if we enter 1*77.81*4*38.85714 into the calculator (and display only 2 decimal places), the result appears to be $12,093.90 -- the same as G7.
And BTW, G7 only appears to be $12,093.90 because of the cell format. Its actual value is 12093.8971428571.
Only you can decide if and where to explicitly round Excel calculations. For example, you might write:
F3: =ROUND((E3-D3)/7, 0)
G7: =ROUND(B7*C7*H7*F3, 2)
(Arguably, rounding G7 is unnecessary if we round F3, at least for the example Excel file. But it is prudent to explicitly round calculations that involve decimal fractions for other reasons.)
Alternatively, you might choose to leave F3 unrounded for other purposes, but round its value when calculating G7. So, you might write:
F3: =(E3-D3)/7
G7: =ROUND(B7*C7*H7*ROUND(F3, 0), 2)
But in the final analysis, you might choose not to round at all, accepting the fact that the Excel calculation is more "accurate" (for some purposes) and different from the WYSIWYG calculations that you might enter on a calculator.
Aside.... There is no need to use DATEDIF(...,"d"); E3-D3 is simpler. And there is no need for the outer parentheses around the entire formula expression after the equal sign ("=").
-----
Caveat: Someone might suggest setting the option "Precision as displayed" to avoid the explicit rounding. I do not recommend that, for many reasons. But if you choose to experiment with PAD, be sure to make a copy of the Excel file first. Merely setting PAD might irreversibly change constants that you purposely display with less precision. Setting PAD affects all worksheets change in the entire Excel file. And PAD affects only the final value in a cell that is formatted as intended; for example, =IF(F3=39, TRUE) would still return FALSE(!).
1 Reply
Wll2546 Let's start with the first question first. Dates are stores as sequential numbers, starting at January 1, 1900. When you format a column as Date, a zero will, oddly enough, become 0 January 1900. You can suppress that by custom formatting the entire date column with the desired date format followed by two semicolons. See picture below where the active cell contains a zero, formatted as a custom date with ;;
And this works in column O (and onwards) because zero never falls within any of the date ranges, and most likely, the conditional formatting condition that colors the cells green is never met. This will thus solve the problem you raised in the second question, which is most likely caused by the conditional formatting rule that colors the cells green. It is probably based =NOT(ISBLANK(cell)) . A cell with "" is not considered empty/blank when you evaluate it with ISBLANK. You would need a rule like =cell<>""
