Recount/Reorder

Hello,

Can you provide me a formula that will recount if a number is missing?

For instance if I have 1,2,3,5 but I only want to see 1,2,3,4 because I don't need 5... How would I write that


A little bit trickier... If I want to Add in into the formula as well...
Example I have 1, 2, 3, All, 5... How can I get it to show 1, 2, 3, All, 4

I have included an attached spreadsheet that show the numbers in column B how the result will display currently, column C is for the formula to reorder column B... and I have added column F to show how the results should show.

Your help is greatly appreciated!

See attachment

The object of the entire formula is to change the numbers to count in order if a number is missing. ((like a recount))

If there are no 1's it will do a search within the range of numbers to find the number closest to 1.
So lets say I have in Column A: 2, 4, 5, 6
If it starts at 2 it will change all the 2's to 1's. Also, It will change my 4's to 2's, 5's to 3's, and 6's to 4's.

The results in Column B would show: 1, 2, 3, 4


Hopefully I explained it better.

Thank you! This formula works great for the attached document. Is there a formula to use if the numbers in column B were to change around? These numbers will change constantly anywhere from 1 to 5.... I tried to change the fist two number to a 3 but it doesn't seem to work work the same way.

The results are in column F needed.

Is there a way i can use the RANK formula but if it has "All" in the results to return all?

Try this array formula in row 2

=IF(ISNUMBER(A2),SUM(IF(FREQUENCY(IF(ISNUMBER(A$2:A$27),IF(A$2:A$27<A2,A$2:A$27)),A$2:A$27),1))+1,A2)

confirm with CTRL+SHIFT+ENTER and copy down

That gives me the same results as you showed in your "expected results" column

see attached