• 597K Members
• 4,628 Online
• 726K Conversations

## Remove

Occasional Contributor

# Remove

Hello,

How can i automatically remove the lowest digit from a row after 13 digits.

Jos

9 Replies
Highlighted

# Re: Remove

What do mean by remove the lowest digit after 13 digits?

Is this an accurate representation where the cell value is = 12,345,678,910,111,212,978?

Highlighted

# Re: Remove

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.

Gr. Jos

Highlighted

# Re: Remove

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))<LARGE(\$C\$3:\$C\$21,MIN(12,COUNTA(\$C\$3:\$C\$21))),0,LARGE(\$C\$3:\$C\$21,ROW()-ROW(\$D\$2))),0)``
Highlighted

# Re: Remove

In this example, with every time a number is added, the highest 12 must remain.

Highlighted

# Re: Remove

If your goal is actually remove the values which are below top12, that's with VBA programming.

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<LARGE(\$B2:\$T2,12)``

and white on white formatting.

Totals in vertical

``=IFERROR(SUMPRODUCT(LARGE(\$B2:\$T2,ROW(INDIRECT("1:"&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>=\$AJ\$2:\$AJ\$42)*B\$2:B\$42)``

In attached file I kept your calculations and added above in parallel.

Highlighted

# Re: Remove

In this example, with every time a number is added, the highest 12 must remain.

Highlighted

# Re: Remove

@Jos4841MC , in your sample I don't see "12 only", there are all results in the table.

Anyway, I don't have another solution but the one in the file attached to my previous post.

Highlighted

# Re: Remove

Thanks it works perfectly, is it possible that the position changes automatically according to the number of points someone gets. And it is also possible that if someone fails 12 times, they will no longer participate in the final ranking.

Thanks

Highlighted

# Re: Remove

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

Related Conversations