How to calculate max profit per quarter ?

[ElmerS]

Looking for a Formula to get the Quarter number for the highest profit per Product, like in B16:F16.
The excersiser instracted us that it must be with one formula with no Macro, not User Defined Function and not openning additional colums.
I know how to find the qtr. Number - like for A2: =INT((MONTH(A2)+2)/3) but I am lost in finding how to combine the calculation for the Qtr and the most profitable Qtr. probably with MAX on SUM.
Thank you all in advance,
Elm

[solnajeff]

HI

I have added formulas to calculate what you are looking for using a combination of nested IF, Sum and Max.

Unfortunately it gives quite a long formula, however it is quite straightforward, but if you have any questions please let me know.

Regards

Jeff

[JBeaucaire]

I'm a big fan of naming ranges of cells to make formulas easier to read.

Name the cells for Product A Quarter 1 PAQ1 by highlighting them and typing PAQ1 into the Name Box. Repeat for the other three quarters...PAQ2, PAQ3, and PAQ4.

Now, in cell B16, the following formula will identify the most profitable quarter:

Please Login or Register  to view this content.

The same code without the named ranges:

Please Login or Register  to view this content.

[ElmerS]

Thank you very much, but as you said it is quite a long formula and I with your permission I will for more replies maybe someone will find a shorter solution with an Array Formula or similar.

edit:
this was posted to Jeff.

[ElmerS]

Thank you JBeaucaire, but when I copy the formula from B16 to C16 it returns 2 instead of 1.

The same result [2] is returned in D16, E16 and F16.

What did I do wrong?

[JBeaucaire]

Nothing, I just interpolated my formula into yours and it worked for the first column, I didn't copy it across to see the logic error.

You're right, an array is needed for a short formula, which I'm less proficient at. However, this formula in B16 works, ugly, but copies across smoothly:

Please Login or Register  to view this content.

[ElmerS]

yes, this one works fine but I am sure our instructor did not mean such a long formula.
Jeffs formula is also a little too long but is shorter than yours.
I am sure it can be solved, maybe an array formula.
I saw once an array formula making use of ROW(INDIRECT combined with MOD but cannot find it now.
I will wait for more suggestions.
Thanks again,
Elm

[Cheeky Charlie]

In my mind, this is an obvious way of doing it:
=MATCH(MAX(SUM(B2:B4),SUM(B5:B7),SUM(B8:B10),SUM(B11:B13)),{SUM(B2:B4),SUM(B5:B7),SUM(B8:B10),SUM(B11:B13)},0)

unfortunately, I can not make Excel accept this formula - I'm wondering if it's because I'm trying 2007 for the first time, or whether I'm being a big fat thicky...

[ElmerS]

Thanks, but this does not work in Excel 2003 either.
Sometimes you get a good idea but the hard part is to translate it into "Excel"s language.

[shg]

In B16 and copy across,

=MATCH(MAX(SUBTOTAL(9, OFFSET(B1, {1,4,7,10}, 0, 3))), SUBTOTAL(9, OFFSET(B1, {1,4,7,10}, 0, 3)), 0)

[Cheeky Charlie]

Hey shg, why does subtotal9 work and sum doesn't?

[ElmerS]

More than Perfect.

Thank you.

[daddylonglegs]

Hey shg, why does subtotal9 work and sum doesn't?

Using OFFSET like that allows you to generate an "array of ranges" which SUBTOTAL can then sum, see here

You could generate an array of sums with your method by introducing CHOOSE, i.e.

=MATCH(MAX(CHOOSE({1,2,3,4},SUM(B2:B4),SUM(B5:B7),SUM(B8:B10),SUM(B11:B13))),CHOOSE({1,2,3,4},SUM(B2:B4),SUM(B5:B7),SUM(B8:B10),SUM(B11:B13)),0)

....but, obviously, shg's suggestion is more elegant

[Cheeky Charlie]

....but, obviously, shg's suggestion is more elegant

...



yep

[shg]

Using OFFSET like that allows you to generate an "array of ranges" which SUBTOTAL can then sum

I recollect picking up that construct from a recent DLL post.

Hey shg, why does subtotal9 work and sum doesn't?

Dunno. I sense it's somewhat analogous to the difficulty of using AND and OR in array formulas; they operate on all of their arguments in one go, rather than successively on the elements of an array. Maybe DLL can generalize the issue.

[daddylonglegs]

......I sense it's somewhat analogous to the difficulty of using AND and OR in array formulas; they operate on all of their arguments in one go, rather than successively on the elements of an array. Maybe DLL can generalize the issue.

The link I posted was supposed to help but perhaps it's not specific enough.

It implies that the clever part is using OFFSET to generate an "array of ranges". I think that is clever but you still need to be able to do something with them which is where SUBTOTAL comes in.

If you use SUM with OFFSET then you'd just get a sum of everything which defeats the purpose of creating the separate ranges in the first place. SUBTOTAL is able to operate on each specific range generated by OFFSET and return an array of the results.

In the link examples SUBTOTAL has a twofold purpose, it can evaluate each range seperately....and it ignores filtered rows, possibly that second property obscures the usefulness of the first which is crucial in that example and here.

Note: I don't know why it works like that, it just does.......