Excel formula

Question

Is there an excel formula which will automatically add .01 or .02 to a list of numbers that are duplicative?

Answer 1

Hi @Michael Glaspy,

Use below formula and drag it down:

=B2 + COUNTIF($B$2:B2, B2) / 100

or use this dynamic formula (no need to drag it):

=BYROW(B2:B12, LAMBDA(a, a + COUNTIF(B2:a, a) / 100))

HTH

IlirU

Answer 2

My "guess" is that you want something simple like this? Not sure.

Answer 3

Use a formula that counts duplicates and adds a small increment based on how many times the value has appeared so far.

Assume the original numbers are in column A starting in A2. In B2 enter:

=A2 + (COUNTIF($A$2:A2, A2)-1)/100

Then fill the formula down.

Explanation:

• COUNTIF($A$2:A2, A2) counts how many times the value in A2 has appeared up to the current row.
• The first occurrence gives 1 → (1-1)/100 = 0 → no change.
• The second occurrence gives 2 → (2-1)/100 = 0.01 → adds 0.01.
• The third occurrence gives 3 → (3-1)/100 = 0.02 → adds 0.02, and so on.

This automatically appends .01, .02, .03, etc. to duplicates while leaving the first instance unchanged.

---

References:

• Enter a formula
• Use Excel as your calculator in Excel for Mac
• Examples of common formulas in lists