getting excel to calculate correctly

[pyclen]

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?

any help for this novice that is trying to make a big contribution and learn new stuff is geratly appreciated

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
2		Test	Difficulty	Group	Surcharge	hourly rate	setup hours	replicate hours	#of setups	Replicates	# of lots/strengths	Orientations	Packaging Configurations	T0	T1	t2	T3	T4	T5	t6	t7	t8									setup hours		replicate hours
3		test 1	avg	grp1	10%	$110	1	0.5	1	5	1	1	1	$385										surcharge		Group	hr rate			Test	avg	hi	avg	hi
4		test 2	hi	grp1	0%	$100	2	1.5	1	3	1	1	1	$650										0		grp1	100			test 1	1	1.5	0.5	1
5		test 3	avg	grp1	20%	$120	3	1	1	2	1	1	1	$600										0.1		grp2	150			test 2	1	2	1	1.5
6		test 4	avg	grp2	10%	$165	3	1	1	3	1	1	1	$990										0.2		grp3	175			test 3	3	4	1	1.5
7		test 5	hi	grp1	0%	$100	0.75	0.75	1	5	1	1	1	$450										0.3						test 4	3	5	1	2
8																								0.4						test 5	0.5	0.75	0.5	0.75
9															at t1 I need to calculate the number of total samples to analyze and then adjust the number of setups based on that total.
10															that goes for every time after t1
11		stability	T0	T1	t2	T3	T4	T5	t6	t7	t8				t0 I can calculate easily
12		# of samples	1	3	3	3	2	2	2	1	1
13
14
Sheet1

Cell Formulas
Range	Formula
F3:F7	F3	=ROUND(IF(COUNTA($D3:$E3)<2,"",VLOOKUP($D3,group,2,0)*(1+$E3)),0)
G3:G7	G3	=IF($B3="","",INDEX(setup,MATCH($B3,test,0),MATCH($C3,difficulty,0)))
H3:H7	H3	=IF($B3="","",INDEX(replicate,MATCH($B3,test,0),MATCH($C3,difficulty,0)))
I3:I7	I3	=ROUNDUP(PRODUCT($J3:$M3)/10,0)
N3:N7	N3	=ROUND(PRODUCT($F3,SUM(PRODUCT($G3,$I3),PRODUCT($H3,$J3:$M3))),0)

Named Ranges
Name	Refers To	Cells
difficulty	=Sheet1!$AE$3:$AF$3	G3:H7
group	=Sheet1!$Z$4:$AA$6	F3:F7
rate	=Sheet1!$AA$4:$AA$6	F3:F7
replicate	=Sheet1!$AG$4:$AH$8	H3:H7
setup	=Sheet1!$AE$4:$AF$8	G3:G7
test	=Sheet1!$AD$4:$AD$8	G3:H7

Cells with Data Validation
Cell	Allow	Criteria
C3:C7	List	=difficulty
D3:D7	List	=group
E3:E7	List	=surcharge

 

[jtakw]

Hi,

Can you explain exactly what needs to be adjusted from the formula in N column to O column and on?
Also, please explain the logic of your calculation in your N column formula.

 

[pyclen]

Hi there,
Column N is my base, there's nothing to change.

The next column (O) needs to take into account the change in the total number of samples being analyzed based on the product of D12 and J --> M.
In other words, the value in I3 needs to be dynamic for each time point, ie it needs to be calculated for each time.
If the formula can be adjusted for the initial time as well that is an option as well.

 

[jtakw]

I'm afraid I'm not following, again, please explain the logic for the required calculation, step by step, for column O, then, what changes for column P ?
Where is this "time" you speak of?

Don't forget, you know everything about your spreadsheet, potential helpers are Only relying on the information You provide in order to try to help.

 

[pyclen]

my apologies for being not as clear as possible.

