using if function in an amortization table

I am trying to use the if function in an amortization table and I am wondering if there is a way to make it so if I cut the years in half if I can make the table read with 0's or dashes rather than to start counting back up again.

Hi rdcall,

welcome to the forum.

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

Hi,

you could compare the number in the Term cell D4 with the current column number adjusted by two cells and wrap your calculation in each cell in this.

For example
H9 =IF(COLUMN()-2>$D$4,"",H31)
H10 ==IF(COLUMN()-2>$D$4,"",H9*$E$5)

and so on.

You only need to change the formulae in one column, column C, then copy right

=column() in column C returns a 3
=column() in column H returns a 8

you need to subtract 2 from the respective number to have the correct number to compare with your term number.
For example, in column H you don't want to see a value if the column is > than the term number, so you calculate the column() for H, which is 8, but subtract 2 to adjust for the two columns A and B, which do not have any calculations, to arrive at 6. So now you see if 6 > 5 and if so, enter a blank.

On the other hand, since you have numbers in row 8, you might as well use these:

=if(H$8>$D$4,"",H31)

note the placement of the $ sign in H$8

Good feeling, innit?

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Trent, welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.