Incorrect autofill formula when inserting a new row

Hi, I'm new to this forum. I'm working on a spreadsheet that uses the IF function in a table. When dragging the formula down it auto fills correctly, but when inserting a new row it auto fills correctly up to the last reference which it duplicates then then the value below skips one. I have attached a some screen shots. Any help would be greatly appreciated. [ATTACH]860340[/ATTACIF error.xlsxH]IF function with error.PNG

As it appears that cells J17:O17 should be blank, please paste the following into cell J17, copy over to cell O17 and then down to the last row of the table:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

II put the formula in J17:O17 and it still producers a a inconsistent formula when autofilling, i,e duplicate last reference then the next row will skip one.Capture.PNG

As DJ has explained, you need to have a formula ... "the" formula in the first row of the table. That's what Excel will use to replicate the formula in new rows. If you don't have a formula in the first row of the table, Excel gets confused. The formula Jetemec has given you checks the row number and, if it is 17, it returns a null value (to all intents and purposes, blank). If it is not, that is for the rest of the table, it processes the formula. Once you have established the new formula, you need to copy it down to all rows in the table to establish consistent formulae.

You should then find that, if you edit any cell in a column, it will ripple through the entire column. And, if you insert a row, it should replicate correctly.

I think the formula needs a slight tweak:

Formula:

Please Login or Register  to view this content.

@TMS, I agree with your tweak, however when I turned off Show Formulas in the original file it displayed FALSE in the cells with formulas, so I assumed that is what the OP wants.

Here is the file from post #1 with the formula from post #4 applied.
When I insert a table row (20), not a sheet row, the formula does not duplicate the last reference nor skip the next one.
Let us know if you have any questions.

It's the name of the Sructured Table.


If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

You're Welcome and thank you for the feedback. as TMS stated, please take a moment to mark the thread as 'Solved'. I hope that you have a blessed day.