I am trying to calculate the cost at various intervals (timepoints (T0, T1, T2, T3 etc (found in Column N2 --> U2, and C11 --> L11) for samples being tested.
I made an example sheet using test 1, 2, 3, etc. as example.
Each test has a setup time and replicate time (i.e. how often the test is performed (column J).
How often I need to perform a setup (akin to a reset of an instrument) is dependent on the total number of samples I need to analyze.
At my initial timepoint T0 the number of samples is C12*J3*K3*L3*M3 (1*1*5*1*1 = 5), and since I need to perform a reset after every 10 samples I just need to do it once.
So, the cost at T0 is F3*((g3*I3)+(h3*C12*J3*K3*L3*M3) = f3*(1+2.5) = 110 * 3.5 = 385

For T1 and subsequent times (T2/3/4 etc) that formula needs to be dynamic since D12, E12, F12 etc. may not be 1.
For T1 the value is D12*J3*K3*L3*M3 (3*1*5*1*1 = 15), therefore I need to perform 2 resets since 15>10 however the above formula that I used for T0 needs to be manually adjusted to account for the second reset.
F3*((g3*I3)+(h3*D12*J3*K3*L3*M3) = f3*(2+(0.5*15) = f3*(2+7.5) = 110 * 9.5 = 1045.

The same goes for subsequent times (T2/3/4...), how can I account for the changing value in D12/E12/F12 and resets I need to perform, i.e. make I3 dynamic

 

[bebo021999]

Not 100% understanding, but below formula returns what you expected for T0 & T1:

sumeven.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1
2		Test	Difficulty	Group	Surcharge	hourly rate	setup hours	replicate hours	#of setups	Replicates	# of lots/strengths	Orientations	Packaging Configurations	T0	T1	t2	T3	T4	T5	t6	t7	t8									setup hours		replicate hours
3		test 1	avg	grp1	0.1	110	1	0.5	1	5	1	1	1	385	1045	1045	1045	660	660	660	385	385		surcharge		Group	hr rate			Test	avg	hi	avg	hi
4		test 2	hi	grp1	0	100	2	1.5	1	3	1	1	1	650	1550	1550	1550	1100	1100	1100	650	650		0		grp1	100			test 1	1	1.5	0.5	1
5		test 3	avg	grp1	0.2	120	3	1	1	2	1	1	1	600	1080	1080	1080	840	840	840	600	600		0.1		grp2	150			test 2	1	2	1	1.5
6		test 4	avg	grp2	0.1	165	3	1	1	3	1	1	1	990	1980	1980	1980	1485	1485	1485	990	990		0.2		grp3	175			test 3	3	4	1	1.5
7		test 5	hi	grp1	0	100	0.75	0.75	1	5	1	1	1	450	1275	1275	1275	825	825	825	450	450		0.3						test 4	3	5	1	2
8																								0.4						test 5	0.5	0.75	0.5	0.75
9
10
11		stability	T0	T1	t2	T3	T4	T5	t6	t7	t8
12		# of samples	1	3	3	3	2	2	2	1	1
Sheet3

Cell Formulas
Range	Formula
F3:F7	F3	=ROUND(IF(COUNTA($D3:$E3)<2,"",VLOOKUP($D3,group,2,0)*(1+$E3)),0)
G3:G7	G3	=IF($B3="","",INDEX(setup,MATCH($B3,test,0),MATCH($C3,difficulty,0)))
H3:H7	H3	=IF($B3="","",INDEX(replicate,MATCH($B3,test,0),MATCH($C3,difficulty,0)))
I3:I7	I3	=ROUNDUP(PRODUCT($J3:$M3)/10,0)
N3:V7	N3	=$F3*($G3*ROUNDUP(HLOOKUP(N$2,$C$11:$K$12,2,0)*$J3*$K3*$L3*$M3/10,0)+$H3*HLOOKUP(N$2,$C$11:$K$12,2,0)*$J3*$K3*$L3*$M3)

Named Ranges
Name	Refers To	Cells
difficulty	=Sheet3!$AE$3:$AF$3	G3:H7
group	=Sheet3!$Z$4:$AA$6	F3:F7
rate	=Sheet3!$AA$4:$AA$6	F3:F7
replicate	=Sheet3!$AG$4:$AH$8	H3:H7
setup	=Sheet3!$AE$4:$AF$8	G3:G7
test	=Sheet3!$AD$4:$AD$8	G3:H7

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

https://www.myonlinetraininghub.com/excel-forum/excel/getting-excel-to-calculate-correctly

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[pyclen]

Not 100% understanding, but below formula returns what you expected for T0 & T1:

sumeven.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1
2		Test	Difficulty	Group	Surcharge	hourly rate	setup hours	replicate hours	#of setups	Replicates	# of lots/strengths	Orientations	Packaging Configurations	T0	T1	t2	T3	T4	T5	t6	t7	t8									setup hours		replicate hours
3		test 1	avg	grp1	0.1	110	1	0.5	1	5	1	1	1	385	1045	1045	1045	660	660	660	385	385		surcharge		Group	hr rate			Test	avg	hi	avg	hi
4		test 2	hi	grp1	0	100	2	1.5	1	3	1	1	1	650	1550	1550	1550	1100	1100	1100	650	650		0		grp1	100			test 1	1	1.5	0.5	1
5		test 3	avg	grp1	0.2	120	3	1	1	2	1	1	1	600	1080	1080	1080	840	840	840	600	600		0.1		grp2	150			test 2	1	2	1	1.5
6		test 4	avg	grp2	0.1	165	3	1	1	3	1	1	1	990	1980	1980	1980	1485	1485	1485	990	990		0.2		grp3	175			test 3	3	4	1	1.5
7		test 5	hi	grp1	0	100	0.75	0.75	1	5	1	1	1	450	1275	1275	1275	825	825	825	450	450		0.3						test 4	3	5	1	2
8																								0.4						test 5	0.5	0.75	0.5	0.75
9
10
11		stability	T0	T1	t2	T3	T4	T5	t6	t7	t8
12		# of samples	1	3	3	3	2	2	2	1	1
Sheet3

Cell Formulas
Range	Formula
F3:F7	F3	=ROUND(IF(COUNTA($D3:$E3)<2,"",VLOOKUP($D3,group,2,0)*(1+$E3)),0)
G3:G7	G3	=IF($B3="","",INDEX(setup,MATCH($B3,test,0),MATCH($C3,difficulty,0)))
H3:H7	H3	=IF($B3="","",INDEX(replicate,MATCH($B3,test,0),MATCH($C3,difficulty,0)))
I3:I7	I3	=ROUNDUP(PRODUCT($J3:$M3)/10,0)
N3:V7	N3	=$F3*($G3*ROUNDUP(HLOOKUP(N$2,$C$11:$K$12,2,0)*$J3*$K3*$L3*$M3/10,0)+$H3*HLOOKUP(N$2,$C$11:$K$12,2,0)*$J3*$K3*$L3*$M3)

Named Ranges
Name	Refers To	Cells
difficulty	=Sheet3!$AE$3:$AF$3	G3:H7
group	=Sheet3!$Z$4:$AA$6	F3:F7
rate	=Sheet3!$AA$4:$AA$6	F3:F7
replicate	=Sheet3!$AG$4:$AH$8	H3:H7
setup	=Sheet3!$AE$4:$AF$8	G3:G7
test	=Sheet3!$AD$4:$AD$8	G3:H7

THank you very much for your help, and I believe that is what I am trying to do

 

[pyclen]

Ok, I am grateful for the help received so far however the above solution only works on a single row (here row 12), i.e the calculation is correct in cell N4:N8, nowhere else as the test is different as well as the number of replicates.
The formulas in N4:N8 need to be modified so that the referenced test is correct as well as the timepoint in C8:K8

Again, any help is appreciated,
Thank you

Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
2																																	setup hours		replicate hours
3		Test	Difficulty	Group	Surcharge	hourly rate	setup hours	replicate hours	#of setups	Replicates	# of lots/strengths	Orientations	Packaging Configurations	T0	T1	t2	T3	T4	T5	t6	t7	t8				surcharge		Group	hr rate			Test	avg	hi	avg	hi
4		test 1	avg	grp1	0%	100	1	1	1	1	1	1	1	$200	$300	$400	$500	$600	$700	$800	$900	$1,000				0		grp1	100			test 1	1	1	1	1
5		test 2	avg	grp1	0%	100	2	2	1	1	1	1	1	$400	$600	$800	$1,000	$1,200	$1,400	$1,600	$1,800	$2,000				0.1		grp2	150			test 2	2	2	2	2
6		test 3	avg	grp1	0%	100	3	3	1	1	1	1	1	$600	$900	$1,200	$1,500	$1,800	$2,100	$2,400	$2,700	$3,000				0.2		grp3	175			test 3	3	3	3	3
7		test 4	avg	grp1	0%	100	4	4	1	1	1	1	1	$800	$1,200	$1,600	$2,000	$2,400	$2,800	$3,200	$3,600	$4,000				0.3						test 4	4	4	4	4
8		test 5	avg	grp1	0%	100	5	5	1	1	1	1	1	$1,000	$1,500	$2,000	$2,500	$3,000	$3,500	$4,000	$4,500	$5,000				0.4						test 5	5	5	5	5
9
10
11		stability timepoints	T0	T1	t2	T3	T4	T5	t6	t7	t8
12		test 1	1	2	3	4	5	6	7	8	9
13		test 2	1	2	3	4	5	6	7	8	9
14		test 3	1	2	3	4	5	6	7	8	9
15		test 4	1	2	3	4	5	6	7	8	9
16		test 5	1	2	3	4	5	6	7	8	9
17
18
19
Sheet1

Cell Formulas
Range	Formula
F4:F8	F4	=ROUND(IF(COUNTA($D4:$E4)<2,"",VLOOKUP($D4,$AB$4:$AC$6,2,0)*(1+$E4)),0)
G4:G8	G4	=IF($B4="","",INDEX(setup,MATCH($B4,test,0),MATCH($C4,Difficulty,0)))
H4:H8	H4	=IF($B4="","",INDEX(replicate,MATCH($B4,test,0),MATCH($C4,Difficulty,0)))
I4:I8	I4	=ROUNDUP(PRODUCT($J4:$M4)/10,0)
N4:V8	N4	=$F4*($G4*ROUNDUP(HLOOKUP(N$3,$C$11:$K$16,2,0)*$J4*$K4*$L4*$M4/10,0)+$H4*HLOOKUP(N$3,$C$11:$K$16,2,0)*$J4*$K4*$L4*$M4)
B12:B16	B12	=B4

Named Ranges
Name	Refers To	Cells
Difficulty	=Sheet1!$AG$3:$AH$3	G4:H8
group	=Sheet1!$AB$4:$AB$6	F4:F8
rate	=Sheet1!$AC$4:$AC$6	F4:F8
replicate	=Sheet1!$AI$4:$AJ$8	H4:H8
setup	=Sheet1!$AG$4:$AH$8	G4:G8
test	=Sheet1!$AF$4:$AF$8	G4:H8

Cells with Data Validation
Cell	Allow	Criteria
B4:B8	List	=test
C4:C8	List	=Difficulty
D4:D8	List	=group
E4:E8	List	=surcharge