Repeating # formula
this is what i have - but not working
IF([row #]18, =1, "2", OR(IF([row #]18, =2, "3", OR(IF([row #]18, =3, "4", OR(IF([row #]18, =4, "5", OR(IF([row #]18, =5, "6", OR(IF([row #]18, =6, "7", OR(IF([row #]18, =7, "8", OR(IF([row #]18, =8, "9", OR(IF([row #]18, =9, "1",
i want if the row above is a 1, the next row to equal 2
but when it reaches 9 - to be assigned to 1
repeating 1-9 on each row - so when new items come in gets a
Best Answer
-
I agree with @Kimberly Loveless, you shouldn't need the OR function.
You can probably also shorten the formula to the following:
=IF([row #]18 = 9, 1, IF([row #]18 > 0, [row #]18 + 1))
Answers
-
I don't think that you need the OR functions. With nested IF statements if the first one is not met it automatically moves on to the next. So the formula below should work
=IF([row #]18=1, "2", IF([row #]18 =2, "3", IF([row #]18 =3, "4", IF([row #]18 =4, "5", IF([row #]18 =5, "6", IF([row #]18=6, "7", IF([row #]18=7, "8",IF([row #]18 =8, "9", IF([row #]18, =9, "1"
-
I agree with @Kimberly Loveless, you shouldn't need the OR function.
You can probably also shorten the formula to the following:
=IF([row #]18 = 9, 1, IF([row #]18 > 0, [row #]18 + 1))
-
@Kimberly Loveless - thank you. I tried it and it works for the first 9 numbers, (i dragged the formula down since i cant do a column formula on it )
so the one after 9, give #INVALID DATA TYPE
Help Article Resources
Categories
Check out the Formula Handbook template!