Nov 11 2020 10:42 AM
Nov 11 2020 10:42 AM
Please see the attached file. I am trying to calculate the costs in terms of categories (services) from drop down lists. Sheet 3 is the final sheet to show the calculated results based on the data from sheet 1 and 2. Is there any way to combine the results of different categories in the final sheet?
Thank you very much for taking the time.
Nov 11 2020 11:12 AMSolution
@MTooba in the attached I did what I think you want in 2 different ways:
a) pivot table - this is powerful and flexible solution
if the array formula above doesn't work then replace C6:C10 with just C6 and copy it down the column
Nov 11 2020 11:57 AM
Dec 19 2020 01:07 AM
Hi, its me again. Hope you are doing well.
Please check the attached file. I need to fix the price of one task from a list for all the staff members this time. Previously, the total was calculated according to staff's rate per hour, but now I need to include one task at fixed price for all the staff members.
Can you please have a look if it can be done?
Thank you very much for your time.
Dec 19 2020 06:04 AM
@MTooba I'm sure it is possible if enough information is known. Right now you have a table with person and rate, and a table with person, hours, tasks and $$ based on previous table. You want to fix the rate for a given task but you don't have a table that defines rate for a given task. You also want person to get their rate for 'other' tasks but don't define what that is. For example this excerpt from the table:
each of these staff do Janitorial and I think you want to 'fix' the rate but at what amount? Each of them have different rates.
Dec 19 2020 07:14 AM
@mtarler Thank you for getting back to me.
You are right. That is exactly the issue. Tasks do not have rates. All the payments are made on the basis of employee hourly rate regardless of what they do.
Now, admin has decided that one of the tasks will be billed on the basis of task's price and not according to staff's hourly rate.
Let's continue with the example of "Janitorial" from the excerpt. I want to assign a value "300" to janitorial tasks, no matter who is performing. In this case hourly rates of staff will not apply. (So this particular task will be an exception).
For other tasks, there are no rates, but the calculations are made on the basis of hourly rate of the person performing the task.
Kindly let me know if I have explained it properly.
Dec 19 2020 01:34 PM
@MTooba so yes it is possible but is that the only task with a fixed rate or just 1 example? The basic concept I see is: lookup(based on task) but if error then lookup(based on staff rate)
So you need a table of tasks and corresponding rates then if the lookup fails to find the task then it will perform the lookup we did for the staff.
Dec 19 2020 01:53 PM
@mtarler yes. For now, it is only one task "transport" with fixed rate.
So, let's say I add a table with all the tasks by adding the rate of other tasks as "0", and use Vlookup twice, it should help. I am understanding that I need to change the formula that was previously written.
Is that right?
Dec 19 2020 05:25 PM
@MTooba Here is the formula:
I also added the "AltTotal" column that uses the newer XLOOKUP() function:
=IF(E4="","",XLOOKUP(F4,Table3[Tasks],Table3[per hr rate],XLOOKUP(E4,Table1[Name],Table1[Per hour rate],0)*G4))
Dec 20 2020 08:58 AM
Thank you very much. I'll get back to you if I get stuck with something. Hope that is fine.