CHOOSE function with Boolean expressions vs. Too many nested IF functions

[Reynola5]

Hi, my first time posting. Would really appreciate any help .

I have a file showing products in one column and the volume of each product in another.

I am trying to create a list of products groupings (CONCATENATE) based on the cumulative volume of the products. Within my source data each product is shown in Column B has a volume in Column E.

The list I wish to create begins in row 385 and I have numbered each item in column C. So the first item in the list is labelled no. “1” in cell C385.
The result I am looking for is that, once the cumulative amount in my data column B exceeds the list number in my results, I can CONCATENATE the names of a set of these products from Column B of my source data.

For example if the cumulative value of my list at cell E286 is 4, the first 4 lines of my results list should populate.
I have a working formula to achieve this as follows:

=(IF(ROUND(SUM($E$283),0)>=C385,(CONCATENATE($B$281,$B$282,$B$283)),” “)&(IF(ROUND(SUM($E$283:$E$284),0)>=C385,(CONCATENATE($B$281,$B$282,$B$284)),” “)&IF(ROUND(SUM($E$283:$E$285),0)>=C385,(CONCATENATE($B$281,$B$282,$B$285)),” “)&IF(ROUND(SUM($E$283:$E$286),0)>=C385,(CONCATENATE($B$281,$B$282,$B$286)),” “)))))

However as the lists are quite long this would require too many nested IF functions. I have heard about using CHOOSE functions with Boolean expressions to achieve such results. I have tried converting my formula to this option below, however I am afraid that I don’t fully understand this formula, particularly the “Error” component. Might someone be able to point out where I am going wrong with the below, or even better suggest an alternative solution to achieve my goal? Any help would really be appreciate

