Issues with inaccuracies in my math

[Breakneckbrands]

Howdy yall, 

I am trying to do a simple math setup for a client. calls per hour, demos per hour, calls per demo... 

The issue I am running into is the math seems to be coming back wrong. I have tried a few different ways and still having issues. 

Here you can see the number not adding up. In this case, we did 30 demos and had 19 families sign up. According to my calculator, I get 1.58 demos per family (30/19). However, I get 1.82 in the formula field. I have eliminated the infinity/NaN issues with the code in the pic that I found and edited. 

I will add that the rows the data is being pulled from are just normal number rows. I have tried different decimal sizes to see if it was a rounding issue, but it wasn't. 

Any advice or ideas would be awesome. 

[pressGO_design]

From your screenshot it looks like you're looking at the summary part of a grouped view and not the number in the field cell itself.

[Breakneckbrands]

Correct, that was just for the efficiency/privacy sake of the screen cap. However, the end result is still the same number for the field av. on the interface. 

I have tried a few different iterations and the end result is Nan, Infinity, or Error

[pressGO_design]

I'm making a bunch of assumptions, one or more of which might be wrong. But it's hard to tell from your screenshots what the context is. So here's what I'm imagining:

1. You're trying to come up with a way to display metrics for a group of employees.
2. You have one or more tables that roll up data to each employee's record. You're getting error messages because you're trying to manipulate fields that have zeroes in them (like a day where zero families signed up). See attached screenshots.
3. You want to create a dashboard where people can select a single employee and view their metrics. So they select John and see his demos/family number and then select Jane and see hers, etc etc etc.

If that's the case, you might have a problem in one of the formulas that is rolling up the numbers to the employees' records. If that's not the case, it would be great if you could provide any additional context that would help diagnose your problem, along with a screenshot that shows data in the record and the formula you're using to get the result.

Thanks.

[Breakneckbrands]

Thank you for the context prompt, various insurance agents input their data via a form. Everyone is grouped via their user. The data in question is just regular number field columns with no formulas. The formula columns are in the same table where I have number column/ number column EX:  {total hours}/{total calls}. However, the numbers are returning strangely. 

For example, here I have the Calls per demo 86 calls total / 30 total demos = 2.86666 calls per demo however the cell and interface are showing 3.1. 

[pressGO_design]

Aha! Got it. “Everyone is grouped via their user.” At the top of each group, you have summary fields. The summary fields for demos, calls, and families are SUM fields: they add up all the numbers in the group and - voila! - there’s your total. But your calls / demo summary field is an AVG field: it’s taking the average of all of the results in your formula field. It doesn’t do the formula itself. It’s not supposed to. The Number field in your interface is showing the same number for the same reason.

Those summary fields that show up at the top of each group aren’t actionable data fields in the table. They’re there as a convenience to the viewer. In your case, they’re not convenient at all, because they’re not showing what you want them to show. But they are showing exactly what you’ve set them up to show. 

In order for what you want to have happen to actually happen you need to have a Users table that links the to table with the form inputs. Then you use roll-ups to sum all the numbers (the calls, the demos, the families, the hours, etc), and you do your formula fields (calls per demo, demos per family, hours per calls, etc) in the Users table. This is what I tried to show you in my screenshots.

Then you set up your interface off of that table. Each number widget in the interface can pull the formula result for each of the users. If your interface users need to see the granular data, you can present that data in a grid in your interface. 

You don’t have a formula problem. You have a data structure problem. Which is also eminently solvable and requires reconfiguring how your base is set up. 

[Breakneckbrands]

Thank you so much for your write-up. I am still hitting the same wall. 
I made a simplified base with the same structure 

Link to Base

[pressGO_design · Accepted solution]

I added 2 rollup fields and a formula field to your users table (Table 2) and then used that table to create an interface where people can pull the record of the user and see that users Total Calls, Total Hours, and the number of calls/hour.

[Breakneckbrands]

Thank you so much. That was it!

[pressGO_design]

You're welcome! I had some fun playing with your interface. 🙂