Need a formula to (Automatically) change last digit of a multi digit numeric value in a cell

[Douglas997t]

I am working in Excel 365 and am trying to accomplish the following;

I need to auto-correct or change the last digit in a cell where the formula that calculates a multi digit number (ex. $503,500) exists and I want to randomize the last 4 or 5 digits to the left of the decimal with the last number ending up as anything but 0, 5 or 9. or at the very least simply change that last digit to anything but a 0, 5 or 9.

I have tried "Right" but that only seems to apply to text strings and this is a numeric value and cannot function as a text string.

I just can't figure out how to locate the last digit in this cell and if the value of that last digit is 0, 5 or 9, convert it to anything that is not a 0, 5 or a 9.

It might also work with being able to randomize all digits right of the 10,000 mark and have the last right-most digit adhere to the no 0, 5 or 9 rule. And just to be clear, the last digit has to auto-randomize through "1,2,3,4,6,7,8" for each row as the same people could likely see our offer more than once. I can't have it perceived as bot driven and would rather automate this functionality than do it manually...if possible without getting into VBA scripting etc.

Whatever answer comes out of this will be nested within other functionality "IF/THEN" etc.

Thanks!

Douglas

[#2]

@Douglas997t In order to find the last digit of a number you can use:

=MOD(cell_reference,10)

The next step would be to determine if that results in a 0, a 5 or a 9. If so, deduct that number from the original and add any of the numbers 1, 2, 3 ,4, 6, 7 or 8. Randomising the last step is, of course, possible, as demonstrated in the attached example. But when you use the RAND function, this will change the outcome every time the sheet recalculates. And that's probably not what you want. Then you'd need VBA to do the calculation for you and paste the value back into the original cell.

However, VBA is not my strong point.

[#3]

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...

[#4 · Accepted solution]

@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