Trying to create calculation based on particular criteria

Ok forum I need your help again!!!

I have 2 worksheets with data. One of them is a bench mark template or expected times to complete devices based on batch size and product. The other worksheet is a report generated that shows the actual time techs worked on batches of equipment by product type. I am trying to automate a report that I will need to run every week. I have most of it automated but need some help with some calculations. I want to calculated the expected time to complete the work, based on the data provided in the benchmark worksheet. I do not want to use an average, and I want to use the expected time of completion based on batch size and product relative to the tech report. Column T is highlighted and where I want this formula to be entered. So if the Total Batch items is 1, then the expected calculation uses that time relative to the product and then multiplies that number to the batch size. Or if the batch size is 3, then it uses the time relative to a batch size of 3 and then multiplies that number by the batch size on the tech report and is entered in the highlighted column.

I have attached the workbook I am working with.

Look forward to any of the input and assistance I can get!!!!!!

Thanks!!!!!!!

Pierre-
There are only 4 columns I need to worry about on the bench mark worksheet. That is if the batch size is 1, 2, 3, or 4 and larger. So if the batch size is greater or equal to 4 then, the time in that column would be used. So would the formula you provided work in that manor?

I guess my question is...will this formula calculate the time if the batch size is 1 unit with the batch time it is expected for 1 unit, or if the batch size is 3, then it uses the time expected for a batch size of 3?

Nevermind...I experimented with the formula and it works perfectly thanks!!!!!

Actually...one quick question...if I want to incorporate this into my macro...do i just do this...

Please Login or Register  to view this content.

Thanks!

Also...I was playing around with the batch Size and I think I understand the question before...The formula works great as long as the batch size is 4 or less. When the batch size is over 4 it calculates the expected time from the next column. How can I change...

Formula:

Please Login or Register  to view this content.

To use the column for a batch size of 4 and larger on the bench mark worksheet, for all batch sizes 4 and over?