• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula Challenge 024 - Sum Of Items Based On Hierarchy Level

Sam Mathai Chacko

Sam Mathai Chacko

Active Member
So we haven't had a new challenge in a while, so I thought I'll remind people that we have a section of the forum to pick your brains, by posting the following formula based challenge. I came across this in VBAExpress; so the credit for the question goes to the OP. So anyway, the challenge is non-VBA, and it goes like this

Illustration:
Formula Challenge 024.png
You have a list of items and their prices in columns B and C. In column A, the levels are indicated as .[x]#, where .[x] represents the level of the hierarchy, and # is the number which represents that hierarchy. So if one were to identify the hierarchy, you could either count the number of dots/periods/decimals (whatever you want to call it), or extract the numeric value on the right hand side of the value in Col A.

Note that there is no price value for the top-most hierarchy, simply because that's the highest level. By the way, column B is irrelevant in our question.

The sample output is shown in column D, and this you can use as reference. What we are looking for is a formula which does not require a helper column.

You can assume that there is no data below this table.

And if it's any motivation, I cooked up a simple array formula that is 98 characters long.
 

Attachments

  • challenge .xlsx
    13.2 KB · Views: 153
Last edited:
Hi Sam ,

Can you please post a workbook with the data , so that everyone does not have to go through the same effort in setting it up ?

Not that I am going to attempt this challenge , but it may help others.

Narayan
 
Hi Sam ,

This seems to give the same results for the test data ; is it correct ?

=IFERROR(SUM(OFFSET(C2,,,MATCH(TRUE,RIGHT(A3:$A$20)<=RIGHT(A2),0))),SUM(C2:$C$20))

entered as an array formula , using CTRL SHIFT ENTER.

Narayan
 
I might add that the only problem being, if the level exceeds to two digits, like 10, 11 or above, then the solution might not work exactly as intended
 
Narayan

Of course that will work, but again, the idea is that the levels could be as long is it can... maybe 3 digits or 4 digits. Your original formula is fundamentally correct, but maybe could be a little more generic, to the extend that it considers any number of levels, and not just up to 99. Hope that makes sense. :)
 
No takers? I understand it's relatively simple compared to a lot of the other formula challenges, but that shouldn't make you abstain!

Well, I'll go ahead and post my solution

=SUM(OFFSET(C2,,,MATCH(0,--(--SUBSTITUTE(0&A3:A$22,".","0")>--SUBSTITUTE(A2,".","0")),)))
 
Thanks Faseeh for the feedback.

Just realized that I could take out 5 more characters :)

=SUM(OFFSET(C2,,,MATCH(0,N(--SUBSTITUTE(0&A3:A$22,".",0)>--SUBSTITUTE(A2,".",0)),)))
 
Hi,

what if the data is like this:

upload_2015-11-9_15-40-42.png

the value of a particular row is the sum of all "lower" levels (1 is the highest level).

is it possible to create a formula to caluculate the appropriate sum based on the level?
 
@Emmanuel Guido Interesting variation of original question. Here is one formula that seems to work ok.

Assuming your data is in range A2:B11, you can use below formula.

screenshot-098.png

=IF(B2<>"",B2,SUM(B2:INDEX(B2:$B$11, MATCH(A2,A3:$A$11,0))))

We essentially get the value in column B if one is present.
Else, we look for next cell with same level and add up values up to that cell from current cell (using a combination of relative and absolute references)

For more on the usage of INDEX in a reference, see this page:

http://chandoo.org/wp/2013/09/18/index-formula-usage-and-tips/
 
Hi.

Thanks for the feedback!

Yes, the formula is great. Just need to add error handling if no Match found.

Anyway, the formula I needed was a bit more complicated. The source data I have sometimes has amount at a level with lower levels, and it may/may not be correct (e.g. level 2 has 999 but sum of level 3 is 1000). See updated sample below and the formula I used. (it is quite crude but works anyway, and I would not have solved it without your feedback, thanks again.)

upload_2015-11-20_15-27-9.png

=IF(AND(B2<>"", B2>SUMIFS(C2:INDEX(C3:$C$11,IFERROR(MATCH(A2,A3:$A$11,0),0)),A2:INDEX(A3:$A$11,IFERROR(MATCH(A2,A3:$A$11,0),0)),A2+1)),B2,SUMIFS(C2:INDEX(C3:$C$11,IFERROR(MATCH(A2,A3:$A$11,0),0)),A2:INDEX(A3:$A$11,IFERROR(MATCH(A2,A3:$A$11,0),0)),A2+1))

Regards,
 
Hi @Emmanuel Guido,
A small attempt from my side for this problem,

=IF(A2<A3,SUMIF(OFFSET(A3,,,IFERROR(MATCH(A2,A3:A$11,0),COUNT(A3:A$11))),A2+1,C3:C$11),B2)

Assuming your data is in range A2:B11, you can use below formula.
We take the value in column B if row below has same or higher level.
Else, we look for next cell with same level and add up values up to that cell from cell below.

Cheers!
Vivek
 
I know there are few shorter approach already posted above, I just wanted to give it a try:
{=IFERROR(SUM(OFFSET(C2,,,MATCH(1,IF(VALUE(SUBSTITUTE(A2,".",""))<VALUE(SUBSTITUTE(A3:$A$20,".","")),0,1),0))),SUM(C2:$C$20))}
 
You must log in or register to reply here.
Back
Top