| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula help Mod and square
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 -- Mifty |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula help Mod and square
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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Mifty" wrote in message ... 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 -- Mifty |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula help Mod and square
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 "Mifty" wrote: 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 -- Mifty |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula help Mod and square
=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 -- Kevin Backmann "Mifty" wrote: 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 -- Mifty |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula help Mod and square
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(H139+100*(2006-5),IF(G13<7,1,IF(G139,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 ....... -- Mifty "Bernard Liengme" wrote: 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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Mifty" wrote in message ... 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 -- Mifty |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula help Mod and square
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 -- Mifty "Mike H" wrote: 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 "Mifty" wrote: 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 -- Mifty |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula help Mod and square
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 -- Mifty "Kevin B" wrote: =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 -- Kevin Backmann "Mifty" wrote: 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 -- Mifty |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula help Mod and square
My guess is that F13 isn't actually a date in Excel terms, but a number like
25122008 being used to represent a date. -- David Biddulph "Bernard Liengme" wrote in message ... 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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Mifty" wrote in message ... 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 -- Mifty |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula help Mod and square
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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Mifty" wrote in message ... 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 -- Mifty |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula help Mod and square
Hi David,
Yep! just so Many thanks -- Mifty "David Biddulph" wrote: My guess is that F13 isn't actually a date in Excel terms, but a number like 25122008 being used to represent a date. -- David Biddulph "Bernard Liengme" wrote in message ... 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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Mifty" wrote in message ... 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 -- Mifty |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula help Mod and square
Thanks Bernard :-)
-- Mifty "Bernard Liengme" wrote: 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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Mifty" wrote in message ... 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 -- Mifty |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I a rotate my square radar chart (square-diamond)? | Charts and Charting in Excel | |||
| how do i use square root on excel - what is the formula | Excel Worksheet Functions | |||
| how to center a square plot area in a square chart | Charts and Charting in Excel | |||
| Regression Output -- R Square versus Adjusted R Square | Excel Discussion (Misc queries) | |||
| Root mean square (RMS) error formula | Excel Worksheet Functions | |||
Linear Mode
