Log In
Register
Home
December 16, 2021
Offline
Hi there,
I am still working on a workbook to calculate test cost at different times, something that is very often used in the pharmaceutical industry.
I copied a dummy example using XL2bb (below) however I try to explaijn what I want.
I have a column with tests, their difficulty, what department is performing this test, a surcharge, the hourly rate for the department, then setup hours and replicate hours.
For the # of setups column I have a formula that calculates the # of setups needed based on the product of columns J --> M.
The calculation works beautifully for T0 (column N) but I need to manually adjust it from T1 (column O) and beyond.
The number of samples for T1 and beyond comes from a separate table using a countA function, I have just created a dummy table below in row 11/12 indicating number of samples at each Tx.
My problem is that I need to adjust the number of setups for T1 and beyond manually instead of having it calculate based on the product of columns J -->M times x (value in row 12 for each time)
Is there a way to do that or am I stuck with manual adjustments? Dummy example workbook attached
any help for this novice that is trying to make a big contribution and learn new stuff is greatly appreciated
See Answer
Moderators
January 31, 2022
Online
Change the formula in N3 to:
=ROUND(PRODUCT($F3,SUM(PRODUCT($G3,INDEX(samples,1,MATCH(N$2,t_headers,0))),PRODUCT($H3,$J3:$M3))),0)
Copy down and across, and I believe it shall works as desired. The "#of setups" column in the upper table is then no longer needed, as the relevant information for each "T" will be picked-up from the table in rows 11 and 12.
Note: I have used two named ranges "samples" and "t_headers" that refer to the information in columns C:K on rows 11 and 12.
File attached.
Answers Post
December 16, 2021
Offline
Thank you very much indeed for your answer, and yes, this is what I was looking to do.
at least I thought however upon looking at it more closely it calculates things correctly but does not include that fact that for every 10 samples the number of setups (# of setups, column I) is not factored in.
In T1 I have a total of 15 samples for test1, (3 samples x 5 replicates = 15) and I need to reset every 10 samples, i.e. 15 samples = 2 resets
1 Guest(s)
