Formula help Mod and square

M

Mifty

Hi everyone,

Is there any chance that some kind soul could tell me what these 2 formulae
are doing please?

F13 is a date

=(MOD(F13,10^6)-MOD(F13,10^4))/10000

=(MOD(F13,10^6)-MOD(F13,10^4))/10000+MOD(F13,10^4)*100

Thank you very much
 
B

Bernard Liengme

Let's look at the first
Suppose F13 hole 12345678
MOD(F13,10^6) says divide F13 by 1 million and return the remainder: you get
345678
MOD(F13,10^4) says divide F13 by 10,000 and return the remainder; you get
5678
The we subtract to get 340000; then divide by 10,000 to get 34

In the second one we find MOD(F13,10^4) again = 5678
Multiply by 100: 567800

Then we add this to the first answer: 567834

Not sure why you would want this but that is what they do

best wishes
 
M

Mike H

The first one
=(MOD(F13,10^6)-MOD(F13,10^4))/10000

divides the date by 10^6 or 1 million and gets the modulus (remainder)= 39541
or if converted to a date the same as in F13
divides the date by 10^4 or 1 thousand and gets the modulus (remainder) = 9541
it subtracts these 2 values and get 30000 and divides that by 10000 and gets
the answer of 3

I think from that you should be able to work our what the second one is
doing while I work out why!!

Mike
 
K

Kevin B

=MOD(Argument1, Argument2)

The MOD function takes the argument 1 value, divides it by the argument 2
value and returns the remainder. So in the formula above it calculates the
first MOD function, dividing the value in F13 by 10^6, returning the
remainder. It then calculates the second MOD function, dividing the value in
F13 by 10^4, returning the remainder.

The formula then subtracts remainder 2 from remainder 1 and divides the
result by 10,000

Hope this helps
 
M

Mifty

Thank you Bernard,

In a very befuddled way I'm trying to work out what they are trying to do
but it's just giving me a headache at the moment.

Maybe you or Mike would be able to work it out if I gave you the next one :)

=IF(LEN(F13)=8,(IF(I13=1,-75.622,IF(J13 = 1, -24.226,0))),"")


F13 = date ddmmyyyy I13=IF(H13>(2004-2)*100+9,1,0)
J13 = =IF(H13>9+100*(2006-5),IF(G13<7,1,IF(G13>9,1,0)),0)

G13 = F13 as value
H13 = Lookup returning a value from 0-1

I think what they are trying to do is assign a value to date differences
through the year but there must be an easier way.

Could they have used datedif and then a lookup I wonder?

Do you think the 2 formulae using MOD were so that they could do
calculations with dates as Excel numbers and Years e.g. 2006 (J13)? Would it
not have been easier to use a specific date in 2006 and use the excel date to
do calcs?

Confused .......
 
M

Mifty

Thanks Mike,

I've replied to Bernard, wondering if anyone could figure out what's going
on if you don't mind looking.

Cheers again
 
M

Mifty

Hi Kevin,

Thank you for answering :)

I've posted more to Bernard re what the spreadsheet is trying to do, if you
don't mind taking a look.

Many thanks
 
D

David Biddulph

My guess is that F13 isn't actually a date in Excel terms, but a number like
25122008 being used to represent a date.
 
B

Bernard Liengme

If F3 is a date in the form 12282008 or 1012008 etc then
=DATE(MOD(F13,10000),F13/10^6,MOD(INT(F13/10000),100))
will return a true date value
best wishes
 
M

Mifty

Hi David,
Yep! just so

Many thanks

--
Mifty


David Biddulph said:
My guess is that F13 isn't actually a date in Excel terms, but a number like
25122008 being used to represent a date.
Click to expand...
 
Top