Need Help Please with Excel Formula

Lindee

New Member
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

MrExcel MVP, Junior Admin
Welcome to the Board!

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

Lindee

New Member
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. :eek:
 

Joe4

MrExcel MVP, Junior Admin
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

New Member
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

MrExcel MVP, Junior Admin
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)?
 
Last edited:

Lindee

New Member
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

MrExcel MVP, Junior Admin
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

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

<tbody>
</tbody>
=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>
</tbody>

<colgroup><col width="356"></colgroup><tbody>
</tbody>

<colgroup><col width="220"><col width="187"><col width="178"></colgroup><tbody>
</tbody>
 

Joe4

MrExcel MVP, Junior Admin
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.
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...
Top