How can I make Excel MAX function to return an array?

P

pasunmaa

New Member
Joined
Mar 22, 2017
Messages
18
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I have an two-dimensional in cell array {0,2;3,0;0,5;6,0;0,0;0,8} and I would need to to get Max value of each column in a single cell. So, I would like to get {6,8} as a response from {=MAX({0,2;3,0;0,5;6,0;0,0;0,8})} instead of just 8.

02
30
05
60
00
08

<tbody>
</tbody>

In the next step I would prefer to get ROW number, where Max value is present?

I have tried everything, but can't make MAX to return an array. MAXIF would return an array, but it does not accept ROW function as max_range.

Are these actions doable with Excel formulas?
 
Sort by date Sort by votes
Hi, you are absolute right: my portfolio changes over time. I'm actually starting my calculations from transactions table, which structure is



-- removed inline image ---


Then I calculate previous rows and next rows, previous units and units for each row:
-- removed inline image ---
s!AtMqEk6YiyvupbVQb_cOxIiDjwatgQ
Using tTranscation table I calculate # of units owned at each day

INDEX(tTransactions[Units];
N(IF(1; LARGE(IF(
((tTransactions[Date]<=$A2) * (tTransactions[Units]>0) * ((tTransactions[Next Row]=0) + (tTransactions[Next Row] > MAX(IF(tTransactions[Date]<=$A2; ROW(tTransactions[Date]))))) * ROW(tTransactions[B&A]))
>0;
((tTransactions[Date]<=$A2) * (tTransactions[Units]>0) * ((tTransactions[Next Row]=0) + (tTransactions[Next Row] > MAX(IF(tTransactions[Date]<=$A2; ROW(tTransactions[Date]))))) * ROW(tTransactions[B&A]))
); ROW($A$1:INDIRECT("A"&SUM(IF(
((tTransactions[Date]<=$A2) * (tTransactions[Units]>0) * ((tTransactions[Next Row]=0) + (tTransactions[Next Row] > MAX(IF(tTransactions[Date]<=$A2; ROW(tTransactions[Date]))))) * ROW(tTransactions[B&A]))
<>0;1;0))))) ))-1; 1);


e.g. for shares owned {"NOKIA";"KO";"MSFT"} on 2nd of July it gives me {24;28;8}

Then I calculate prices (using my old formula)

SUMIFS(HistoricalQuotes[Price];HistoricalQuotes[Date];"="&$A2; HistoricalQuotes[Ticker]; T(IF(1;

INDEX(tTransactions[Asset];
N(IF(1; LARGE(IF(
((tTransactions[Date]<=$A2) * (tTransactions[Units]>0) * ((tTransactions[Next Row]=0) + (tTransactions[Next Row] > MAX(IF(tTransactions[Date]<=$A2; ROW(tTransactions[Date]))))) * ROW(tTransactions[B&A]))
>0;
((tTransactions[Date]<=$A2) * (tTransactions[Units]>0) * ((tTransactions[Next Row]=0) + (tTransactions[Next Row] > MAX(IF(tTransactions[Date]<=$A2; ROW(tTransactions[Date]))))) * ROW(tTransactions[B&A]))
); ROW($A$1:INDIRECT("A"&SUM(IF(
((tTransactions[Date]<=$A2) * (tTransactions[Units]>0) * ((tTransactions[Next Row]=0) + (tTransactions[Next Row] > MAX(IF(tTransactions[Date]<=$A2; ROW(tTransactions[Date]))))) * ROW(tTransactions[B&A]))
<>0;1;0))))) ))-1; 1)

)) );

I get for {"NOKIA";"KO";"MSFT"} on 2nd of July {202,07;102,07;302,07}

Additionally I also calculate exchange rates, as I have shares in multiple currencies, but in my sample file I have marked all currency rates ones, to keep sample simple.

To get a portfolio value on a date I use SUMPRODUCT(units, prices, forexrates). I works as long there are prices for all shares on needed day.

You can find my sample file from here:
https://1drv.ms/x/s!AtMqEk6YiyvupbVM_Oq4To8_ilvutA

