Nested IF with AND Functions

[Joe Smith]

I'm trying to learn how to use IF nested with AND by
duplicating a formula in the Asset Depreciation Schedule
downloaded from Microsoft's online templates.

The formula, is copied down from B15 through B43. It is
supposed to give you a numbered list of years up to the
number in D8 (labeled "Useful life"). So if D8=10, you see
1 through 10 appearing from B15:B23. The rest of the
column appears blank.

When I duplicate the formula in a blank spreadsheet and
copy it down, I get the 1 through 10, followed by one
blank cell, followed by #VALUE! throughout the rest of the
range (rather than a continued blank appearance as is the
case in the Asset Depreciation template.

If I delete the formula in the template and recreate it in
the template, it works just fine.

The formula in A15 is:
=IF(AND(B14+1<=$D$8,B14>0),B14+1,"")

Any ideas why the first blank appearing cell in the
template doesn't create an error message but does when
created in a new worksheet?

Thanks in advance for your help and time reading through
this posting.

Joe

 

[Earl Kiosterud]

Joe,

I haven't thoroughly digested your situation, but here's something that may
help. With the offending formula, in Edit mode (double-click the cell,
press F2, or work in the formula bar), you can select an expression, press
F9 (calculate), and get an instant evaluation of the expression. Some
examples:

B14+1 Should yield what's in B14 + 1
B14+1<=$D$8 Should yield TRUE or FALSE

If any don't seem to be what they ought to be, you can go after them. Or if
any results in an error, it almost always causes the formula to return an
error.

Be sure not to press Enter, or your formula will get changed to having the
evaluated value put into the formula. Press Esc instead.