formatting to a given number of sigfigs

[chemparrot]

Here is a wonderful, chewy problem someone might be able to help me with. I am formatting chemical concentrations, and want to display them to a specified number of significant figures. I have a calc going back to the FM2 days that I am trying to update for a couple of reasons. First, there seems to be an anomaly in FM's Round function and so sometimes the output of this calc is not what we want. Second, I want to make the calc just right, so I can make a custom function out of it in Developer.

So, the issue with the Round function. Have you ever noticed what happens when Round(number, precision) gets very close to a whole number? For example:

Round (1.08;1) returns 1.1 as expected, but Round (1.03, 1) returns 1 (NOT 1.0!)

Similary, Round (0.794, 2) returns .79, but Round (0.795, 2) returns .8 (not .80)

So, it looks to me like when you are very close to a whole number, the precision you specify might be a moot point. I asked FileMaker about this years ago, they insisted this was not a bug. I think they have a different definition of what "precision" means.

So, I wanted to trap for the instances in my calc where this issue might come up, and beat the formatting into submission for those cases. So, here is the chewy part.

You might well guess, I'm getting a little confused in doing the calculation (I wish the calcs editor had some sort of window like programmers have, that match up parntheses with different colors for you, so you can track your logical statements more easily).

I have five fields in the calc, all numbers; Amt, conc, LOD, lowsigfigs, highsigfigs.

Concentrations need to be formatted as follows (the result of the calc is text):

1. When Amt is empty, the field should show "NR".

2. When conc < LOD, the field should show "< LOD", where LOD is the contents of the LOD field, formatted to the proper number of sigfigs (lowsigfigs when LOD<10, highsigfigs otherwise), trapping for the problem in the Round function.

3. When conc > or = LOD, show "conc" where conc is the contents of the conc field, formatted to the proper number of sigfigs (lowsigfigs when conc<10, highsigfigs otherwise), trapping for the problem in the Round function.

This is what I've got so far:

Case ( IsEmpty ( Amt ); "" ;

conc < LOD; If(LOD < 10; If ( LOD < 0.9999 ; If ( Abs ( Truncate ( LOD ; 3 ) - LOD) < 0.005; GetAsText ( Round ( LOD; lowSigFig-Int ( Log ( Abs ( LOD ) ) ) )) & "0" ; GetAsText ( Round (LOD; lowSigFig-Int ( Log ( Abs ( LOD ) ) ) ))); GetAsText ( Round (LOD; lowSigFig-Int ( Log ( Abs ( LOD ) ) ) ))) ; If ( LOD < 9.5 ; If ( Abs ( Truncate ( LOD ; 2 ) - LOD) < 0.05; GetAsText ( Round (LOD; lowSigFig-1-Int ( Log ( Abs ( LOD ) ) ) ) ) & ".0" ; GetAsText ( Round (LOD; lowSigFig-1-Int ( Log ( Abs ( LOD ) ) ) ) )))); GetAsText ( Round (LOD; lowSigFig-1-Int ( Log ( Abs ( LOD ) ) ) ) ));

*If(Conc < 10; If ( Conc < 0.9999 ; If ( Abs ( Truncate ( Conc ; 3 ) - aldrin Conc) < 0.005; GetAsText ( Round ( Conc; lowSigFig-Int ( Log ( Abs ( Conc ) ) ) )) & "0" ; GetAsText ( Round (Conc; lowSigFig-Int ( Log ( Abs ( Conc ) ) ) ))); GetAsText ( Round (Conc; lowSigFig-Int ( Log ( Abs ( Conc ) ) ) ))) ;

If ( Conc < 9.5 ; If ( Abs ( Truncate ( Conc ; 2 ) - Conc) < 0.05; GetAsText ( Round (Conc; lowSigFig-1-Int ( Log ( Abs ( Conc ) ) ) ) ) & ".0" ; GetAsText ( Round (Conc; lowSigFig-1-Int ( Log ( Abs ( Conc ) ) ) ) )))); GetAsText ( Round (Conc; lowSigFig-1-Int ( Log ( Abs ( Conc ) ) ) ) ))

Now, I will have to tweak to put a "0" in front of LODs and Concs <1, to make them look pretty and to make sure everyone sees the decimal, but I'm getting an error when the calc editor parses this, at the point where I put the asterisk. It says another operator is expected. So, I think I'm not closing an If or a Case statement at the end of the LOD formatting part, but I'm a little lost. Anyone want to have some fun?

[RalphL]

Trialing zeros to the right of the decimal point do not indicate the precession of the number. Trailing zeros to the right of the decimal point are not stored.

