# Calcualting Trinary Numbers.

#### DreamAlchemist

##### Board Regular
Ok Excel as a dec2bin and a bin2dec function. And I was trying to figure out a way to write a formula for a trinary number system (0, 1, 2, 10, 11, 12, 20, 21, 22, 100, ... ) And I was having trouble with how to do it. I am guessing that writing a formula would not be plausable since I would have to check every digit and to write a formual to calculate 212201221100 would be a nightmare. Though I am curious to see if there is an easier way to write the formula to calculate up to 5 digits long. Mine was quite atrotious.

So I was thinking of a macro that would check a cell and then populate the answer in another cell. I know the macro would need to loop through the string of numbers calculating its place and doing the math then adding that number to the prior answer.

I can read code pretty well but I was never good at starting or creating it from scratch. Anyone want to help with how to do this? Again this is for my own edification, and looking at more of the thought process on how to design, create a macro. (I have read books on creating code, and even read pages, but they never mention anything about the thought process behind it and the steps of what to do)

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Code:
``and looking at more of the thought process on how to design, create a macro.``

Well, a good place to start is simply to reproduce the steps to do the calculation manually.

Like, to calculate the value 211 (3) you'd do:

211 (base 3) = 1 * 3^0 + 1 * 3^1 + 2 * 3^2 (base 10) = 22 (base 10)

A simple loop is enough:

Code:
``````Function Tri2Dec(s As String)
Dim j As Long

For j = 1 To Len(s)
Tri2Dec = Tri2Dec + Mid(s, j, 1) * 3 ^ (Len(s) - j)
Next j
End Function``````

or you can use a formula, in B1:

=SUMPRODUCT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*3^(LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))))

Copy down

You can try the inverse function.

Thanks a bunch. I was along the same lines for the macro but just didn't have the s in there. for the Length it seems.

As for the formula I really need to learn indirect. I was doing a calculation for each place and cancatenating them together in the end. I knew my method was way too long and only worked for as many places as I wrote to calculate. Guess I will go learn indirect and play with that function a bit to see how it is used.

Thank you again for your assistance.

deleted because I was backwards

Replies
18
Views
450
Replies
1
Views
66
Replies
0
Views
155
Replies
2
Views
329
Replies
1
Views
326

1,196,057
Messages
6,013,178
Members
441,752
Latest member
klfezler

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back