Formula Conversion Help

Post a reply

Confirmation code
Enter the code exactly as it appears. All letters are case insensitive.
:D :) ;) :( :o :shock: :? 8-) :lol: :x :P :oops: :cry: :evil: :twisted: :roll: :!: :?: :idea: :| :mrgreen: :geek: :ugeek: :arrow: :angel: :clap: :crazy: :eh: :lolno: :problem: :shh: :shifty: :sick: :silent: :think: :thumbup: :thumbdown: :salute: :wave: :wtf: :yawn: :facepalm: :bravo: :dance: :beard: :morebeard: :xmas: :HeHe: :trollface: :cookie: :rainbow: :monkeysee: :monkeysay: :happybday: :headwall: :offtopic: :superhappy: :terms: :beer:
View more smilies

BBCode is ON
[img] is OFF
[flash] is OFF
[url] is ON
Smilies are ON

Topic review

Expand view Topic review: Formula Conversion Help

Re: Formula Conversion Help

Post by deltran552 » 17 Jul 2017, 15:18

Hi all,

I actually figured out what the problem with my script was (I haven't tried jeeswg's solution or Helgef's). In my original program, prior to adding a selectable date via a GUI calendar, I had included SetFormat, Float, 0.2 in the header. It seems that I accidentally left that out in my improved program.

Thanks for the prompt suggestions, I really appreciate them!

Re: Formula Conversion Help

Post by jeeswg » 13 Jul 2017, 21:26

Can you give an/some example values where your script fails?

I believe that all you have to do is replace any use of Mod, with my JEE_Mod function.

Code: [Select all] [Expand]GeSHi © Codebox Plus

I tried creating a function that works like Excel's TRUNC function (I would test this thoroughly before any industrial use), and also functions to convert between Excel and AHK style dates:

Code: [Select all] [Expand]GeSHi © Codebox Plus

[EDIT:] Here's some code to truncate to an integer, which is another way of doing what is done in Helgef's trunc function.

Code: [Select all]GeSHi © Codebox Plus

q:: ;truncate to an integer (i.e. round to the nearest integer towards 0)
vNum := 123.456
MsgBox, % vNum := (vNum >= 0) ? Floor(vNum) : Ceil(vNum)
vNum := -123.456
MsgBox, % vNum := (vNum >= 0) ? Floor(vNum) : Ceil(vNum)

Re: Formula Conversion Help

Post by Helgef » 13 Jul 2017, 03:25

Hi. You are correct about floor. You can try to use this,

Code: [Select all]GeSHi © Codebox Plus

return substr(f,1,instr(f*1.0,".")-1)

Possibly related: Mod function returning wrong value?

Good luck.

Formula Conversion Help

Post by deltran552 » 12 Jul 2017, 18:12

Hi All,

I am having some trouble getting a value to calculate correctly in AHK, that is calculating correctly in MS Excel. I've converted the formula to what I *think* is correct given the difference in AHK commands and Excel formula functions, but what I'm finding is that one digit is off.

Here's the original formula from Excel:

Code: [Select all]GeSHi © Codebox Plus

=((MOD(DAY(B14),10)*MONTH(B14))*100000)+((MOD(YEAR(B14)/10,10)*10)-INT(10*MOD(YEAR(B14)/100, 10))*10)*10000+INT(10*MOD(YEAR(B14)/100, 10))*1000+70+(TRUNC(DAY(B14)/10))

And here's the AHK code. I broke it into chunks to make it more manageable:

Code: [Select all] [Expand]GeSHi © Codebox Plus

I've used floor() because I don't think AHK has a function similar to int() or trunc() that the Excel formula is using. I do think it's important to note that trunc(-3.5) would return -3, while floor(-3.5) would return -4 (at least, this is my understanding on how floor() would work).

I'm not sure where I'm going wrong in this. I have another program with the exact same calculations to generate this code, the only difference in that program is that you can't select a new date (it's using DateTime and a_now to get the year, month, etc). In this program, I've used MonthCal.

Thanks in advance for the help.