Decimating rows in spreadsheet
Solution 1
Placed in cell A1, the formula
=MOD(ROW(A1),10)
will repeat with values 0-9 when you fill it down. You can then use filtering to delete the rows that you don't want. Note that the result of the formula will then change if you delete (rather than clear) the rows. (Although filtering doesn't update until you ask it to.)
Alternatively,
=MOD(ROW(A1),10)=0
will only return TRUE
every tenth row.
Solution 2
You can use the Sampling function in the Analysis Toolpak, which you will have to install. Unfortunately, MS makes you work to find it if you are using anything later than Office/Excel 2003.
Go to File menu and click on Options. On Excel Options window, click on Add-Ins. At the bottom, select Manage: Excel Add-ins and click Go.
Check the Analysis Tookpack and click OK. Now select the Data menu to see the associated ribbon. Over on the right, select Data Analysis.
Scroll down to Sampling and click OK. You will see a window that allows you to specify the sampling period, cell ranges, etc. To sample every 10th cell, select Periodic and 10 for the period.
In Excel 2003, the above is accomplished in only a couple clicks, indicating how grossly inefficient the ribbon is.
Related videos on Youtube
Val
Updated on September 18, 2022Comments
-
Val about 1 year
Is there such an option to preserve only every 10th row in my spreadsheet? It can be Excel or Calc. Quality standards want some noise to be added.
-
Val almost 10 yearsCan you make this the answer?
-
-
DavidPostill about 9 yearsIt would improve your answer if you explained how to install this addin