Skip to main content

Need help creating a formula

  • March 17, 2023
  • 1 reply
  • 0 views
C
Forum|alt.badge.img+1
Hello All,
I am trying to figure out how to perform a calculation if possible.
I have jobs that are sorted by date. Currently, data is grouped by date. I want to take the flat rate for example $500 and divide that by the number of jobs. So that I can have a column that tells me how much each job was worth at the flat rate. So if there were 14 jobs and the flat rate is $500 each job for that day will get its portion of the 500. The number of jobs per day can vary. For example, on some days, I will only have 6 jobs but the same flat rate. In addition, I would like to know how many jobs are urban and how many long distances. I already have the data using a lookup by zip code. Just need to know the percentage of revenue that is urban and long-distance. Hope this makes sense. Been trying different formulas but cannot figure how how to get the count working. I appreciate any help or advise you may have. Thank you so much
1 Attachments

1 reply

TheTimeSavingCo
Forum|alt.badge.img+28

I think you're going to need to create another table that acts as a rollup to get this to work

Try this:
1. Create a new table called "Summary" or some such
2. In your original table, create a new linked field to the "Summary" table
3. Click the field header of your "Date" field.  This should select all the values in this column
4. Click the field header of the newly created linked field from step 2
5. Paste the values in
6. In the "Summary" table, a whole bunch of new records should have been created, with each record representing a single date
7. Create a "Count" field to get the number of jobs per date
8. Create a "Currency" field to input the flat rate for that day, e.g. 500
9. Create a "Formula" field to divide the rate by the number of jobs

You should be able to apply the same logic for the urban and long distance problem

Adam, Consultant @ thetimesaving.company

Reply


Most helpful members this week

Terms & Conditions