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

 

[Fluff]

How about

=$F4*($G4*ROUNDUP(SUM(($C$11:$K$11=N$3)*($B$12:$B$16=$B4)*($C$12:$K$16))*$J4*$K4*$L4*$M4/10,0)+$H4*SUM(($C$11:$K$11=N$3)*($B$12:$B$16=$B4)*($C$12:$K$16))*$J4*$K4*$L4*$M4)

 

[pyclen]

=$F4*($G4*ROUNDUP(SUM(($C$11:$K$11=N$3)*($B$12:$B$16=$B4)*($C$12:$K$16))*$J4*$K4*$L4*$M4/10,0)+$H4*SUM(($C$11:$K$11=N$3)*($B$12:$B$16=$B4)*($C$12:$K$16))*$J4*$K4*$L4*$M4)

Wow, that does the trick as far as I can tell, thank you very much for that quick reply
I am amazed by how quickly someone sees a solution in a formula or how to solve the problem, need to watch a bunch more youtube videos and use other resources to get anywhere near this level

I am just a bit confused about what the bolded part above does but I guess it replaces the HLookup

 

[Fluff]

As that works you can shorten it slightly like

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

The 1st two part of the sum create an array of True/false which is converted into 1s & 0s That array is then multiplied by the part in bold so you should get a load of 0s with a single number mixed in.

 

[pyclen]

Again, I am amazed at the mastery of formulas
I checked this in the 'evaluate formula' window and yes, at some point it is just a bunch of zeroes with a single number

Thank you again for your help

 

[Fluff]

You're welcome & thanks for the feedback.

 

[pyclen]

I apologize for messaging you directly however I am running into a snag with your solution

This table was just an example I made up in order to protect the confidentiality however all things work the same way with the exception that the table values in the lower table (c12:k16) are the result of a countA formula. Additionally, my cell references are different, i.e. B12:B16 is B66:B90 in my real table (am not able to share due to confidentiality
reasons)
The formulas work great when I use the first row however once I have a second (or any number of additional) row(s) of tests I get an aggregate value corresponding to the sum of c12:c13 in cell N4, in other words the formula doubles/triples etc the calculated value.

I have checked every line, every cell reference and I am getting the same result.
Any idea why that may occur?

Thank you

 

[Fluff]

Do you have the same test multiple times B66:B90?

 

[pyclen]

It is possible that the same test shows up more than once

 

[Fluff]

That will be the problem. If a test does appear more than once, how would you know which row to use?