CODEWITHSUNDEEP
c#excelformula
Simon_679834563
Simon_679834563

Reputation: 17

Excel Formula convert to C#

Im new to c# but trying to run this formula from a WPF.

=(BIN2DEC(RIGHT(DEC2BIN(MOD(INT(A1/16777216), 256),8), 3)) * 16777216) + (MOD(INT(A1/65536), 256) * 65536) + (MOD(INT(A1/256), 256) * 256) + MOD(A1,256)

started to try and work through but think im not even close.... if anyone has any pointers..... 

decimal A1 = Convert.ToInt32(textBox1.Text);
A1 = (A1 / 16777216);
A1 = decimal.Truncate(A1);
A1 = decimal.Remainder(Left, Right);
Convert.ToByte(A1);
String Number = A1.ToString();
Number.Reverse();
Number.Remove(3);
Number.Reverse();
A1 = Convert.ToByte(Number);

and so on....

----------------- UPDATE----------------------------------

Sorry, if it's clearer im trying to achieve this..

A 32bit number should translate into an 8 or 9 digit (27bit binary) number. If a 9 digit decimal number is produced, you will need to drop the most significant decimal digit to show the number that I want.

Examples: 467597668 converts to 64944484

705313524 converts to 34224884

4294967295 converts to 134217727

Im trying this now but get an incorrect number ?

        int A1 = Convert.ToInt32(textBox1.Text);
        A1 /= 16777216;
        A1 &= 7; 
        A1 *= 16777216;
        int A2 = (((A1 >> 16) & 255) << 16);
        int A3 = (((A1 >> 8) & 255) << 8);
        int A4 = (A1 & 255);
        textBox2.Text = (A1+A2+A3+A4).ToString();

Upvotes: 0

Views: 1450

Answers (3)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 727077

Since you have a nice integer types and bit operations in C#, you do not have to jump through the same hoops as in Excel.

Here is how you convert the initial part (BIN2DEC(RIGHT(DEC2BIN(MOD(INT(A1/16777216), 256),8), 3)) * 16777216):

int A1 = Convert.ToInt32(textBox1.Text);
A1 /= 16777216; // This also truncates the result; INT(A1/16777216)
A1 &= 7; // This takes the last three bits of the number: BIN2DEC(RIGHT(DEC2BIN(MOD(A1,8),3))
A1 *= 16777216; // A1 * 16777216

(MOD(INT(A1/65536), 256) * 65536), (MOD(INT(A1/256), 256) * 256), and MOD(A1,256) are even simpler:

(((A1 >> 16) & 255) << 16)
(((A1 >> 8) & 255) << 8)
(A1 & 255)

>> means "shift (binary representation) to the right"; << means "shift (binary representation) to the left". Shifting by one is equivalent to multiplying or dividing by two in the same way that shifting a decimal number is equivalent to multiplying or int-dividing by ten.

& means bitwise AND. 255 is a number with the last eight bits set to one; AND-ing with it keeps the last eight bits of the original number.

EDIT : Here is a corrected version of your re-write that returns the right result:

int A0 = 467597668;
int A1 = A0 / 16777216;
A1 &= 7;
A1 *= 16777216;
int A2 = (((A0 >> 16) & 255) << 16);
int A3 = (((A0 >> 8) & 255) << 8);
int A4 = (A0 & 255);
Console.WriteLine(A1 + A2 + A3 + A4);

Upvotes: 3

Phil Bolduc
Phil Bolduc

Reputation: 1656

This should help, from what I gather,

RIGHT(DEC2BIN(MOD(INT(A1/16777216), 256),8), 3)) * 16777216

is equivalent to 

(((a1 / 16777216) % 256) & 7) * 16777216

You should be able to use this to derive the other 3 components.

Upvotes: 0

Alexei Levenkov
Alexei Levenkov

Reputation: 100630

Looks like some bit operations to me based on constants and operations. In C# you should simply use bit-wise "and" (&), "or" (|) and shifts (<<, >>) to do that.

Upvotes: 0

Related Questions