Forum Discussion
Help with MMULT for a range containing 100,000+ values
I have been trying to solve a problem for some time now but unable to find an acceptable solution and hence requesting help from you. The attached has a snapshot of what I am trying to achieve. The ...
Hello Peter, Many thanks for the help you extended. Unfortunately, I don't have the version that supports LAMBDA, HSTACK and others that are still in Beta. Hence, am limited to functions that are available in the Released O365.
Sorry, the HSTACK was little more than cosmetic tidying up. This is the same function stripped right back to a relative reference.
= SUM(XLOOKUP(@distinct,letter,number,,, 1):XLOOKUP(@distinct,letter,number,,,-1))
- Hello Peter, The version of excel that I have for implementing the actual solution is Version 2202 (Build 14931.20132) Semi -Annual Enterprise Channel (Preview) and it appears that the LAMBDA functions are not yet there in the said version.
Is there a way to implement the solution without MAP / LAMBDA.So you still have something to look forward to! I couldn't wait, so put my semi-annual update aside and licenced a personal version (insider beta) in order to get to grips with the tumultuous changes.
If you are prepared to accept relative referencing and copy-fill-down then the formula
= SUM( XLOOKUP(@distinct,letter,number,,, 1) : XLOOKUP(@distinct,letter,number,,,-1) )will do the same job (green header).
- Hello Peter, I am not really keen to implement copy-fill-down formulas. Reason being sheer size of everchanging data which might run way past the copy-filled formula range.
Since this tool will be run by junior / inexperienced resources, even if I incorporate certain checks to validate correctness / completeness of data, they can very well overlook and ignore such checks and send out an incorrect utilization report. Hence, summarily trying to avoid copy-fill-down formulas.
- Hello Peter, Many thanks for helping me out with this - exactly the solution I was looking for. I replaced the HSTACK with CHOOSE({1,2}, distinct, total) and it worked the way I wanted. However, I now need to implement this on the actual data I have and check the turnaround time. I will keep you posted if I encounter any issue. Thanks again for your help
- Hello Peter, The version of excel that I have for implementing the actual solution is Version 2202 (Build 14931.20132) Semi -Annual Enterprise Channel (Preview) and it appears that the LAMBDA functions are not yet there in the said version.
Is there a way to implement the solution without MAP / LAMBDA