Substitute function help - number swapping with like numbers

Hi!

I'm working with a raw data set that has values ranging from 1-6 as responses. I need to substitute all 1 values for 6, 2 values for 5, 3 values for 4, and then vice versa. I've tried the 'Substitute function': =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(P5,"1","6"),"2","5"),"3","4"),"4","3"),"5","2"),"6","1") ... the function works to convert values 4 to 3, 5 to 2, and 6 to 1 but not the other way around.

Does anyone know of a more efficient or better way to solve this?

I am using Excel on mac OSventura.

Thanks!

Formula

Formula:

Please Login or Register  to view this content.

Why not just:

=7-P5

Hope this helps.

Pete