=CHOOSE(1+(ROUND(SUM($E$283),0)>=C385*1+(ROUND(SUM($E$283:$E$284),0)>=C385*2+(ROUND(SUM($E$283:$E$285),0)>=C385*3+(ROUND(SUM($E$283:$E$286),0)>=C385*4,”Error”,(CONCATENATE($B$281,$B$282,$B$283)),(CONCATENATE($B$281,$B$282,$B$284)),(CONCATENATE($B$281,$B$282,$B$285)),(CONCATENATE($B$281,$B$282,$B$286)))

Thanks

 

[StephenCrump]

Welcome to the Forum, and sorry you haven't had any response yet.

You should get more interest if you can post a simplified screenshot of what you're trying to achieve.

See Part B here for a couple of ways you can do this: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

 

[ukmikeb]

For example if the cumulative value of my list at cell E286 is 4, the first 4 lines of my results list should populate.
I have a working formula to achieve this as follows:

=(IF(ROUND(SUM($E$283),0)>=C385,(CONCATENATE($B$281,$B$282,$B$283)),” “)&(IF(ROUND(SUM($E$283:$E$284),0)>=C385,(CONCATENATE($B$281,$B$282,$B$284)),” “)&IF(ROUND(SUM($E$283:$E$285),0)>=C385,(CONCATENATE($B$281,$B$282,$B$285)),” “)&IF(ROUND(SUM($E$283:$E$286),0)>=C385,(CONCATENATE($B$281,$B$282,$B$286)),” “)))))

However as the lists are quite long this would require too many nested IF functions.

They are NOT NESTED IFs, so you wouldn't have any limit!

I have heard about using CHOOSE functions with Boolean expressions to achieve such results. I have tried converting my formula to this option below, however I am afraid that I don’t fully understand this formula, particularly the “Error” component. Might someone be able to point out where I am going wrong with the below, or even better suggest an alternative solution to achieve my goal? Any help would really be appreciate

=CHOOSE(1+(ROUND(SUM($E$283),0)>=C385*1+(ROUND(SUM($E$283:$E$284),0)>=C385*2+(ROUND(SUM($E$283:$E$285),0)>=C385*3+(ROUND(SUM($E$283:$E$286),0)>=C385*4,”Error”,(CONCATENATE($B$281,$B$282,$B$283)),(CONCATENATE($B$281,$B$282,$B$284)),(CONCATENATE($B$281,$B$282,$B$285)),(CONCATENATE($B$281,$B$282,$B$286)))

In CHOOSE you have a value of a cell or an expression which relates to the position in a list, Normally this is a serial value incremented from 1.

We will await your screenshot before suggesting how this problem can be solved.

 

[Reynola5]

Thank you for the response. This is already quite helpful. I have included a screenshot below with sample data and the result using the IF function. As noted the issue is that I will have numerous Groups and seasons so the formula becomes quite long using IF.

	DATA:
	Number of Product groupings		10
	Combinations:
	Group 1	Proportions	Weighting	Products included in grouping
	Summer	60%
	Bargain	12%	7%	1
	Saver	20%	12%	1
	Standard	30%	18%	2
	Premium	18%	11%	1
	Luxury	20%	12%	1
	Winter	40%		0
	Bargain	12%	5%	0
	Saver	20%	8%	1
	Standard	30%	12%	1
	Premium	18%	7%	1
	Luxury	20%	8%	1
	RESULTS:
		#		Product
		1		Group 1Summer Bargain
		2		Group 1Summer Saver
		3		Group 1Summer Standard
		4		Group 1Summer Standard
		5		Group 1Summer Premium
		6		Group 1Summer Luxury
		7		Group 1WinterSaver
		8		Group 1WinterStandard
		9		Group 1WinterPremium
		10		Group 1WinterLuxury
	Formula for Product #1
	=IF(ROUND(SUM($E$5),0)>=C18,(CONCATENATE($B$3,$B$4,$B$5)),IF(ROUND(SUM($E$5:$E$6),0)>=C18,(CONCATENATE($B$3,$B$4,$B$6)),IF(ROUND(SUM($E$5:$E$7),0)>=C18,(CONCATENATE($B$3,$B$4,$B$7)),IF(ROUND(SUM($E$5:$E$8),0)>=C18,(CONCATENATE($B$3,$B$4,$B$8)),IF(ROUND(SUM($E$5:$E$9),0)>=C18,(CONCATENATE($B$3,$B$4,$B$9)),IF(ROUND(SUM($E$5:$E$11),0)>=C18,(CONCATENATE($B$3,$B$10,$B$11)),IF(ROUND(SUM($E$5:$E$12),0)>=C18,(CONCATENATE($B$3,$B$10,$B$12)),IF(ROUND(SUM($E$5:$E$13),0)>=C18,(CONCATENATE($B$3,$B$10,$B$13)),IF(ROUND(SUM($E$5:$E$14),0)>=C18,(CONCATENATE($B$3,$B$10,$B$14)),IF(ROUND(SUM($E$5:$E$15),0)>=C18,(CONCATENATE($B$3,$B$10,$B$15))))))))))))

<tbody>
</tbody><colgroup><col><col><col span="2"><col><col span="2"></colgroup>

 

[wideboydixon]

The problem becomes much easier if you add a couple of helper columns:

Book1
	A	B	C	D	E	F	G	H	I
1	DATA:							RESULTS:
2	Number of Product groupings		10						Product
3	Combinations:							1	Group 1 Summer Bargain
4	Group 1	Proportions	Weighting	Products included in grouping	Cumulative count	Product		2	Group 1 Summer Saver
5	Summer	60%						3	Group 1 Summer Standard
6	Bargain	12%	7%	1	1	Group 1 Summer Bargain		4	Group 1 Summer Standard
7	Saver	20%	12%	1	2	Group 1 Summer Saver		5	Group 1 Summer Premium
8	Standard	30%	18%	2	4	Group 1 Summer Standard		6	Group 1 Summer Luxury
9	Premium	18%	11%	1	5	Group 1 Summer Premium		7	Group 1 Winter Saver
10	Luxury	20%	12%	1	6	Group 1 Summer Luxury		8	Group 1 Winter Standard
11	Winter	40%			6			9	Group 1 Winter Premium
12	Bargain	12%	5%	0	6	Group 1 Winter Bargain		10	Group 1 Winter Luxury
13	Saver	20%	8%	1	7	Group 1 Winter Saver
14	Standard	30%	12%	1	8	Group 1 Winter Standard
15	Premium	18%	7%	1	9	Group 1 Winter Premium
16	Luxury	20%	8%	1	10	Group 1 Winter Luxury
Sheet1

Cell Formulas
Range	Formula
C6	=$B6*$B$5
C12	=$B12*$B$11
E6	=SUM($D$5:$D6)
F6	=$A$4&" "&$A$5&" "&$A6
F12	=$A$4&" "&$A$11&" "&$A12
I3	=INDEX($F$6:$F$16,IFERROR(MATCH($H3-1,$E$6:$E$16,1)+1,1))

You can always hide them from view ...

WBD