Need a formula 
Author Message
 Need a formula
I need help with a calculation.  I need a formula to display thirds as
either .1 or .2 (1/3, or 2/3).  Is there a place I can post my question and
get a speedy answer?

Calling Filemaker technical support is pretty expensive.

Thanks is advance.

Al



Sun, 21 Jan 2001 03:00:00 GMT
 Need a formula

Hi Al,

I haven't had much time to think this one over as I'm in a hurry, so if I am
way off, please go easy on me...

Couldn't you just multiply your decimal number by 3?  Then run the Int()
function on that result to get values of either 0,1, or 2.  Then add 1 to
this result to get 1,2, or 3.  Then to a NumToText() and concatenate a
period in front of the number.

I feel like this will NOT work depending on how you want to "round" your
numbers.  For example, should .015 round to zero or to one third?  If you
use Round() instead of Int() you will get results of 0,1,2,or 3 which might
be ok for you too.  Then just don't add the extra one I guess...

Perhaps this will get you started.  The entire calc might look like:
  resultField (calc, text) = ["." & NumToText(Int(sourceDecimal * 3) + 1)]

If there is a much simpler method, forgive me - my mind works in mysterious
ways when I am in a hurry. :)

Good luck,

Andrew LeCates
--------------


Quote:

>I need help with a calculation.  I need a formula to display thirds as
>either .1 or .2 (1/3, or 2/3).  Is there a place I can post my question and
>get a speedy answer?

>Calling Filemaker technical support is pretty expensive.

>Thanks is advance.

>Al



Sun, 21 Jan 2001 03:00:00 GMT
 Need a formula

Quote:

>For example, should .015 round to zero or to one third?

Ok, I should slow down. Sorry.  To clarify:

Mean to ask if decimals < one-third should round to zero or one-third.
Should decimals > five-sixths round to one or to two-thirds?

Good luck :)

Andrew



Sun, 21 Jan 2001 03:00:00 GMT
 Need a formula
Thanks for responding to my posting.

Ive created a database to track career stats for players in a baseball
league.  Im nearly finished except for displaying the total innings a
pitcher has completed.  I tracks the stats by year and then use the
sub-summary function to get grand totals for each player in the league.  Im
modeling my database after the Sporting News Baseball Register.

I use a field to input whole innings and a second field to input one-third
of an inning completed.  The thirds are input as a single digit.  The
display shows the whole innings, a period, then the thirds.

So a line item of 11 2/3 innings pitched, displays as 11.2 for a one season.
Another line might read 12.2.  Using sub-summary I get a total of 23.4
innings.  On paper I can equate that to 24.1.  My goal is to have the
database display 24.1.

The formula Im using is

Int(GetSummary(Total innings,Name)) +
Round(Mod(GetSummary(TotThrds,Name),1)/.3,1)

Ive gotten help from one fellow, but the formula did not account for the
thirds field.  I tweaked it enough so it now rounds the total innings.  Its
acceptable for now, but Im looking for perfection.   The formula is
rounding to the total number of innings, whether there are .1 or .2 innings.

I can forward formulas if you need to see where Im stuck if youd like to
help.  Hope this hasnt been too confusing.

Thanks for your consideration.

Quote:



>>For example, should .015 round to zero or to one third?

>Ok, I should slow down. Sorry.  To clarify:

>Mean to ask if decimals < one-third should round to zero or one-third.
>Should decimals > five-sixths round to one or to two-thirds?

>Good luck :)

>Andrew



Tue, 23 Jan 2001 03:00:00 GMT
 Need a formula
Alan, I hope you're using FMPro 3.0 or higher. I would die a thousand deaths
before using the GetSummary function. I used it when it was first introduced,
but found it to be so -ssssllllooooowwwwww- that it made my database solutions
unusable.

The upgrade to v3 or v4 is *well* worth the price of admission.

There, that said, here's a slightly less cumbersome solution to your problem:

In your database, create a RELATIONSHIP call 'NameMatch' which matchs WITHIN
the current file, name=name.
This will allow you to get rid of the GetSummary Functions and use the MUCH
faster Aggregate functions.

In my example, I have 5 fields as follows:

Name   Text - Indexed

Innings  Number - Validated by Calc     = (Innings = Int(Innings)) or
(Mod(Innings,1) = .1)
                                          or (Mod(innings,1) = .2

calc.InningsWhole   Calculation, number = Int(Innings)

calc.InningsPartial Calculation, number = Round(Mod(Innings,1) * 10, 0)

sum.Innings         Calculation, number = Sum(NameMatch::calc.InningsWhole) +
                                          Int(Sum(NameMatch::InningsWhole)/3) +

(Mod(Sum(NameMatch::InningsPartial),3)/10)

This works every time, and the validation rule on the innings field keeps the
innings from being entered as 12.25.
One caveat, sometimes the sum.Innings field won't get updated *immediately*
when an Innings value changes. Simply clicking in the sum.Innings field causes
it to update. This solution also eliminates the Partial Inning data entry
field. All innings and partial innings are entered in the Innings field.

Give it a spin, let me know if it works for you.

Quote:

> Ive created a database to track career stats for players in a baseball
> league.  Im nearly finished except for displaying the total innings a
> pitcher has completed.  I tracks the stats by year and then use the
> sub-summary function to get grand totals for each player in the league.  Im
> modeling my database after the Sporting News Baseball Register.

> I use a field to input whole innings and a second field to input one-third
> of an inning completed.  The thirds are input as a single digit.  The
> display shows the whole innings, a period, then the thirds.

> So a line item of 11 2/3 innings pitched, displays as 11.2 for a one season.
> Another line might read 12.2.  Using sub-summary I get a total of 23.4
> innings.  On paper I can equate that to 24.1.  My goal is to have the
> database display 24.1.

><snip>

--
==========================================================
Michael Little

NOTE: Remove 'PAR3' from my address when replying
==========================================================
no one has to be hip, no one's gotta be out of sight  --RD Davies


Tue, 23 Jan 2001 03:00:00 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Need a formula to generate unique ID (distributed)

2. Need a formula!

3. need magic formula for euro conversion

4. More complicated aggregation using unary operatos, custom rollup formula and custom member formula

5. Using formula field in selection formula

6. mdx calculated measure - formula needed

7. Need help with a formula

8. Need Formula for calc distance from Lat/Lon

9. Need help with formula using ACT! 2000

10. Spreadsheet Formula Needed

11. Need help with Report Builder formula


 
Powered by phpBB® Forum Software