5 Replies Latest reply on Jan 27, 2010 9:02 AM by slaglemark

# help with calculation field: if value is between x and y...

### Title

help with calculation field: if value is between x and y...

### Post

im trying to create a field that tells me: how many values in field X are between "value" and "value"

like if value is between 0 and 10... then i want the total number of instances.

my thinking is that i need two fields

1. calculation field If ( %score= "is between 0 and 10" ;1;0)

2. summary field total of above field 1.

but i cannot figure out the range part.

is there a better way?

fmpa 9, mac osx 10.5.8

thx!

-m

• ###### 1. Re: help with calculation field: if value is between x and y...

Try:

If ( %score ≥ Value_1 and %score ≤ Value_2 ; 1; 0)

Also consider using the function Sum ( Your_table::your_calculated_field ) instead of Summary

• ###### 2. Re: help with calculation field: if value is between x and y...

i thought i had it right before i saw you reply with:

If ( %score ≥  "0"  and  %score ≤ "40" ;1;0)

then you confirmed!

now guess i still need an additional summary field.

then i have to repeat for 10 more ranges.  yay.

thx very much!

-m

• ###### 3. Re: help with calculation field: if value is between x and y...

would you mind explaining further about the Sum function and why i should use it?

• ###### 4. Re: help with calculation field: if value is between x and y...

Summary is good for reports where you want to break-out totals by sub-header, etc. but I noticed in some instances that it doesn't always update the total displayed on layouts immediately whereas the function Sum does.

• ###### 5. Re: help with calculation field: if value is between x and y...

i see.

i think in this case i need to use the summary field rather than the Sum total.

the most frustrating part of this process is that i distribute data using IWP, so i think im often learning improperly.  or at least creating less than perfect work-arounds.