CODEWITHSUNDEEP
sql-serverexcelmodulo
Сашко Мицевски
Сашко Мицевски

Reputation: 303

How to convert this SQL Server formula to an Excel formula?

I have tried all day long but cant make it to work ...

    declare @a as nvarchar(max)
    declare @b as nvarchar(max)
    declare @c as nvarchar(max)
    declare @d as nvarchar(max)
    declare @k as integer

    set @b = '00115'
    set @d = '00001'
    set @c = '10000952010'

    select ((CONVERT(BIGINT, CONVERT(VARCHAR(2), CONVERT(BIGINT, @b + @d) % 97) + @c) * 100) % 97)

The result from this formula is 77

My Excel formula so far looks like this, but it's not correct

MOD(((MOD(11500001;97)+10000952010)*100);97)

Thanks

Upvotes: 0

Views: 71

Answers (1)

basic
basic

Reputation: 11968

Your mistake is operator +. In SQL server "formula" it concatenates values, in Excel it sums values. Correct excel formula would be

MOD(((MOD(11500001;97)&10000952010)*100);97)

But then you get a "big number" problem with the MOD function. So the complete solution would be:

=MOD(MOD(((MOD(11500001;97)&10000952010)*100);134217728*97);97)

Upvotes: 2

Related Questions