Pace and Cap - Sartin Methodology & The Match Up - View Single Post

DaveEdwards · 01-22-2019, 04:41 PM

Hi Wayne,

I have taken a look at the spreadsheet and if I'm reading your issue correctly it should be easy to overcome.

It all depends where you insert the lines on whether or not the formula at the bottom of the sheet automatically amend themselves to take account of the extra lines.

If you go into Q25 you will see the following formula
=COUNTIF(Q3:Q20,"=3") / Q22 * 100

Now if you insert the additional lines right at the bottom, presuming you've filled 20 lines and don't want to alter anything you'd click over the number 21 and on the left hand side and drag down a few lines thereby highlighting a few lines across the sheet. The next step would be to right click and insert lines. The problem is that by doing it right at the bottom, you are beyond the range of the formula, that is from rows 3 to 20. Excel assumes you are doing something different now.

The solution is to start with a new blank template. Go to the number 5 for instance on the LHS, hold down the left button and drag down a few numbers. Now right click again to insert rows.

As this has been done in the middle of the range used for the COUNTIF process Excel automatically updates the formula in the cells below.

Summary if you don't care about the details, get a blank template and add extra lines, but always be sure that it is within the range currently specified. In this case 3 and 20. Once you've set up this new version, just copy and paste your data into the new one and you should be good to go.

01-22-2019, 04:41 PM	#7
DaveEdwards Grade 1 Join Date: Nov 2008 Location: England Posts: 489	Hi Wayne, I have taken a look at the spreadsheet and if I'm reading your issue correctly it should be easy to overcome. It all depends where you insert the lines on whether or not the formula at the bottom of the sheet automatically amend themselves to take account of the extra lines. If you go into Q25 you will see the following formula =COUNTIF(Q3:Q20,"=3") / Q22 * 100 Now if you insert the additional lines right at the bottom, presuming you've filled 20 lines and don't want to alter anything you'd click over the number 21 and on the left hand side and drag down a few lines thereby highlighting a few lines across the sheet. The next step would be to right click and insert lines. The problem is that by doing it right at the bottom, you are beyond the range of the formula, that is from rows 3 to 20. Excel assumes you are doing something different now. The solution is to start with a new blank template. Go to the number 5 for instance on the LHS, hold down the left button and drag down a few numbers. Now right click again to insert rows. As this has been done in the middle of the range used for the COUNTIF process Excel automatically updates the formula in the cells below. Summary if you don't care about the details, get a blank template and add extra lines, but always be sure that it is within the range currently specified. In this case 3 and 20. Once you've set up this new version, just copy and paste your data into the new one and you should be good to go.