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