Inner Workings of INT and MOD functions... 2

[ThatNewGuy]

Hello,

I picked up some great pointers on here about using both the INT and MOD functions in Excel. But now I'm trying to find a work around solution to a problem i'm having in a program that isn't as powerful as Excel when it comes to preset formulas. The program doesn't offer the INT or MOD functions so I'm trying to figure out the inner workings of the functions themselves to see if I can build a formula that would replicate what these two functions can do.

I'm working with an order quantity that changes on a daily basis and with a set number of items that fit into a box. So part number A may fit 90 into a box. So if the customer orders 2500 qty/90 boxes = 27.77 boxes. The program I'm working with only offers the basic +, -, *, /, (), etc. and it's restricting the number from rounding down from 27.77 to 27. Is there a way to manually build the INT function so that this number would be rounded down to the nearest integer? I know this sounds crazy not being able to use the INT function, but I can't help the shortcomings of this program.

I'm also working with the MOD function and I found this as MOD (n,d) = n-d*INT(n/d). As defined from Microsoft, but once again this doesn't help me if I'm not working with MOD or INT. I guess the MOD main function is to return the remainder after the number is divided by a divisor. I use the MOD function for the remaining .77 from the 27.77 boxes which calculates the final box holding 69 items. All in all I would need 27 full boxes with 90 and 1 box with 69 items. Any creative solutions, suggestions, or ideas would be greatly appreciated. Thanks.

 

[SkipVought]

Hi,

"The program I'm working with only offers the basic +, -, *, /, (),"

What program is that? Someone may know something about it's features.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[AnotherHiggins]

FYI for others reading this thread: the original thread referred to can be found here: thread68-1412273

I'm with Skip - what application are you working with?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[ThatNewGuy]

The other program is called "PrintSmith". It's specialized for the printing industry to estimate jobs, create job invoices, track paper/stock, monitor your shops costing. Overall it's very powerful, but I'm in an add on module called Report Writer for Printsmith and it's set up much like the reports section in Access. Pulling data from varying fields, to customize your reports, etc. The area that I was refering to is the formula builder within Report Writer. This section is limited compared to applications like Excel and Access. It seems that you can make formulas work, but you have to build the functions from the ground up. I can't just drop in a MOD function to solve a very simple problem. It's frustrating, and yes, I have already reached out to PrintSmith's technical support and they aren't exactly in the business of writing formulas.

 

[SkipVought]

Are there any functions available at all?

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[SkipVought]

Are there any functions available at all?

You might want to do the heavy lifting in Excel and export data to this application in ready format.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[IknowMe]

You need to be able to consider digits before and after your decimal seperately. Can you do this? As Skip asks, what functions do you have?

[small]Sometimes you gotta leave your zone of safety. You have to manufacture Inspirado. You gotta get out of the apartment. You've got to run with the wolves. You've got to dive into the ocean and fight with the sharks. Or just treat yourself to a delicious hot fudge sundae........ with nuts. - Jack Black[/small]

 

[ThatNewGuy]

The functions that I know of are very limited.

The main issue might be the inability to seperate the decimals, but I'm not positive.

Even if this is the case is it possible to have a general formula that would state if a number, like 4.4, is greater than the whole number, 4, then subtract a range from .01 to .04, or something to that effect. You can build things around "If, then statements" to a degree. I've attached a link to a pdf document. Please review this, I think seeing this section of the program would help, rather than have me stumble along explaining things.

Thanks

 

[SkipVought]

But if you have 4.4, you have no mathematical means in your application of determining that 4 is the integral part of the number OR string.

As I recommended before, do the calculations in Excel and export to your application.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[AnotherHiggins]

No offense, but I'm not going to learn a new application right now.

I did, however, look over the linked file and I don't see anything that will help you. I'd hope to see functions like LEFT, RIGHT, FIND/SEARCH/INSTR, but none of those are listed.

I've already provided formulas that will get you what you need in Excel, so I think you should go with Skip's suggestion and use Excel to get the results you need, then import the file into PrintSmith.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[dallen43]

Can you group functions?
For example, you could multiply 2700 by 100, divide by 90, get the interger, convert to text, check the right two characters, if they are = "00", use the interger/100 else use the interger/100 then add 1. It is rather complex and using excel would be a lot easier.

 

[Markus4]

Hi,

in Excel date and time are stored in a number, with the dates left and the time right of the decimal point. As I just galloped through your upload on [ignore]

http://www.box.net/shared/d6vh5e4cnr

[/ignore] and did not find the date/time format PrintSmith is using I can only provide a hint:

Couldn't you simulate the INT-function by converting your number to a date and subtracting the time?

E.g.: you have 4.5. In Excel this would show as Jan 4, 1900, 12 o'clock. If you just subtracted 12 o'clock you would get your desired value of 4.

Markus.

 

[Markus4]

I forgot:

In the same way you could simulate the MOD-function: from January 4th, 1900, 12 o'clock you would subtract the four days and get your desired 12 o'clock which in Excel is .5

Markus.

 

[ThatNewGuy]

Hello Markus,

Thanks for the creative direction. I never thought about converting the number into a date/time. This can be done in Printsmith. The only question I have is if you convert the number to a date does that put any restrictions on how many days you can use? For example, can you only use 30 or 31 days depending on the number of days in a specific month?

The reason I ask is if one part number requires 45 or 50+ boxes for one order then you can see how you would be limited with how many days are actually in the month.

Either way I appreciate your response.

 

[SkipVought]

The NUMBERS have nothig to do with the number of days in a month.

faq68-5827

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[IknowMe]

I like it Markus4 have a star.

[small]Sometimes you gotta leave your zone of safety. You have to manufacture Inspirado. You gotta get out of the apartment. You've got to run with the wolves. You've got to dive into the ocean and fight with the sharks. Or just treat yourself to a delicious hot fudge sundae........ with nuts. - Jack Black[/small]

 

[Markus4]

ThatNewGuy:

never mind about how many days there are in a month, because this is the wrong path. What I suggest is that you convert your number into a date/time format, do operations simulating INT and MOD, and re-convert the results into a number.

The idea behind this is: INT and MOD take place before and after the decimal point of a number. In Excel, data format is similar: date is before, time is after the decimal point.

No offense: I do not want to learn a new program. That is why you will have figure out yourself how to do this in PrintSmith. I just wanted to give you a hint.
Maybe someone else around here can assist you more specifically.

---

MrMilson:

Thank you very much for your star. Seeing great programmers around (SkipVought, TonyJollans) it is so so flattering that you gave me a star. Thank you so much.

Markus.

 

[SkipVought]

Have one from me too, for an outside-the-box solution.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}