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))," ")))))
Formatted differently as follows to show the nesting:
=(
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?
=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)))
An alternative format showing the nesting would be:
=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))
)
ROUND
functions ones. In addition your original quotes wouldn't work from what I know.