Forum Discussion
Profit Formula Challenge
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.
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/
#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 computerkeenadvice you're right. Both of your formulas could be shortened by avoiding the repetition of Types in Challenge 1 and SUMIFS in Challenge 2. As you'll see later, my solutions excluded such repetitions!
I think if you could show me how you've simplified the character length for #1
I would definitely learn something!!
And stand a good chance of getting the other solutions out!!
#1 @ 82 characters
=SUM(
Amounts,
-2*Amounts*((Types="Cost of Sales")+(Types="operating expenses"))
)#1 @ 118 characters
=SUM(
XLOOKUP(
Types,
$E$2:$E$7,
1+(E2:$E$7="cost of sales")*-2+($E$2:$E$7="operating expenses")*-2)
*Amounts)#1 @ 120 characters
=SUM(
SUMIF(Types,UNIQUE(Types),Amounts)
*(1-(UNIQUE(Types)="Operating Expenses")*2-(UNIQUE(Types)="Cost of Sales")*2)
)#2
=SUMPRODUCT((
SUMIFS(Amounts,Branches,SEQUENCE(1,9,1,1))
-2*SUMIFS(Amounts,Branches,SEQUENCE(1,9,1,1),Types,"Cost of Sales")
-2*SUMIFS(Amounts,Branches,SEQUENCE(1,9,1,1),Types,"Operating expenses")
>0)*1)