water fall

[Zubair]

1 to 100 is available in cell from B2 to CW2,

Based on given data range A10 to C14 (column C) I want formula to bring 1& 2 against each fruits in B3 TO CW6 please help.

A3 = Apple (B3 to AT3 =1)
A4 = Banana (B4 to AT4 = 2 & AU4 to BX4 = 1)
A5 = Orange (B5 to BX5 = 2 & BY5 to CM5 = 1)
A6 = Peach (B6 to CM6 = 2 & CN9 to CW6 =1)


A10 to C14

Apple	45	45
Banana	30	75
Orange	15	90
Peach	10	100
Total	100

 

[Zubair]

I got it =IF(AND($C10>B$2,$C10<$C11),1,2)

 

[Zubair]

No sorry its not complete formula B4 to AT4 is not showing 2

 

[KRice]

Try this, with the formula copied out to column CW...

MrExcel20200908.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1
2
3	Apple	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
4	Banana	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2
5	Orange	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2
6	Peach	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2
7
8
9		# 1	# 2
10	Apple	45	45
11	Banana	30	75
12	Orange	15	90
13	Peach	10	100
14	Total	100
Sheet2

Cell Formulas
Range	Formula
B3:W6	B3	=IF(COLUMNS($B3:B3)<=SUMPRODUCT(--($A$10:$A$13=$A3)*($C$10:$C$13-$B$10:$B$13)),2,IF(COLUMNS($B3:B3)<=SUMPRODUCT(--($A$10:$A$13=$A3)*($C$10:$C$13)),1,""))
B14	B14	=SUM(B10:B13)

 

[Zubair]

KRice - Yes thats the formula I was looking for Many thanks.

 

[KRice]

You're welcome...happy to help.

 

[Peter_SSs]

Another option with a slightly reduced formula (some columns hidden to reduce size) ..

20 09 10.xlsm
	A	B	C	D	E	F	G	H	I	J	K																										AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX																						BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW
1
2
3	Apple	1	1	1	1	1	1	1	1	1	1																										1	1	1	1	1	1	1	1	1	1
4	Banana	2	2	2	2	2	2	2	2	2	2																										2	2	2	2	2	2	2	2	2	2	1	1	1	1																						1	1	1	1	1
5	Orange	2	2	2	2	2	2	2	2	2	2																										2	2	2	2	2	2	2	2	2	2	2	2	2	2																						2	2	2	2	2	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
6	Peach	2	2	2	2	2	2	2	2	2	2																										2	2	2	2	2	2	2	2	2	2	2	2	2	2																						2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	1	1	1	1	1	1	1	1	1	1
7
8
9		# 1	# 2
10	Apple	45	45
11	Banana	30	75
12	Orange	15	90
13	Peach	10	100
14	Total	100
Waterfall

Cell Formulas
Range	Formula
B3:K6,AK3:AX6,BT3:CW6	B3	=IF(COLUMNS($B:B)<=VLOOKUP($A3,$A$10:$C$13,3,0)-VLOOKUP($A3,$A$10:$B$13,2,0),2,IF(COLUMNS($B:B)>VLOOKUP($A3,$A$10:$C$13,3,0),"",1))
B14	B14	=SUM(B10:B13)

 

[Peter_SSs]

Or if you are happy to include another column in that bottom table, it reduces further

20 09 10.xlsm
	A	B	C	D	E	F	G	H	I	J	K																										AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX																						BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW
1
2
3	Apple	1	1	1	1	1	1	1	1	1	1																										1	1	1	1	1	1	1	1	1	1
4	Banana	2	2	2	2	2	2	2	2	2	2																										2	2	2	2	2	2	2	2	2	2	1	1	1	1																						1	1	1	1	1
5	Orange	2	2	2	2	2	2	2	2	2	2																										2	2	2	2	2	2	2	2	2	2	2	2	2	2																						2	2	2	2	2	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
6	Peach	2	2	2	2	2	2	2	2	2	2																										2	2	2	2	2	2	2	2	2	2	2	2	2	2																						2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	1	1	1	1	1	1	1	1	1	1
7
8
9		# 1	# 2
10	Apple	45	45	0
11	Banana	30	75	45
12	Orange	15	90	75
13	Peach	10	100	90
14	Total	100
Waterfall (3)

Cell Formulas
Range	Formula
B3:K6,AK3:AX6,BT3:CW6	B3	=IF(COLUMNS($B:B)<=VLOOKUP($A3,$A$10:$D$13,4,0),2,IF(COLUMNS($B:B)>VLOOKUP($A3,$A$10:$C$13,3,0),"",1))
D10:D13	D10	=C10-B10
B14	B14	=SUM(B10:B13)