Forum Discussion
Need a formula to (Automatically) change last digit of a multi digit numeric value in a cell
Douglas997t Sorry for not being clear. Perhaps an example will help.
Let's say the original amount is 25. Then MOD will find that the number ends with 5. LOOKUP and RAND will then pick a number from the list {1,2,3,4,6,7,8}, for example 7. The calculation to be made is then:
25 (original) - 5 (last digit found with MOD) + 7 (the random number) = 27
Hello Riny and thanks for the help on this! I am not quite understanding the logistics of, 'If so, deduct that number from the original and add any of the numbers 1, 2, 3 ,4, 6, 7 or 8." Deduct the original value in its original form before curing any iteration of "0, 5 or 9" then adding one of "1, 2, 3, 4, 6, 7 or 8" to that original number?
As far as the randomization, I understand about the VBA component and that is likely a bit deeper than I wish to go on this anyway. So, no problem on that component. It falls into the category of "Oh Well!". I did notice your attachment and saw the helper column but just wasn't understanding how you got there in the right most column. Sorry for my density tonight.
I did try two other approaches that apparently both have issues, see attached. I really thought one of these might work...
Douglas997t Sorry for not being clear. Perhaps an example will help.
Let's say the original amount is 25. Then MOD will find that the number ends with 5. LOOKUP and RAND will then pick a number from the list {1,2,3,4,6,7,8}, for example 7. The calculation to be made is then:
25 (original) - 5 (last digit found with MOD) + 7 (the random number) = 27