0

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))
    )
8
  • 3
    please post some sample data, it's very difficult to understand your issue currently. May 8, 2017 at 9:12
  • 1
    If you look at the edit you'll find that the formulas you posted aren't valid. You have at least two closing parenthesis in your first formula that have no match and in your second there are multiple missing to close the ROUND functions ones. In addition your original quotes wouldn't work from what I know.
    – Seth
    May 8, 2017 at 9:34
  • 1
    If you are willing to use a helper column you can do this with an index/match formula. May 8, 2017 at 13:21
  • 1
    Apologies, is there a way to post the data in a readable format?
    – Reynola5
    May 10, 2017 at 9:57
  • 1
    @Reynola5, If you "address" a comment with @, like I did here, the person will get an alert to your posting. Otherwise, nobody will be aware of a comment unless they happen to stumble across it. To your question, you can post a link to a screenshot (imgur.com is the best place, someone with adequate rep will embed it in your question for you). If it is a lot of values someone would need to re-input to answer the question, you can turn it into a text table and embed it using a service like senseful.github.io/web-tools/text-table or tablesgenerator.com/text_tables.
    – fixer1234
    May 10, 2017 at 19:23

0

You must log in to answer this question.

Browse other questions tagged .