Need Help Please with Excel Formula

[Lindee]

I am new to Excel and I'm trying to write a formula that will change the value of a set of numbers to a different value. i.e. 116 ~161~ 161 i.e. 225~ 552~525~ 520 for numbers 1 thru 0.
Would some kind soul Help me. I have started writing with the Subsitute function however I'm not advanced enough to go beyond the 1st 2 conversions.
Thanks in Advance.
Lindee

 

[Joe4]

Welcome to the Board!

I think you need to describe to us in more details exactly what the conversion rules are.

 

[Lindee]

Thanks Joe for responding, I trying to take #'s 1 thru 9, in a matrix and it should be a matrix. if I for example have the number set of 116 I want to have a return value of 661, next value returned would be 616. In other words 1=6, 2=7, 3=8, 4=9, 5=0, so if you have a three digit number of 123 it would convert to 678, if you have double digits 232, it would convert to 787 then 877~434~...forgive me it is a permutations matrix, I think.

 

[Joe4]

I am sorry, it is still unclear to me.

If you have: 1=6, 2=7, 3=8, 4=9, 5=0, 6=1, 7=2 ,8=3, 9=4, 0=5,
then it makes sense how you get from 116 to 661 and 123 to 678 and 232 to 787.
But I do not understand how you then get 116 to 661, or 787 to 877.

You need to remember, while you are intimately familiar with the problem you are working on, we have NO idea what you are doing. All that we have is whatever you share with us.
So it is essential that you provide enough information that people who have no idea what you are trying to accomplish can understand it and help you.

 

[Lindee]

I'm sorry,,, please forgive me, the 116 converts to 661 by doubling the 6 to 66 then retaining the 1 , with the 787 my mistake,, double the 8 to 88 and retain the 7,= 878 so I'm converting the single digit to a double digit .
116~661
1=6 so 11 becomes 661
787~887
Double the single 8 to 88
887~7=2 ~ 228
7=2 double the 7 to 77 which is 228
8=3 so 282 could become ~332
the position of the numbers does not matter. The whole idea is to convert the numbers. 11=66, 22=77, 33=88, 44=99, 55=00 while retaining the single digit. Thank you for your help

 

[Joe4]

OK, so sometimes the single digit is doubled, and then the other digit is retained.
Then it appears that other times, digits converted.

So, what decides when to do what (double or convert)?

This is rather odd. Can you tell me exactly what this is used for (just curious)?

 

[Lindee]

Sure, I noticed a pattern in Pick 3 lottery. Yes, it can be mind bogging. I thought an excel formula would help with the headaches. LOL I seem to be a bigger headache to you. sorry.. as for what caused the conversion, it seems to be a math formula , my guess is it's based on Fibonacci numbers. however that is just a guess. Tried a Fibonacci calculator, again to many numbers to Convert. My best guess it that it's derived from past numbers based on my tracking, hence the excel formula.

 

[Joe4]

I hope you are not trying to guess the winning numbers of a lottery! That is usually an exercise in futility!

If you want any more help with this question, please answer the other question in my previous post.

OK, so sometimes the single digit is doubled, and then the other digit is retained.
Then it appears that other times, digits converted.

So, what decides when to do what (double or convert)?

 

[Lindee]

the previous numbers , here is a basic formula that I came up with using the numbers 11 and converting to 66

<tbody>
[TD="class: xl65, width: 220"] 11.00
[/TD]
[TD="class: xl65, width: 187"] 66.00
[/TD]

</tbody>

<colgroup><col width="356"></colgroup><tbody>
[TD="class: xl67, width: 356"]=SUBSTITUTE(11,11,66), a very simple formula however if I knew how to nest formulas , the set of 116 could be converted to 661~616 and so on.

Yes it is very difficult to win, now considering that I have acknowledge that fact, tell me what else does an elderly, single lady have to do with her time, lol, some seniors have many other hobbies this is just one of mine. I play on paper, until.... so if you would be so kind as to help with this formula, I will stop bothering you, you young wipper-snapper. lol

<tbody>
[TD="class: xl65, width: 220"][/TD]
[TD="class: xl65, width: 187"][/TD]
[TD="class: xl66, width: 178"]
[/TD]

</tbody>

[/TD]

</tbody>

<colgroup><col width="220"><col width="187"><col width="178"></colgroup><tbody>
[TD="class: xl65, width: 220"][/TD]
[TD="class: xl65, width: 187"][/TD]
[TD="class: xl66, width: 178"][/TD]

</tbody>

 

[Joe4]

I am still trying to understand the logic behind the formula.

In some instances, you appear to be doing the substitution/replacing, like you have shown.
But in other instance, you do the value conversion (1=6, 2=7, 3=8, 4=9, 5=0,...)

So I am trying to understand when (under which conditions) you want to do each of these things.
That is usually the first step to coming up with complex formulas, first layout ALL the rules in plain English, and then work from there.

If it is complex, it may have to involve VBA in the solution. Is that acceptable? I know some people don't want to wade into that.