I wish that I could do portfolio calculation without any additional tables. I wonder if its feasible.


<tbody>
DateTypeBrokerAssetCurrencyin EURTransacted UnitsTransacted Price (per unit)FeesStock Split RatioB&APrev RowNext RowPrevious UnitsUnits
21.4.2006BuyNordeaNOKIAEUR1100,018,3802,001,0NordeaNOKIA040,0100,00
2.1.2007BuyNordnetMSFTUSD0,920,0020,0002,001,0NordnetMSFT050,020,00
28.2.2007SellNordeaNOKIAEUR126,0012,3502,001,0NordeaNOKIA27100,074,00
5.3.2007SellNordnetMSFTUSD0,912,007,0602,001,0NordnetMSFT31020,08,00
14.3.2007BuyNordnetKOEUR0,9528,005,4002,001,0NordnetKO080,028,00
14.3.2007SellNordeaNOKIAEUR150,0010,0102,001,0NordeaNOKIA41174,024,00
6.7.2007BuyNordnetKOUSD0,92,0040,0004,001,0NordnetKO61228,030,00
9.7.2007BuyNordnetCCFEUR13,00380,0004,001,0NordnetCCF000,03,00
9.7.2007SellNordnetMSFTUSD0,98,005,0002,001,0NordnetMSFT508,00,00
9.7.2007SellNordeaNOKIAEUR124,0014,0002,001,0NordeaNOKIA7024,00,00
9.7.2007SellNordnetKOUSD0,929,0042,0002,001,0NordnetKO8030,01,00

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

</tbody>

<tbody>
</tbody>
 
Last edited:
Upvote 0

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Here is a transaction-table that's not shown correctly in my previous reply:

Date
TypeBrokerAssetCurrencyin EURTransacted UnitsTransacted Price (per unit)FeesStock Split RatioB&APrev RowNext RowPrevious UnitsUnits
21.4.2006BuyNordeaNOKIAEUR1100,018,3802,001,0NordeaNOKIA040,0100,00
2.1.2007BuyNordnetMSFTUSD0,920,0020,0002,001,0NordnetMSFT050,020,00
28.2.2007SellNordeaNOKIAEUR126,0012,3502,001,0NordeaNOKIA27100,074,00
5.3.2007SellNordnetMSFTUSD0,912,007,0602,001,0NordnetMSFT31020,08,00
14.3.2007BuyNordnetKOEUR0,9528,005,4002,001,0NordnetKO080,028,00
14.3.2007SellNordeaNOKIAEUR150,0010,0102,001,0NordeaNOKIA41174,024,00
6.7.2007BuyNordnetKOUSD0,92,0040,0004,001,0NordnetKO61228,030,00
9.7.2007BuyNordnetCCFEUR13,00380,0004,001,0NordnetCCF000,03,00
9.7.2007SellNordnetMSFTUSD0,98,005,0002,001,0NordnetMSFT508,00,00
9.7.2007SellNordeaNOKIAEUR124,0014,0002,001,0NordeaNOKIA7024,00,00
9.7.2007SellNordnetKOUSD0,929,0042,0002,001,0NordnetKO8030,01,00

<tbody>
</tbody>
 
Upvote 0
Hi,

It's not that difficult to return an array comprising maximum/minimum values from each row/column within a two-dimensional array when the latter is an actual worksheet range. Things become a little more complicated when the array being queried is one derived as the result of some function(s).

In your case, and using the workbook you provided, the required construction for row 4 would be:

SMALL(IF(HistoricalQuotes[Ticker]={"MSFT","NOKIA","KO"},IF(HistoricalQuotes[Date]<=E4,HistoricalQuotes[Date])),MOD(LARGE(TRANSPOSE((COLUMNS({"MSFT","NOKIA","KO"})-ROW(INDIRECT("1:"&COLUMNS({"MSFT","NOKIA","KO"})))))*10^6+IF(HistoricalQuotes[Ticker]={"MSFT","NOKIA","KO"},IF(HistoricalQuotes[Date]<=E4,RANK(HistoricalQuotes[Date],HistoricalQuotes[Date],-1),0)),(ROW(INDIRECT("1:"&COLUMNS({"MSFT","NOKIA","KO"})))-1)*ROWS(HistoricalQuotes[Date])+1),10^6))

