# Excel formula to add a minimum amount and add an extra hour

#### dpaton05

##### Well-known Member
I have a spreadsheet with data entered into cells A5:E11 in a matrix. This is a named range with the name Service_Types. Along the top, starting at B5 I have Mon-Fri, Sat, Sun and Public Holiday. In column A, starting at A6, I have a list of activities. The cells contain hourly cost of certain days of the different activities. These are all cells, not a table.

I do however have another sheet that has a table that correctly references the activity in the row and returns the corresponding rate, based on the date of the activity. The formula for this cell is
Code:
``=INDEX(Service_Types,MATCH([Service],Sheet2!\$A\$5:\$A\$11,0),MATCH([Day rate],Sheet2!\$A\$5:\$E\$5,0))``
The Day rate column for each row contains Mon-Fri, Sat, Sun and Public Holiday.

The list of activities is Activity 1, Activity 2, Activity 3, Activity 4, Activity 5, Activity 6, Activity 7.

I was just multiplying the hourly cost by hours performed but I have run into some problems. Activity 2 needs to have an additional hour added to account for report writing and activity 3 and 4 have a minimum of 3 hours in the cost of the activity.

Can someone help me with the formula so it can do the extra bits of the minimum hours for the activities3 and 4 and the extra hour for that activity 2 please?

#### Anthony47

##### Well-known Member
I suggest that you share a sample workbook, so that we can work on exactely your situation...

Bye

#### dpaton05

##### Well-known Member
I can't share the workbook, sorry, there is too much confidential info in it. I have a cell for the hours done and a cell that allows you to select the type of activity. In the background, it does a index and match on the selected activity from B1 to find the rate per hour. That is multiplied by the hours done to get a figure. For one of the activities, there is a minimum of 3 hours when calculating the cost.

If the rate is in A1, the hours done in A2, the type of activity in B1, the total in A3 would normally be A1*A2, but I don't know how to write it if there is a minimum of 3 hours. Can someone help me please?

#### Michael M

##### Well-known Member
If the rate is in A1, the hours done in A2, the type of activity in B1, the total in A3 would normally be A1*A2, but I don't know how to write it if there is a minimum of 3 hours. Can someone help me please?
Maybe
Code:
``=IF(A2<=3,3,A1*A2)``

#### Anthony47

##### Well-known Member
Don't miss Michael message, above…

I suggested you to share a "sample" workbook, not your real workbook; I mean you spend some time to create a sample that reflect your original. The other way is that we spend our time to understand your workbook, recreate a sample and finally search for a formula…

In general, your formula should be something like this:
Code:
``=CalculatedDuration+If(Or(Activity_type="Activity3", Activity_type="Activity4"), Max(0,3-CalculatedDuration), If(Activity_type="Activity2",1,0))``
If this doesn't allow you to create the final formula, then try to give us a sample to work on.

Bye

Last edited: