Converting Excel Calculation to Filemaker - Stuck

[vuego100]

Hi,

I'm pretty new to filemaker and the forums so first I'd like to say hi!

Wow what a program filemaker is, I didn't know this was out there until recently, I think it's going to revolutionize my way of working and I've already made great headway on my first project that orginally resided in a excel spreadsheet, but now I'm really stuck. I thought this would be straight forward but I'm struggling to get my head around it. I'm trying to enter two values (number of visits and age) and a corresponding result be picked from an array/table it was pretty easy in excel but it's bent my brain back on itself trying to work this one out lol any advice or pointing in the right direction would be a great help. Attached pic of how it's set up in excel.

 

,

I tried using Getnthrecord (number of visits) in a calculation which kind of works for to get a value of numer of visits but I'm struggling with linking this to only choose values within repective age ranges etc.

 

 

 

Edited June 5, 2015 by vuego100
pic throwing text out

[comment]

Filemaker is not a spreadsheet, and things don't map 1:1 between the two. For example, the data in the "array" that you show us would be best arranged in a database table as follows:

FromAge   NumberOfVisits      Percentage
16        1                   .05
25        1                   .04
34        1                   .06
40        1                   .04
46        1                   .03
51        1                   .05
16        2                   .06
25        2                   .05
...
46        8                   .1
51        8                   .12

That is, an individual record for each summary value. This is assuming that the summary values are the 'raw" values in your database - not derived from another table that records the actual visits.

With this in place, you can lookup the value of interest using a relationship based on matching ≥FromAge and =NumberOfVisits. Or you could just perform a find for the record of interest.

 

[vuego100]

Thank you for your help. took me a while but ended up using a relationship to show the matching value, works a treat.

Thanks again.