Is there a function in Excel to convert a number to base 5?

[Kurt]

Hello All,

Yes I searched the board here, but I wanted to see if anyone has had to do this before. I want to be able to do Base 5 math. For instance if in cell A1, A2, A3 I have 124 and I add a "1" to the 4 in cell A3, I want it to be displayed on the worksheet as a "0".

Has anyone had to do this before?

Again thanks for any and all help in advance!

Best,

Kurt

 

[Norie]

Kurt

Wouldn't 4+1=5 in base 5 be 10?

 

[Rick Rothstein]

Can you expand on what you are looking for, namely, why did you mention the 1 and the 2 you have in A1 and A2? Are we supposed to treat the combined digits in A1, A2 and A3 as the number 124? Or did you just want the addition in a cell to revolve through the numbers 0, 1, 2, 3 and 4? If the latter, then you want he MOD function.

A3: 4
B3: =MOD(A3+1,5)

 

[Kurt]

Hello Rick, Norrie and all,

If I add a 1 to a 4 it should be 0 in base 5.

So 1+1 = 2, 1+2 = 3, 1+3 = 4, 1+4 = 0

Same on the subtraction part.

I hope this helps clarify.

Thanks,

Kurt

 

[Norie]

Kurt

No it shouldn't.

4 + 1 = 5

In base 5 that is 10 = 5¹ x 1 + 5⁰ x 0

Perhaps you do need something with MOD as Rick suggests.

 

[Ruddles]

These user-defined functions convert an input decimal number to a number in another base (up to 36) and back again:-

Option Explicit
 
Public Function ConvertToBase(ByVal aDecimal As Variant, ByVal aBase As Integer) As Variant

 Application.Volatile
 
 Const alpha As String = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
 
 Dim temp As Variant
 
 ConvertToBase = CVErr(xlErrValue)
 
 If aBase < 2 Then Exit Function
 If aBase > 36 Then Exit Function
 If aBase <> Int(aBase) Then Exit Function
 
 ConvertToBase = ""
 
 Do Until aDecimal = 0
   temp = aDecimal Mod aBase
   ConvertToBase = Mid(alpha, temp + 1, 1) & ConvertToBase
   aDecimal = (aDecimal - temp) / aBase
 Loop
 
End Function

Public Function ConvertFromBase(ByVal aValue As Variant, ByVal aBase As Integer) As Variant

 Application.Volatile
 
 Const alpha As String = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
 
 Dim temp As Variant
 
 ConvertFromBase = CVErr(xlErrValue)
 
 If aBase < 2 Then Exit Function
 If aBase > 36 Then Exit Function
 If aBase <> Int(aBase) Then Exit Function
 
 aValue = UCase(aValue)
 
 ConvertFromBase = 0
 
 Do Until Len(aValue) = 0
   temp = Left(aValue, 1)
   aValue = Mid(aValue, 2)
   ConvertFromBase = ConvertFromBase * aBase + (InStr(alpha, temp) - 1)
 Loop
 
End Function

So if you have a number in Base 5 and you'd like to add another number in Base 5 to it and express the result in Base 5, you'd do:-

result = converttobase(convertfrombase(344, 5) + convertfrombase(334, 5) ,5)

(equivalent to 99 + 94 = 193).

Is this any use to you?

 

[pgc01]

Hello Rick, Norrie and all,

If I add a 1 to a 4 it should be 0 in base 5.

So 1+1 = 2, 1+2 = 3, 1+3 = 4, 1+4 = 0

Same on the subtraction part.

I hope this helps clarify.

Thanks,

Kurt

Hi

What you are describing is arithmetic modulo 5, not arithmetic operations using numbers written in base 5.

4 + 1 ≡ 0 mod 5

which means that 4 plus 1 is congruent with 0 modulo 5

If this is what you need you may try Rick's solution.

If it were an operation in base 5 numbers you'd have (like Norie explained)

4₅ + 1₅ = 10₅

or 4 plus 1 is equal to 10, with numbers in base 5.

Please check what your need

 

[Kurt]

Hello Norie and all,

Yep you were right as usual Norie Rick's Mod function worked quite well.

Thanks to all who answered and I will try out Ruddle's funciton later.

Have a great day everyone!