Apr 07 2020 04:40 AM
Hello friends,
I am looking for a formula in Excel to auto generate values based on data in another column.
Example:
Column A Column B
Value x Starting value = 1
Value y Next value = 2
Value y Same value =2
Value z. Next value = 3
Repeat this process up to 5 and then start over again at value 1.
Any support would be greatly appreciated.
Apr 07 2020 05:35 AM
Solution
In the following formula, I do make the assumption that you enter the starting value in cell B1. This formula then works when you copy it all the way down as the attached spreadsheet shows.
=IFS(AND(A2<>A1,B1=5),1,A2<>A1,B1+1,A2=A1,B1)
(I haven't tried it, but it stands to reason that you could switch around the sequence of those conditions and their consequences, for example, first checking whether A2=A1 and then leaving the value in column B the same. This just happens to be the way I wrote it.)
Apr 07 2020 05:39 AM
Apr 07 2020 05:52 AM
You're very welcome.
As it happens, this is (if memory serves me) the first time I've ever employed the IFS function. I started off trying to nest IF within IF, and was getting confused as I went; said to myself, maybe that IFS function--I'd read about it and seen it in other postings here--would serve well. And not only did it work well; it was also easy to use.
So thank you for that learning opportunity. I'll be using it more myself.
Apr 07 2020 05:35 AM
Solution
In the following formula, I do make the assumption that you enter the starting value in cell B1. This formula then works when you copy it all the way down as the attached spreadsheet shows.
=IFS(AND(A2<>A1,B1=5),1,A2<>A1,B1+1,A2=A1,B1)
(I haven't tried it, but it stands to reason that you could switch around the sequence of those conditions and their consequences, for example, first checking whether A2=A1 and then leaving the value in column B the same. This just happens to be the way I wrote it.)