Multiplication with levels problem eg 1.1.4.2

Hey,

I am a bit stuck on this issues for a while now.

I have a qty field on different levels and i would like them multiplied by the parent level and the whole way up the tree. It probably easier to show you , i have added a workbook to help explain.

eg. 1.1.4 would be the qty beside level 1 X qty beside lvl 1.1 X qty beside lvl 1.1.4

Cheers,

Brian

Try placing the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter) in cell C2 and drag-copying it down as needed:

=PRODUCT(INDEX($B$2:$B$14,N(IF(,,MATCH(LEFT($A2,ROW(INDIRECT("1:"&(LEN($A2)+1)/2))*2-1),$A$2:$A$14&"",)))))

Damn man,

After looking at it i still don't know what you did or what Ctrl+Shift+Enter does but it works. Your a genius.

Cheers =)

This is part of a big list of levels where there may be multiple occurrences of 1, 1.1 etc. Using the formulae above, this would calculate all 1s in the array.

If i added an extra field say A1, A1.1, B1, B1.1. The formulae does not work, Would it be possible to work around this?

Thanks,

Or would it be possible to group this calculation depending on another column (say D) which contains a,b,c etc. So as we have above but only include the entry's that are grouped together.

Does that make sense?

Hey Root,

That formulae worked really well. The only issue is it causes an error once the indent goes past 9. e.g. 1.10. Do you have any idea how to fix this? That would be really helpful, thanks. Ad

I agree with JeteMc give an example sheet with all eventualities and the expected solution.

for example can the numbers be 2 digits or even 3, can the letters you have added be more than 1 character?

It can be frustrating providing a solution for a question that doesn't reflect reality