Inserting a value into a sequential series and then continuing the series

Question

Example data series has the sequence seeded with 1 as the initial value, and a step of 1. There is a calculated value that needs to be inserted in the series, in this case it is 3.5

Desired output:

1
2
3
3.5
4
5

I am avoiding VBA, and I am avoiding sorts. I am trying to do this with an IF formula.

So far I have tried the following A2 copied down:

=IF(A1+1<3.5,A1+1,IF(A1<>3.5,3.5,A1+1))

Seed value may be any real number. Step value may be any real number.

Answer 1

Here is one formula:

=IF(AND(A1<$D$2,A1+$D$1>$D$2),$D$2,IF(A1<>$D$2,A1+$D$1,((QUOTIENT(A1-$A$1,$D$1)+1)*$D$1)+$A$1))

First we test whether the next step will pass the seed/inserted value and if so we insert the value.

IF not then we test whether the cell above is the inserted value. If not then we just step normally. If it is then we take the value in the cell above and subtract the start value in A1.

With this value we divide by the step and get the quotient or number of steps made till this point.

We then add one to the quotient effectively getting the next step and multiply the step value. we then add back in the start value and get the next step.

---