Remove

Question

Hello,&nbsp;How can&nbsp;i automatically remove the lowest digit from a row after 13 digits.&nbsp;Jos

sergeibaklan · Answer

Jos4841MC&nbsp;
I guess you any case do records for all 19, thus highest could be calculated in another column or row

as
=IFERROR(IF(LARGE($C$3:$C$21,ROW()-ROW($D$2))&lt;LARGE($C$3:$C$21,MIN(12,COUNTA($C$3:$C$21))),0,LARGE($C$3:$C$21,ROW()-ROW($D$2))),0)

sergeibaklan · Answer

Jos4841MC&nbsp;
If your goal is actually remove the values which are below top12, that's with VBA programming.
&nbsp;
If to ignore in calculations that could be done with formulas. To hide such values you may apply conditional formatting rule on the first place

with formula
=B2&lt;LARGE($B2:$T2,12)
and white on white formatting.
Totals in vertical
=IFERROR(SUMPRODUCT(LARGE($B2:$T2,ROW(INDIRECT("1:"&amp;MIN(12,COUNT($B2:$T2)))))),0)
counting
=MIN(12,COUNTA($B2:$T2))
minimum
=IFERROR(LARGE($B2:$T2,MIN(12,COUNTA($B2:$T2))),0)
totals in horizontal
=SUMPRODUCT( (B$2:B$42&gt;=$AJ$2:$AJ$42)*B$2:B$42)
In attached file I kept your calculations and added above in parallel.

sergeibaklan · Answer

Jos4841MC&nbsp;
If automatically that's with VBA programming. But you may add another helper column which will calculate ranks only for &gt;=12 and perhaps to sort entire range based on it.

chrismendoza · Answer

Jos4841MC&nbsp;&nbsp;What do mean by remove the lowest digit after 13 digits?&nbsp;Is this an accurate representation where the cell value is = 12,345,678,910,111,212,978?

jos4841mc · Answer

Hi,We have competition over 19 evening, the best 12 counts. So if someone has been 13 times or more, the worst must automatically expire.&nbsp;Gr. Jos

Forum Discussion

Remove

9 Replies

Resources