[comment]

Ralph,

you might be as surprised as I was when I discovered that Filemaker does store superfluous trailing zeros. I don't know why, but it does, and if you format the number field to display data 'as entered' they will still be there.

Now to the point:

chemparrot,

I happen to share Filemaker's view on what "precision" means. I believe you are confusing rounding with formatting. Rounding is a mathematical operation, and Round (1.03 ; 1) SHOULD return 1.

If you want to DISPLAY the result as 1.0, then either format the field to display 1 decimal, or convert the result to TEXT and do the formatting yourself - as you have began to do. But this has nothing to do with rounding. This is a TEXT operation.

Turning to your formula: the 3 rules seem pretty clear, and they would lead me to something like:

Case (

IsEmpty ( Amt ) ;

"NR" ;

conc < LOD ;

Let ( [

LODprecision = Case ( LOD < 10 ; lowsigfigs ; highsigfigs ) ;

roundLOD = Round ( LOD ; LODprecision )

] ;

"< " & Int ( roundLOD ) & "." & Right ( 10^LODprecision & roundLOD * 10^LODprecision ; LODprecision )

) ;

conc >= LOD ;

Let ( [

concPrecision = Case ( conc < 10 ; lowsigfigs ; highsigfigs ) ;

roundConc = Round ( conc ; concPrecision )

] ;

Int ( roundConc ) & "." & Right ( 10^concPrecision & roundConc * 10^concPrecision ; concPrecision )

)

)

However, I notice that in your draft there is something called "aldrin", so perhaps there are some other rules that you haven't mentioned.

And I have just now noticed that although your first rule states

"1. When Amt is empty, the field should show "NR"."

you open with:

