# Can you spot the error in this excel function?

A user's come to me asking why this function isn't doing what he expects it to do.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F7, "5", "0"), "4", "2"), "3", "3"), "2", "4"), "1", "5")

Can you work out what the user thinks it ought to do and why it's not working as expected?

-
We'd need to know what it is supposed to do, and what it actually does. I mean, it would be very helpful. –  Bobby Aug 5 '10 at 13:14
All the user was doing was taking a single number 1-5 and converting it to 0,2,3,4,5. A little logic teaser for a Thursday afternoon! –  oharab Aug 5 '10 at 13:36
-1 for asking questions that don't need answering. I'll happily help with solving people's problems, but this is not the place for quizzes for fun. –  Torben Gundtofte-Bruun Aug 5 '10 at 14:12

Looks like the intention is to change some numbers in a cell so that 5 is replaced with 0, 4->2, 3->3, 2->4 and 1->5.

Problem is it does it in single steps so, after the 4s are changed to 2s, you later change all the 2s (including the one's that were 4s) in to 4s.

Example:

12345 //lets look at each step...
12340 // 5 -> 0
12320 // 4 -> 2
12320 // 3 -> 3  : why is this step used?
14340 // 2 -> 4  : changes the 4 that was changed above back again
54340 // 1 -> 5

54320 // expected result?

If you wish to stick with this method you need to change the 4s to something irrelevant (say "X") and then (after changing the 2s) change the Xs in to 2s.

-
+1 - Beat me to it! –  JNK Aug 5 '10 at 13:17
Yup, that's the problem. My solution was to create a look up table elsewhere in the document and use vlookup. Had me flummoxed for a little while until I broke it down like you did! –  oharab Aug 5 '10 at 13:36

Here's an easier way (if you're only changing single-digit numbers):

=IF(6-F7=1,0,6-F7)
-
This only works if it is a single digit number. –  Jarvin Aug 5 '10 at 21:07
@Dan, I was thinking that was the user was doing, but see now he might have been doing this on larger numbers or text strings. I'll edit in the constraint. –  Lance Roberts Aug 5 '10 at 21:27