In the cell that has the (2666) I want it to be a zero, I am using the iferror as you can see in the formula, but it is not working. So how do I get it to show zero until I fill in the blank blue cell.
In the second screen shot in the cell that has the number 1377, what I would like that to do, is every time the millage hits 4000, 8000, 12000, ect., I want it to start over the counting the 4000 in the cell that has the 1377.
iMac, Mac OS X (10.7.1)
HI Mike,
IFERROR is working exactly as it should. The enclosed formula says "subtract the value in cell B8 from the value in C8" An empty cell, or a cell containing a text value is interpreted by the subtraction operator as numerically equivalent to zero, and your formula is calculating the result of 0 - 2666 and returning the correct result, -2666 (formatted to display as (2666). Had there been an error, IFERROR would return either a null string or a single space, depending whether there is nothing or a space between the quotation marks in the formula.
If you want the cell containing the formula to contain an null string untill there are entries in both column B and column C, then you need to use IF, and include a test that will determine whether both cells have entries. The test used by Apple in the Employee Schedule template was recommended in answer to your previous (similar) question, and adapted to fit that table. Here's a replacement for your current formula above that could be used here. It's written for cell D2, and can be filled down from there.
=IF(OR(LEN(B)<1,LEN(C)<1),"",C-B)
LEN returns the length (in characters) of the entry in the cell in 'this row' of column B. The second LEN does the same for the cell in column C.
If either of these lengths in less than 1 character, there's no entry in one or both of the cells, OR returns TRUE and a null string is returned to the cell containing the formula.
If both lengths are 1 character or more, OR returns FALSE, the calculation is carried out, and the result returned to the cell containing the formula.
Distance to next oil change question:
A2: =4000−MOD(A4,4000)
MOD(dividend,divisor) returns the remainder when the dividend (in A4) is divided by the divisor (4000).
Regards,
Barry
iferror not working
