The calculation in Excel works, but only in a single row

[pyclen]

Hi All,

I apologize as this may be a trivial solution however I am stuck with the current solution provided by one of the members of this forum (Thank you again, bebo021999)

The previous thread is here: getting excel to calculate correctly

my problem is that the solution presented only works for a single row (I thought I could adopt to multiple rows, duh)

The formula is N4 works for this particular cell or better the first row (test 1, N4:V4) however it does not work in N5 and below as well as O5 and on.
What needs to happen is that somehow an index/match or lookup needs to be added to the formula in N4 to make it look up what test it is and how many samples there are to make it work

Again, very appreciative of this forum and any/every help received

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
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

 

[pyclen]

The tests are arranged like shown in the table and each line is only 'be responsible' for that particular test, it would need a match/lookup of that particular test field (B12:B16) to calculate the correct cost

 

[Fluff]

Maybe

=LET(p,INDEX(FILTER(FILTER($C$12:$K$90,$B$12:$B$90=$B4),$C$11:$K$11=N$3),COUNTIFS($B$4:$B4,$B4))*$J4*$K4*$L4*$M4,$F4*($G4*ROUNDUP(p/10,0)+$H4*p))

 

[pyclen]

THank you again for your help, I will trial this and hope this works

 

[Fluff]

Ok

 

[pyclen]

Ok

Thank you again for your help, I have it working for my whole table, I verified the whole calculation using 'hand math' (i.e., calculating it out by hand) as well as using the current calculating tool which has a lot of legacy, is more clumsy, and especially for Newbies and people that are unfamiliar with the workings, difficult to understand.

Excellent stuff

 

[Fluff]

You're welcome & thanks for the feedback.