Beautiful example of turning a common calculation into a reusable Lambda. Having an arsenal of lambdas available is quickly becoming the key to being competitive in Excel, not only for FMWC cases, but in life/work in general!
@DimEarly
3 ай бұрын
I have mixed feelings about whether that's a good thing for the competitions, but I definitely agree with you about the real world - there's a definite edge to be gained!
@IvanCortinas_ES
3 ай бұрын
Excellent solution. Thank you.
@TJ-tb3xm
3 ай бұрын
Thanks! A little over my head but worth learning and struggling through! Looking forward to the lambda series.
@DimEarly
3 ай бұрын
Don't worry, the LAMBDA course is going to start at a gentler pace : )
@TJ-tb3xm
3 ай бұрын
@@DimEarly Thank you! That is very encouraging! Looking forward to it!
@GregHingsbergen
3 ай бұрын
One other thing re. the time spent improving a process... time spent improving one process also helps you make other future process improvements faster as well. It's process improvement improvement.
@DimEarly
3 ай бұрын
Yes! I agree 100%. If you automate 10 processes in a way that takes more time than you save every time, the investment in your skills can still have a very high pay-off over your career.
@peterbartholomew7409
3 ай бұрын
The problem of graduated tax bands or, equivalently, that of banded commission rates is interesting because it admits array solutions that are not based upon past methodology. Operations like sorting and array shaping can be more than a presentational device; they can form a key element of the solution. In the present case, you form a 'high end of bracket' followed by the 'actual income'. Were you then to sort the income into place within the upper bounds, the entire calculation would follow without further tests on sign. =LAMBDA(thresholds, taxrate, [breakdown], [bandName], LAMBDA(income, LET( band, IF(ISOMITTED(bandNname), VSTACK(0, thresholds), bandNname), lower, VSTACK(0, thresholds), upper, SORT(VSTACK(thresholds, income)), split, upper - lower, txDue, split * taxRate, brkdn, HSTACK(band, taxRate, split, txDue), total, HSTACK("Tax due", "", "", SUM(txDue)), IF(breakdown, VSTACK(brkdn, total), SUM(txDue)) ) )) There is extra complexity because the function may be set to provide a simple result or a breakdown by band and is set to accept text descriptions of the bands (eg "Supertax"). I have also partially Curried the function so that it will process an array of incomes using MAP. I make no attempt to achieve brevity; for me there are no prizes for brevity. In fact, when I get back to development, I will probably at least double the formula length with a change control block, error trapping and help. I also think I exercised poor judgement in using the thresholds that are 1 fewer than the bands (0 would have helped). One can't win them all!
@DimEarly
3 ай бұрын
I like it - very neat! I’m a big fan of having a condensed vs detailed output mode like you do here. I think we’re mostly on the same page about formula brevity - I don’t go for brevity for the sake of it (e.g. I use descriptive variable names and white space for readability), although I also don’t like to ‘waste’ space by laying out more steps than are necessary to be able to follow the flow.
@mohammedbaydoun9464
3 ай бұрын
looking forward for the videos of Lambda!
@zulhairibaba
3 ай бұрын
=LAMBDA(income,taxTable, LET( tblMin,INDEX(taxTable,,1), tblMax,INDEX(taxTable,,2), tblRate,INDEX(taxTable,,3), MAP(income,LAMBDA(x,LET(a,tblRate*((SORT(IFERROR(--tblMax,x)))-tblMin),MROUND(SUM((a>0)*a),0.05)))))) or =LAMBDA(income,taxTable, MAP(income,LAMBDA(x,LET(a,INDEX(taxTable,,3)*((SORT(IFERROR(--INDEX(taxTable,,2),x)))-INDEX(taxTable,,1)),MROUND(SUM((a>0)*a),0.05)))))
Пікірлер: 18