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.
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 !!
