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

dpaton05 · Oct 17, 2019

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 · Oct 20, 2019

I suggest that you share a sample workbook, so that we can work on exactely your situation...

Bye

dpaton05 · Oct 20, 2019

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 · Oct 20, 2019

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 · Oct 21, 2019

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

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

dpaton05

Well-known Member

Excel Facts

Anthony47

Well-known Member

dpaton05

Well-known Member

Michael M

Well-known Member

Anthony47

Well-known Member

Similar threads

Forum statistics

Share this page

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

dpaton05

Well-known Member

Excel Facts

Anthony47

Well-known Member

dpaton05

Well-known Member

Michael M

Well-known Member

Anthony47

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock