A formula to consider the values of x rows and y columns and fill it to 1 if the two constraint are met and 0 if not

[thirudg]

Hello,

Any suggestion for an excel formula to derive A2, B2, C2, D2 values.



Eg:

Category	X	A	B	C	D
P1	4	1	1	1	1
P2	3	0	1	1	1
P3	2	0	1	1	0
Y		1	3	3	2

i need a formula or macro which takes into consideration of both the x and y columns values to fill the A,B ,C and D columns with 1 or 0 so that if i sum the rows and columns i will get the x and y values of the respective rows and columns .

 

[StephenCrump]

Welcome to the Forum!

	A	B	C	D	E	F	G
1	Category	X		A	B	C	D
2	P1	4		1	1	1	1
3	P2	3		0	1	1	1
4	P3	2		0	1	1	0
5	Y			1	3	3	2
Sheet1

Cell Formulas
Range	Formula
D2:G4	D2	=--AND(SUM(D$1:D1)<D$5,SUM($C2:C2)<$B2)

 

[Fluff]

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: A formula or macro which takes into consideration of both the x and y values
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.