Case ( IsEmpty ( Amt ); "" ;

[chemparrot]

Sorry! That's what I get for copying/pasting. Aldrin is just one of the chemicals being formatted (I thought I had taken all the references out) - the field to worry about there is the field 'Conc'. As for the first one, of course that should read

Case (IsEmpty( Amt ); "NR" ;

To explain further - the calc I pasted bacially follows what you have above, with the following additional considerations:

In the case that LOD (or Conc) < 10, I have to then test if it is <0.9999, trap for when Round doesn't return what I expect

[NB: FileMaker says Round: "Returns number rounded off to the specified precision (number of decimal places)" so I stand by my assertion that either FM should say what they mean, or mean what they say.]

force the formatting when Round is going to leave off the zero, and otherwise just format it using a precision of

Round (LOD; lowSigFig-Int ( Log ( Abs ( LOD ) ) ) ).

When 0.9999 </= LOD (or Conc) < 10, it again needs to trap for when Round leaves off a zero (this only happens when the LOD or Conc are < 9.5), force the formatting when that is the case, or use a precision of

Round (LOD; lowSigFig-1-Int ( Log ( Abs ( LOD ) ) ) ).

Then, when none of these conditions are met (LOD or Conc > 10), I don't have to trap anymore, and the precision is

Round (LOD; highSigFig-1-Int ( Log ( Abs ( LOD ) ) ) ).

We found in the earlier version of this calc that the <0.9999 case had to be nested within the <10 case so that it would evaluate correctly, so I have maintained that.

I hope that helps explain what I'm trying to do...thanks so much for having a look at this. As I say, I think I am neglecting to close some logical evaluation properly for Case 2 (and it would also apply for the default case as well, since they are similar calculations). It just am having trouble figuring out where!

[comment]

I am as unable to follow your second formula as the first one.

You say it basically follows mine, but since I don't use Abs() or Log(), I very much doubt that.

In general, to format number n to p decimal places, use:

Int ( Round ( n ; p ) )

&

Case ( p > 0 ; "." )

&

Right ( 10^p & Round ( n ; p ) * 10^p ; p )

"Returns number rounded off to the specified precision (number of decimal places)".

As I said, rounding is a mathematical operation.

In mathematics, 1.03 rounded to 1 decimal point is 1.

Writing 1 as 1.0 is redundant.

That seems to be the consensus between Filemaker, Excel and myself - but we could be wrong.

[chemparrot]

Well, we use the SigFig-1-Int ( Log ( Abs ( LOD ) ) ) business to calculate "p". The Abs was put in there because sometimes the LOD would be <0 in some of our earlier data software. I don't think this is ever the case anymore, so I could simplify by leaving it out. Would you calculate the precision differently? Remember, it has to be different depending on the magnitude of the number - I am not trying to display to some precision, but to some number of significant figures:

0.348 to 2 sigfigs is 0.35 (precison is 2)

3480 to 2 sigfigs is 3500 (precision is -2)

[chemparrot]

Oh, wait - I see you are setting the precision with a Let statement. "Let" me go away and play with your calc for a little while and see what happens. I'll have to see if I still have to trap for those cases when Round was giving me fits before. Thanks for your patience.

[chemparrot]

Okay, for anyone else who is interested, the following does exactly what I want:

Case ( IsEmpty ( Amt ) ; "NR" ; Conc < LOD; Let ( [ LODprecision = Case ( LOD < 10 ; If ( LOD < 1 ; lowSigFig - Int ( Log ( LOD ) ) ; lowSigFig - 1 - Int ( Log ( LOD ) ) ) ; highSigFig - 1 - Int ( Log ( LOD ) ) ) ; roundLOD = Round ( LOD ; LODprecision ) ] ; "< " & Int ( roundLOD ) & If ( Int ( roundLOD ) >= 10 ; "" ; "." ) & Right ( 10^LODprecision & roundLOD * 10^LODprecision ; LODprecision ) ) ; Conc >= LOD ; Let ( [ concPrecision = Case ( Conc < 10 ; If ( Conc < 1 ; lowSigFig - Int ( Log ( Conc ) ) ; lowSigFig - 1 - Int ( Log ( Conc ) ) ) ; highSigFig - 1 - Int ( Log ( Conc ) ) ) ; roundConc = Round ( Conc ; concPrecision ) ] ; Int ( roundConc ) & If ( Int ( roundConc ) >= 10 ; "" ; "." ) & Right ( 10^concPrecision & roundConc * 10^concPrecision ; concPrecision ) ) )

So, I needed to add our previous calc for the precision back in, and I added a calc to leave off the decimal when the conc or LOD is >10.

This is a lot simpler than the previous formula, and more important, it looks like everything is formatted exactly the way I want it. And I don't have to trap anything else!

Thanks so much, Comment! Now, I can tell I'm going to have to fiddle with the 'Let' function some more.

[comment]

Any repeating expression is a natural candidate for Let(). I would also incorporate some carriage returns into the formula to make it more readable. IMHO, a readable formula is easier to construct - and to debug.

Would you calculate the precision differently? Remember, it has to be different depending on the magnitude of the number

So that's what this is about...

I am not sure - I would probably compute the significand itself first and round that. Sounds very much like scientific notation - see here.

[chemparrot]

And actually I found a case where the formula I posted above fails, and spectacularly so.

When the number to be formatted is between 0.995 and 1 or between 9.95 and 10, the formula you posted 5/6/05 above for what goes to the right of the decimal point

Right ( 10^concPrecision & roundConc * 10^concPrecision ; concPrecision ) or

Right ( 10^LODprecision & roundLOD * 10^LODprecision ; LODprecision ) as the case may be

returns an extra zero, and so you would get 1.00 instead of 1.0 or 10.0 instead of 10 using that calc. Since I took out the decimal point when it rounds to 10, I now get 100 returned, which is worse yet!

I'll look at the link you gave above - I obviously still have some tweaking to do on the formula if I want it exactly right.

[chemparrot]

Actually, I should be clear: The formula fails but I also have modified how the precision was calculated from what Comment posted on May 6. I should also note that in the examples I just gave where the calc fails, I am assuming two sigfigs.

I looked at the link, and no, I'm not trying to format numbers in scientifc notation. Just to a specified number of significant figures.

[comment]

Can you explain in a few simple words what the formula is supposed to achieve? Frankly, I get lost in your calculation - and since you now complain that it doesn't produce the desired result anyway, I guess it doesn't matter. So if you can say what the input might be and what the desired output should be, perhaps a way can be found.

I referred you to the ScientificNotation() function because it produces a constant number of significant digits - regardless of the input's order of magnitude. I vaguely suspect that's what you are after. In a different format, but the principle could be the same.

[chemparrot]

The basic thing I want to do is to format a number to a given number of significant figures, (including leading and trailing zeroes, where these are applicable - a zero before decimal points, and one at the end if it is needed to show the number of significant figures). So for Function(number, sigfigs), input and output would look like the following:

Function(2.4452, 2) = 2.4

Function(0.3355, 3) = 0.336

Function(0.3402, 3) = 0.340

Function(12.4455, 2) = 12

Function(1.0323, 2) = 1.0

Function(1244.55, 2) = 1200

As for the rest of it: the overall calc is for formatting chemical concentrations. When the amt field is empty, we aren't reporting that compound (so, "NR"). If the concentration of the compound is less than the limit of detection (LOD), then I want to show "< LOD" with the LOD formatted to the correct number of sigfigs. If the concentration is more than the limit of detection, then it should just show the concentration, again, to to the correct number of significant figures. It's a bit more complicated in that we might use a different number of sigfigs when the number is <10 than when it is >= 10 (lowsigfigs versus high sigfigs). I use fields to set these, because the number of sigfigs wanted changes depending on who the data are reported to.

The calc has to work over a range of concentrations and detection limits, which may be as small as 10^-8 or as large as 10^8 in general.

Is this making sense? Thanks for sticking with this!

[comment]

Try:

Let ( [

exponent = Case ( number = 0 ; 0 ; Floor ( Log ( Abs ( number ) ) ) ) ;

significand = Round ( number / 10^exponent ; sigfigs -1 ) ;

n = significand * 10^exponent ;

p = sigfigs - 1 - exponent ;

r = Round ( n ; p )

] ;

Int ( r )

&

Case ( p > 0 ; "." )

&

Right ( 10^p & r * 10^p ; p )

)

[chemparrot]

I tried this, and it is wonderful, but it still fails where the one you posted on May 6 does; when the number rounds to a multiple of 10, an extra significant digit is displayed:

Function(10.0027, 2) returns 10.0

though I want 10

I suppose I could live with these if I had to....certainly less common than where our old formula failed (every time it rounded to a whole number)...and trying to figure out how to make the other one work when it was a multiple of 10 has been driving me nuts the past two days.

[comment]

>> Function(10.0027, 2) returns 10.0

>> though I want 10

No it doesn't. It returns 10 as specified. Check your input.

[chemparrot]

Sorry about that:

Function (9.9667, 2) returns 10.0

[comment]

Ah well. You know why that is. The significand, as derived, is never quite 10, but when you round it... I don't see another way but to treat it as a special case:

Let ( [

exponent = Case ( number = 0 ; 0 ; Floor ( Log ( Abs ( number ) ) ) ) ;

significand = Round ( number / 10^exponent ; sigfigs -1 ) ;

p = sigfigs - 1 - exponent - (significand = 10) ;

r = Round ( number ; p )

] ;

Int ( r )

&

Case ( p > 0 ; "." )

&

Right ( 10^p & r * 10^p ; p )

)

[comment]

There IS another way. It may be less efficient than the workaround above (note that I have simplified it), but it follows the logic of the assignment: first ROUND the number as specified, then FORMAT the display:

Let ( [

magnitude = Case ( number = 0 ; 0 ; Floor ( Log ( Abs ( number ) ) ) ) ;

q = sigfigs - 1 - magnitude ;

r = Round ( number ; q ) ;

exponent = Case ( r = 0 ; 0 ; Floor ( Log ( Abs ( r ) ) ) ) ;

p = sigfigs - 1 - exponent

] ;

Int ( r )

&

Case ( p > 0 ; "." )

&

Right ( 10^p & r * 10^p ; p )

)

[chemparrot]

So far it looks good! I will work on it and look at my data, and let you know if I find any "gotchas". This would be a good function to submit to Brian Dunning's page, you know!

[comment]

It is only a simplification of the ScientificNotation() function. I will have to fix that one though, now that you have exposed a flaw in it. Unfortunately, the fix is not as easy as in your case.

I don't think this will affect you, but you should know that FMP will automatically switch to scientific notation at 10^9 and 10^-9. Try number = 0 and sigfigs = 10.

[chemparrot]

Well, I have been playing with this, and so far have not found any problems!

I made a custom function per your calc above - and I added the ability to set low/high sigfigs with an arbitrary cutoff. I called that from my formatting function, and in all cases of numbers, sigfigs, and cutoffs I have tested, it works just as it is supposed to!

, Comment! I never would have gotten there without your help - this is a great approach to the problem, and I know I wouldn't have thought of it. You really should go post this sigfigs function over at Brian Dunning's page so you can get credit for it; and I'm sure I'm not the only one who could use it.

[chemparrot]

It is only a simplification of the ScientificNotation() function. I will have to fix that one though, now that you have exposed a flaw in it. Unfortunately, the fix is not as easy as in your case.

I don't think this will affect you, but you should know that FMP will automatically switch to scientific notation at 10^9 and 10^-9. Try number = 0 and sigfigs = 10.

I didn't see this until after I posted my thanks - you are saying this function will fail for numbers outside this range of exponents? I don't think I've ever seen numbers > 10^6 or 10^7 in magnitude or < 10^(-6) or 10^(-7) in our data, so you're right, it likely won't affect me - our old formula should have failed too in that case.