# Discussions

View Only

## How do I calculate cost per development hour from an average?

• #### 1.  How do I calculate cost per development hour from an average?

Top
Contributor
Posted 02-19-2018 15:41
I want to automate the process of calculating the cost of development, based on an entry field (development hours) - so when a developer enters their project hours, it automatically calculates the cost based on our team average.

Any thoughts/assistance would be greatly appreciated.

• #### 2.  RE: How do I calculate cost per development hour from an average?

Top
Contributor
Posted 02-19-2018 15:53
Can you tell us more about your tables and relationships and also give a verbal description with example numbers of the calculation?

• #### 3.  RE: How do I calculate cost per development hour from an average?

Top
Contributor
Posted 02-19-2018 16:33
I have my main table (Project), which is the app that contains the general project information (Name, type, creation date, owner, etc).  Within this is a relationship to another table, that allows the developers to 'code' their time to the project (Dev_Hours).  The sum of the entries are then displayed in the Project table as Total Design Hours.

So next, I would like to take the Dev_Hours sum and, in a new field, output a cost.

For example let's say it costs an average of \$65 dollars per hour to develop and there are 12 Dev_Hours input, I would like to create an additional field (next to Total Design Hours) in the Project that displays the cost.

Hope that helps - and this may be a very simple formula to do, but I am struggling with this one...

• #### 4.  RE: How do I calculate cost per development hour from an average?

Top
Contributor
Posted 02-19-2018 16:35
The formula to calculate cost would be

[Total Design Hours] * 65

• #### 5.  RE: How do I calculate cost per development hour from an average?

Top
Contributor
Posted 02-19-2018 16:56
That's just it - it doesn't show a total.  I set the field up as a Numeric (formula) and nothing happens.  If I switch it over to Duration, it will calculate it out as Days just fine. Is there a specific type of field I need to set it up as, other than Formula - Numeric?

• #### 6.  RE: How do I calculate cost per development hour from an average?

Top
Contributor
Posted 02-19-2018 17:07
OK, so if this is a duration field type for

[Total Design Hours]

Then you will need to convert that to be a numeric field type before multiplying

ToHours([Total Design Hours]) * 65

That is because when you have a duration field, even though it happens to be named "hours", it is a a duration which could also be expressed in seconds, minutes, hours, days, weeks ... so you need to get it converted ot the number of hours if you are going to multiply by an hourly rate.

• #### 7.  RE: How do I calculate cost per development hour from an average?

Top
Contributor
Posted 03-06-2018 15:19
Apologies for the late follow-up - travel/schedules bogged me down for a bit. So here's what I wound up with:

If([Designer]= "Name",
[Hours Worked] * 40.9,

I adjust the hours-worked formula based on cost relative to designer.  Where I was getting hung up is the closing parenthesis (I needed 14 and only had 1) - once I figured that out, the formula worked.  So now, when I select a designer from my pull-down, it automatically calculates based on the hours worked and auto-populates the Cost field.

• #### 8.  RE: How do I calculate cost per development hour from an average?

Top
Contributor
Posted 03-06-2018 15:33
So for anyone that would like to use this (or try it out) here's the steps.  To start, create the following three fields:

Designer (or any other name that you need) Type: Text Multiple Choice
Hours Worked (or any other name that you need) Type: Numeric Field
Cost (or any other name you need) Type: Numeric/Currency)

Input the names in your Designer list; in the Cost field properties, locate the formula field and enter the following information:

If([Designer]= "Name",
[Hours Worked] * 40.9,
If([Designer]= "Name",
[Hours Worked] * 38.6,
If([Designer]= "Name",
[Hours Worked] * 34)))

Enter as many formulas as you need.  To close out your formula, use the same number of parenthesis as your If statements.

• #### 9.  RE: How do I calculate cost per development hour from an average?

Top
Contributor
Posted 03-06-2018 15:45
Ron,
Good to see that you made it work.

Here are two suggestions for the future

You only need 1 IF in QuickBase, so you need to undo your excel background.

If(
[Designer]= "Name", [Hours Worked] * 40.9,
[Designer]= "Name", [Hours Worked] * 38.6,
[Designer]= "Name", [Hours Worked] * 34)

But the right way to do this is not to hard code it.

There should be a table of designers and their rates.  Then setup a relationship where 1 designer has many rates and pull that down as a lookup field for the Rate.

Then I would set up a snapshot field on that rate lookup https://help.quickbase.com/user-assistance/setting_up_snapshot_fields.html so that you do not change the historical records, when the rates are changed over time.

• #### 10.  RE: How do I calculate cost per development hour from an average?

Top
Contributor
Posted 03-06-2018 16:41
Thanks for the additional info (for guys like me, this is the stuff that helps tremendously!).  The single If statement does make it so much easier!

The additional ideas on the relationship table is brilliant, as is the snapshot.  Going to give those a try so you may be seeing me back here asking questions when it doesn't work :)

• #### 11.  RE: How do I calculate cost per development hour from an average?

Top
Contributor
Posted 03-06-2018 16:45
Correction to my post above.

There should be a table of Designer with their Rates.

Then set up a relationship where 1 Designer has Many Orders (or whatever you call your table that now has the formula)