which will return the array:

{39266;39267;39267}

as required.

This construction will require committing within an array formula**, and so your main formula would then become the CSE-entered:

=SUM({10;20;30}*SUMIFS(HistoricalQuotes[Price], HistoricalQuotes[Date],SMALL(IF(HistoricalQuotes[Ticker]={"MSFT","NOKIA","KO"},IF(HistoricalQuotes[Date]<=E4,HistoricalQuotes[Date])),MOD(LARGE(TRANSPOSE((COLUMNS({"MSFT","NOKIA","KO"})-ROW(INDIRECT("1:"&COLUMNS({"MSFT","NOKIA","KO"})))))*10^6+IF(HistoricalQuotes[Ticker]={"MSFT","NOKIA","KO"},IF(HistoricalQuotes[Date]<=E4,RANK(HistoricalQuotes[Date],HistoricalQuotes[Date],-1),0)),(ROW(INDIRECT("1:"&COLUMNS({"MSFT","NOKIA","KO"})))-1)*ROWS(HistoricalQuotes[Date])+1),10^6)), HistoricalQuotes[Ticker],{"MSFT";"NOKIA";"KO"}))

Regards
 
Upvote 0
Hi,

thx for the suggestion. Unfortunately, I cannot get it work.

SMALL(array, k) returns to me {#N/A}, where
array resolves to {FALSE;FALSE;FALSE;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A}
and k resolves to {#N/A}

HistoricalQuotes[Ticker]={"MSFT";"NOKIA";"KO"} resolves to {FALSE;FALSE;FALSE;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A}

Am I doing something incorrectly?
 
Upvote 0
I take it you're testing the proposed solution on the file to which you provided a link?

And that you're also committing it correctly as an array formula**?

Regards
 
Upvote 0
You appear to have transposed the array constant in the formula I provided, i.e.:

{"MSFT","NOKIA","KO"}

has become:

{"MSFT";"NOKIA";"KO"}

in your version.

Only the final version of this array (i.e. that given as criteria2 within the SUMIFS) should be as you give.

If you are using a version of Excel for which the argument separator in formulas is the semicolon, not the comma, you should note that this does not also apply to array constants.

I'm not sure what version you are using, so cannot tell you what the equivalent horizontal and vertical separators should be in your case, though one possibility might be:

{"MSFT""NOKIA""KO"}

Regards
 
Upvote 0
The post editor did not like my attempt at using the slash. I intended \ as separator within that final array constant, which I know is used as vertical-array separator in Italian (post-2007) versions of Excel, amongst others.

Regards
 
Last edited:
Upvote 0
XOR LX said:
The post editor did not like my attempt at using the slash. I intended \ as separator within that final array constant, which I know is used as vertical-array separator in Italian (post-2007) versions of Excel, amongst others.
Click to expand...

Apologies. That should have been "horizontal".

Regards
 
Upvote 0
My mistake. Horizontal-array separator must be in my case backslash '\'. Once I fixed your formula worked beautifully. Now, I need to fit it into my bigger Excel which starts from transaction table.

I wonder if you are aware any specification, blog article or similar describing which Excel functions support array-formulas?
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Function to return array of 10 largest absolute values while maintaining sign
Replies
0
Views
160
eurobonds
E
LRATOZ
  • Question
In Excel 2021 what is the formula to return a date when the max value happened (Compare the same month over many years)
Replies
2
Views
891
LRATOZ
LRATOZ
Katelynne
  • Solved
WorksheetFunction.Min Returning Inaccurate Values from Array Varriable (Excel Bug?)
Replies
7
Views
685
Domenic
D
S
  • Question
Trendline values using Dynamic Range in Excel - comparing with LINEST function
Replies
1
Views
401
Domenic
D
K
  • Solved
How do i populate an Array from another array
Replies
9
Views
2K
KasperC
K

Forum statistics

Threads
1,216,765
Messages
6,132,593
Members
449,737
Latest member
naes

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top