Forum Discussion
Profit Formula Challenge
If you believe you can comfortably manipulate arrays, then these eight formula challenges will test your mettle! I superbly love the sixth.
Don't forget that Twifoo intended the challenge to be an examination of the use of advanced array methods available within traditional Excel. The dislike of CSE is understandable but is simply a by-product of the user-interface design.
As I noted earlier, I had trained myself to commit all formulas with CSE but I would have preferred not to need it. For some reason my proposal to reverse the conventions, that is to use CSE to introduce the additional implicit intersection step and use Enter to allow the calculation to proceed as a simple array operation was not well-received 🙂
I share the views of SergeiBaklan and Twifoo in that I see no need to avoid {1,-1,1,-1} as an array constant on the grounds that the notation is similar to the CSE notation. The array constant is an important element of array calculation. They need to be used with care, not because they are arrays, but because they are constants. If there exists a possibility that they may change, a named variable would be more appropriate than a constant embedded within a formula.
79 Replies
Our 'philosophical discussion may be distracting somewhat from your post'. Please accept my apologies.
Would it be a good idea to post a sequence of comments drawing attention to the techniques you have used to achieve the result. I suspect that not all of the 1500+ views will be followed up by file downloads and not all the readers will be confident of picking out the key points.
The starting point might be the role of SUMIFS in creating the array of 'bottom line' values. Since the array is going to be evaluated eight times it needs to be compact and the use of wildcard matches reduces the character count. At the moment, I think MMULT is the only Excel function that is capable of processing a 2D array row by row but it is highly mathematical and may warrant explanation.
[ In my professional career, I probably used Matrix equations more often than written English language but that is far from normal!]
Your apologies are hereby accepted. Contrariwise, written English language is my main course while matrix equations rarely serve as my dessert.
Nevertheless, I presume that you might have overlooked the link to my detailed explanations. If so, kindly do read from here.
You are correct. I had key points in mind, but the detailed treatment serves that purpose as well.
Nothing magical needs to solve the issue, few I'm suggesting.
N11: =SUM($F$8:$N$8)
N12: =COUNTIF($F$8:$N$8,">0")
N13: =COUNTIF($F$8:$N$8,"<0")
N14: =SUMPRODUCT(($F$8:$N$8=MAX($F$8:$N$8))*$F$1:$N$1)
N15: =SUMPRODUCT(($F$8:$N$8=MIN($F$8:$N$8))*$F$1:$N$1)
N16: =SUM(MAX($F$8:$N$8),-MIN($F$8:$N$8))
N17: =SUMPRODUCT(($F$8:$N$8=MIN(IF(($F$8:$N$8<0)," ",
($F$8:$N$8>0)*$F$8:$N$8)))*$F$1:$N$1)
N18: =SUMPRODUCT(($F$8:$N$8=MAX(IF(($F$8:$N$8>0),"",
($F$8:$N$8<0)*$F$8:$N$8)))*$F$1:$N$1)
But I would like to raise few valuable issues:
- Finding Best/Worst performing Branch, your exercise has NO BENCHMARK, considering this finding 4 & 3 are irrelevant.
- Since you got 3 for worst performing are negative values, and on same criteria best should 6 !
- Branch with Lowest/Highest operating profit/Loss, should never 9 & 2. Logically Lowest operating profit is 2100, and Lowest loss is -6800.
- What is the use of finding 38,700, no where utilized .
The idea of this exercise is challenges 1-8 using only raw data in columns A:C, more exactly using defined names (Rule 1). Thus you may forget for a while about numbers in F2:N8, they are only for the illustration and comparing the result.
Benchmarks are defined in formulas for M11:M18
Column F has task & M has results,,, non of these are benchmark,,, like expected "Best Performing Branch" are 4 but what is the lowest value to consider best,, So that about Worst are 3!! If consider the expected answer then "Branch with Lowest Operating Profit" are all 9 never justifies F8:N8>0 since range has few negative values !!
Twifoo Hi
Indeed, it is a wonderful initiative that is full of learning. I appreciate your Profit Challenge Post. Under the given criteria I could hardly do the first challenge.
=SUMPRODUCT((Amounts)*(Types={"Sales Revenues","Additional Revenues"}))-SUMPRODUCT((Amounts)*(Types={"Cost of Sales","Operating Expenses"}))
Eagerly waiting for the solution.
Thanks
Tauqeer
- I will eventually divulge my solutions soon!
After some more fun time on this (and getting behind the rest of the family on re-runs of Lost on TV)
I've found that you can replace the core piece of the formula above
So instead of using this for the "ProfitArray" above, at 186 characters:
=SUMIFS(Amounts,Branches,SEQUENCE(1,9,1,1))
-2*(
SUMIFS(Amounts,Branches,SEQUENCE(1,9,1,1),Types,"Cost of Sales")
+SUMIFS(Amounts,Branches,SEQUENCE(1,9,1,1),Types,"Operating expenses")
)You can get the character count of the core piece of the formula down to 131:
=MMULT(
TRANSPOSE(
IF(Branches=SEQUENCE(1,9,1,1),1,0)*
(1+
(Types="Cost of Sales")*-2
+(Types="Operating Expenses")*-2
)),
Amounts)
1 =SUM(F8:N8)
2 =SUM((F8:N8>0)*1)
3 =SUM((F8:N8<0)*1)
4 =SUM((F8:N8=MAX(F8:N8))*F1:N1)
5 =SUM((F8:N8=MIN(F8:N8))*F1:N1)
6 =SUM(MAX(F8:N8),-MIN(F8:N8))
7 =SUM((F8:N8=MIN(IF((F8:N8<0)," ",(F8:N8>0)*F8:N8)))*F1:N1)
8 =SUM((F8:N8=MAX(IF((F8:N8>0)," ",(F8:N8<0)*F8:N8)))*F1:N1)
Posted by https://www.linkedin.com/in/excel-guy/
- Kindly follow Rule 1.
#1 maybe
=SUMPRODUCT(
Amounts,
(Types="Sales Revenues")
-(Types="Cost of Sales")
+(Types="Additional revenues")
-(Types="operating expenses")
)With the dynamic arrays in my version of Excel it works just as well with plain 'Sum' at the beginning - no curly brackets, no "Ctrl" "shift" "enter" required
So this also works
=SUM(
Amounts*(
(Types="Sales Revenues")
-(Types="Cost of Sales")
+(Types="Additional revenues")
-(Types="operating expenses")
))#2 maybe
=SUMPRODUCT((
SUMIFS(Amounts,Branches,SEQUENCE(1,9,1,1),Types,"Sales Revenues")
-SUMIFS(Amounts,Branches,SEQUENCE(1,9,1,1),Types,"Cost of Sales")
+SUMIFS(Amounts,Branches,SEQUENCE(1,9,1,1),Types,"Additional Revenues")
-SUMIFS(Amounts,Branches,SEQUENCE(1,9,1,1),Types,"Operating expenses")
>0)*1)Again works with Sum at the start, on the later 365 versions
It seems to me there should be shorter versions possible - the formula above 'feels' too long to me, but it's working on my computer
I hope my response has not put others off contributing traditional spreadsheet solutions!
The solutions I am exploring are not available within most Excel versions and would not suit many users.
In the new modification to my solution, I have made it more targeted and eliminated a number
of intermediate variables. The image below shows the spilt range output from a single formula
= LET( Crosstab, SUMIFS(Amounts, Types, listTypes, Branches, HdrBranches), OperatingProfit, MMULT( TRANSPOSE(SignCashFlow), Crosstab ), Profits, IF(OperatingProfit > 0, OperatingProfit), Losses, IF(OperatingProfit<= 0, -OperatingProfit), KeyMetrics,HSTACK("", SUM(OperatingProfit), COUNT(Profits), COUNT(Losses), MAX(Profits)+MAX(Losses)), ExtremeValues, HSTACK("", MAX(Profits), MIN(Profits), -MIN(Losses), -MAX(Losses)), AssociatedBranches, XLOOKUP( ExtremeValues, OperatingProfit, HdrBranches), VSTACK("", Headings1, KeyMetrics, Headings2, AssociatedBranches, ExtremeValues) )I have chosen to show formulas using Charles Williams's HSTACK and VSTACK functions because the intent is clearer, but CHOOSE with the correctly oriented index arrays will achieve the same result.
Although you indirectly admitted that your solution is inherently inapplicable to most Excel users, it nonetheless behooves me to commend you for your tenacious pursuit thereof! Before I ultimately divulge my solutions, I deem it prudent to meanwhile anticipate possible solutions from SergeiBaklan, and other Excel Experts from this Community.
Twifoo , sorry, perhaps some later, bit overloaded with current project and answer here only to have some rest and if that takes not more than 5 minutes. Your task looks quite interesting, but I'm very far from accounting world and for me to understand what "Combined bottom line of All Branches" means will take significant time.
PeterBartholomew1 solutions looks interesting, but as I avoided CSE I try to avoid any macros and third-party add-ins. Charles suggests great additions, however I'd prefer to wait till the same will be out of the box.
I confess, I have never seen or attempted a formula this large in Excel.
= LET( crosstab, SUMIFS(Amounts, Types, listTypes, Branches, HdrBranches), OperatingProfit, MMULT( TRANSPOSE(SignCashFlow), crosstab ), ProfitableBranches, COUNT( IF(OperatingProfit > 0, 1) ), LossmakingBranches, COUNT( IF(OperatingProfit <0, 1) ), MaxProfit, MAX(OperatingProfit), BestPerforming, XLOOKUP( MaxProfit, OperatingProfit, HdrBranches ), MaxLoss, MIN(OperatingProfit), WorstPerforming, XLOOKUP( MaxLoss, OperatingProfit, HdrBranches ), MinGain, MIN(IF(OperatingProfit>0, OperatingProfit) ), MinGainBranch, XLOOKUP( MinGain, OperatingProfit, HdrBranches ), MinLoss, MAX(IF(OperatingProfit<0, OperatingProfit) ), MinLossBranch, XLOOKUP( MinLoss, OperatingProfit, HdrBranches ), CHOOSE({1;2;3;4;5;6;7;8}, SUM(OperatingProfit), ProfitableBranches, LossmakingBranches, BestPerforming, WorstPerforming, MinGainBranch, MinLossBranch, MaxProfit-MaxLoss ) )Would it be better to break the calculation into parts even though it would mean recalculating the SUMIFS calculation within formula? I could, for example, have produced an array of MAX/MINs and then performed an XLOOKUP on the array.
Alternatively, should I have nested the MAX/MINs within the XLOOKUP to reduce the count of LET parameters?
PeterBartholomew1, me too! But would you believe me if I tell you each of my formulas did not exceed 200 characters?
I have faith in you, so yes I am prepared to believe the 200 characters. I would also agree that conciseness is good, but it is not the only metric of relevance. Comparing solutions will always have an element of subjectivity because the secondary objectives can be so different (getting the answer right is not so controversial).
Clearly my selection of names such as 'OperatingProfit' and 'LossmakingBranches' does not serve the cause of conciseness too well! On the other hand, I did consider the number of floating point operations implicit in the formulas and it was that which led me to the unusual strategy of combining the 8 distinct calculations into a single formula.
My formula appears to be 950 characters (including the 140 spaces used to improve layout). That is not too bad when one considers that it delivers all 8 calculations.
I will be interested to see your solution when you are ready to communicate it. Did the 'no CSE' rule impact you significantly?
The thing that might have defeated me is the spirit of 'No CSE'. About 5 years ago I had schooled myself to use CSE for almost all formulas in order to avoid filled formulas and relative referencing.
I also developed the technique of using named formulas to avoid the dreaded implicit intersection and ensure calculations were performed correctly as arrays.
Now it doesn't matter since I am using Excel 365. My formulas would not meet the criteria but ...
= LET( crosstab, SUMIFS(Amounts,Types,listTypes,Branches,HdrBranches), OperatingProfit, MMULT(TRANSPOSE(SignCashFlow), crosstab), ProfitableBranches, COUNT( IF(OperatingProfit>0, 1)), LossmakingBranches, COUNT(IF(OperatingProfit<0, 1)), MinProfit, MIN(OperatingProfit), MaxProfit, MAX(OperatingProfit), BestPerforming, XLOOKUP(MaxProfit,OperatingProfit,HdrBranches), WorstPerforming,XLOOKUP(MinProfit,OperatingProfit,HdrBranches), CHOOSE({1;2;3;4;5;6}, SUM(OperatingProfit), ProfitableBranches, LossmakingBranches, BestPerforming, WorstPerforming, MaxProfit - MinProfit) )The formula avoids the need to repeat the SUMIFS step by retuning all six results as a single array.
PeterBartholomew1 , in opposite I schooled myself to avoid CSE where possible. But by another formulas. Simplest cases SUMPRODUCT() instead of SUM(), MATCH/INDEX instead of MATCH, etc. Yes, I know about the names.
A far more normal strategy I agree. On the other hand SUMPRODUCT and, I think, AGGREGATE are array formulas, just no CSE. It is mainly the user hostility to the presentation of CSE formulas that makes them viewed as methods of last resort - too clever by half!
Logically Excel should prevent a blank line from being inserted between a formula and any of its relative reference because the meaning is ambiguous, but that doesn't happen because in standard Excel practice the risk of error is less important that the fluency of the action-led user interface. Instead, it is the array formula that blocks the insertion of rows, despite there being no such ambiguity. I seem to remember a message such as 'you cannot change part of an array formula'. Since I cannot insert a blank cell into the middle of an array wouldn't it be more logical to assume I do not wish to and simple repaint the array unaltered just as a DA does now (an extra #N/A! at the end if must be).
It was an uphill grind to switch to array formulas but now, thankfully, the position is now reversed. If I were forced to revert to 'enter the formula in cell $PQ17 and fill down 1027 cells', I think I would just delete Excel and use a pocket calculator 🙂
