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

[pasunmaa]

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.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]0[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]

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?

 

[pasunmaa]

Hi, you are absolute right: my portfolio changes over time. I'm actually starting my calculations from transactions table, which structure is


[TABLE="width: 498"]
<tbody>[TR]
[TD][TABLE="width: 745"]
<tbody>[TR]
[TD]
-- removed inline image ---


Then I calculate previous rows and next rows, previous units and units for each row:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TABLE="width: 745"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Type[/TD]
[TD]Broker[/TD]
[TD]Asset[/TD]
[TD]Currency[/TD]
[TD]in EUR[/TD]
[TD]Transacted Units[/TD]
[TD]Transacted Price (per unit)[/TD]
[TD]Fees[/TD]
[TD]Stock Split Ratio[/TD]
[TD]B&A[/TD]
[TD]Prev Row[/TD]
[TD]Next Row[/TD]
[TD]Previous Units[/TD]
[TD]Units[/TD]
[/TR]
[TR]
[TD]21.4.2006[/TD]
[TD]Buy[/TD]
[TD]Nordea[/TD]
[TD]NOKIA[/TD]
[TD]EUR[/TD]
[TD]1[/TD]
[TD]100,0[/TD]
[TD]18,380[/TD]
[TD="align: right"]2,00[/TD]
[TD]1,0[/TD]
[TD]NordeaNOKIA[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]0,0[/TD]
[TD]100,00[/TD]
[/TR]
[TR]
[TD]2.1.2007[/TD]
[TD]Buy[/TD]
[TD]Nordnet[/TD]
[TD]MSFT[/TD]
[TD]USD[/TD]
[TD]0,9[/TD]
[TD]20,00[/TD]
[TD]20,000[/TD]
[TD="align: right"]2,00[/TD]
[TD]1,0[/TD]
[TD]NordnetMSFT[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]0,0[/TD]
[TD]20,00[/TD]
[/TR]
[TR]
[TD]28.2.2007[/TD]
[TD]Sell[/TD]
[TD]Nordea[/TD]
[TD]NOKIA[/TD]
[TD]EUR[/TD]
[TD]1[/TD]
[TD]26,00[/TD]
[TD]12,350[/TD]
[TD="align: right"]2,00[/TD]
[TD]1,0[/TD]
[TD]NordeaNOKIA[/TD]
[TD]2[/TD]
[TD]7[/TD]
[TD]100,0[/TD]
[TD]74,00[/TD]
[/TR]
[TR]
[TD]5.3.2007[/TD]
[TD]Sell[/TD]
[TD]Nordnet[/TD]
[TD]MSFT[/TD]
[TD]USD[/TD]
[TD]0,9[/TD]
[TD]12,00[/TD]
[TD]7,060[/TD]
[TD="align: right"]2,00[/TD]
[TD]1,0[/TD]
[TD]NordnetMSFT[/TD]
[TD]3[/TD]
[TD]10[/TD]
[TD]20,0[/TD]
[TD]8,00[/TD]
[/TR]
[TR]
[TD]14.3.2007[/TD]
[TD]Buy[/TD]
[TD]Nordnet[/TD]
[TD]KO[/TD]
[TD]EUR[/TD]
[TD]0,95[/TD]
[TD]28,00[/TD]
[TD]5,400[/TD]
[TD="align: right"]2,00[/TD]
[TD]1,0[/TD]
[TD]NordnetKO[/TD]
[TD]0[/TD]
[TD]8[/TD]
[TD]0,0[/TD]
[TD]28,00[/TD]
[/TR]
[TR]
[TD]14.3.2007[/TD]
[TD]Sell[/TD]
[TD]Nordea[/TD]
[TD]NOKIA[/TD]
[TD]EUR[/TD]
[TD]1[/TD]
[TD]50,00[/TD]
[TD]10,010[/TD]
[TD="align: right"]2,00[/TD]
[TD]1,0[/TD]
[TD]NordeaNOKIA[/TD]
[TD]4[/TD]
[TD]11[/TD]
[TD]74,0[/TD]
[TD]24,00[/TD]
[/TR]
[TR]
[TD]6.7.2007[/TD]
[TD]Buy[/TD]
[TD]Nordnet[/TD]
[TD]KO[/TD]
[TD]USD[/TD]
[TD]0,9[/TD]
[TD]2,00[/TD]
[TD]40,000[/TD]
[TD="align: right"]4,00[/TD]
[TD]1,0[/TD]
[TD]NordnetKO[/TD]
[TD]6[/TD]
[TD]12[/TD]
[TD]28,0[/TD]
[TD]30,00[/TD]
[/TR]
[TR]
[TD]9.7.2007[/TD]
[TD]Buy[/TD]
[TD]Nordnet[/TD]
[TD]CCF[/TD]
[TD]EUR[/TD]
[TD]1[/TD]
[TD]3,00[/TD]
[TD]380,000[/TD]
[TD="align: right"]4,00[/TD]
[TD]1,0[/TD]
[TD]NordnetCCF[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0,0[/TD]
[TD]3,00[/TD]
[/TR]
[TR]
[TD]9.7.2007[/TD]
[TD]Sell[/TD]
[TD]Nordnet[/TD]
[TD]MSFT[/TD]
[TD]USD[/TD]
[TD]0,9[/TD]
[TD]8,00[/TD]
[TD]5,000[/TD]
[TD="align: right"]2,00[/TD]
[TD]1,0[/TD]
[TD]NordnetMSFT[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]8,0[/TD]
[TD]0,00[/TD]
[/TR]
[TR]
[TD]9.7.2007[/TD]
[TD]Sell[/TD]
[TD]Nordea[/TD]
[TD]NOKIA[/TD]
[TD]EUR[/TD]
[TD]1[/TD]
[TD]24,00[/TD]
[TD]14,000[/TD]
[TD="align: right"]2,00[/TD]
[TD]1,0[/TD]
[TD]NordeaNOKIA[/TD]
[TD]7[/TD]
[TD]0[/TD]
[TD]24,0[/TD]
[TD]0,00[/TD]
[/TR]
[TR]
[TD]9.7.2007[/TD]
[TD]Sell[/TD]
[TD]Nordnet[/TD]
[TD]KO[/TD]
[TD]USD[/TD]
[TD]0,9[/TD]
[TD]29,00[/TD]
[TD]42,000[/TD]
[TD="align: right"]2,00[/TD]
[TD]1,0[/TD]
[TD]NordnetKO[/TD]
[TD]8[/TD]
[TD]0[/TD]
[TD]30,0[/TD]
[TD]1,00[/TD]
[/TR]
</tbody>[/TABLE]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
-- removed inline image ---
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]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.

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[pasunmaa]

Here is a transaction-table that's not shown correctly in my previous reply:

[TABLE="class: grid, width: 745"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Type[/TD]
[TD]Broker[/TD]
[TD]Asset[/TD]
[TD]Currency[/TD]
[TD]in EUR[/TD]
[TD]Transacted Units[/TD]
[TD]Transacted Price (per unit)[/TD]
[TD]Fees[/TD]
[TD]Stock Split Ratio[/TD]
[TD]B&A[/TD]
[TD]Prev Row[/TD]
[TD]Next Row[/TD]
[TD]Previous Units[/TD]
[TD]Units[/TD]
[/TR]
[TR]
[TD]21.4.2006[/TD]
[TD]Buy[/TD]
[TD]Nordea[/TD]
[TD]NOKIA[/TD]
[TD]EUR[/TD]
[TD]1[/TD]
[TD]100,0[/TD]
[TD]18,380[/TD]
[TD="align: right"]2,00[/TD]
[TD]1,0[/TD]
[TD]NordeaNOKIA[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]0,0[/TD]
[TD]100,00[/TD]
[/TR]
[TR]
[TD]2.1.2007[/TD]
[TD]Buy[/TD]
[TD]Nordnet[/TD]
[TD]MSFT[/TD]
[TD]USD[/TD]
[TD]0,9[/TD]
[TD]20,00[/TD]
[TD]20,000[/TD]
[TD="align: right"]2,00[/TD]
[TD]1,0[/TD]
[TD]NordnetMSFT[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]0,0[/TD]
[TD]20,00[/TD]
[/TR]
[TR]
[TD]28.2.2007[/TD]
[TD]Sell[/TD]
[TD]Nordea[/TD]
[TD]NOKIA[/TD]
[TD]EUR[/TD]
[TD]1[/TD]
[TD]26,00[/TD]
[TD]12,350[/TD]
[TD="align: right"]2,00[/TD]
[TD]1,0[/TD]
[TD]NordeaNOKIA[/TD]
[TD]2[/TD]
[TD]7[/TD]
[TD]100,0[/TD]
[TD]74,00[/TD]
[/TR]
[TR]
[TD]5.3.2007[/TD]
[TD]Sell[/TD]
[TD]Nordnet[/TD]
[TD]MSFT[/TD]
[TD]USD[/TD]
[TD]0,9[/TD]
[TD]12,00[/TD]
[TD]7,060[/TD]
[TD="align: right"]2,00[/TD]
[TD]1,0[/TD]
[TD]NordnetMSFT[/TD]
[TD]3[/TD]
[TD]10[/TD]
[TD]20,0[/TD]
[TD]8,00[/TD]
[/TR]
[TR]
[TD]14.3.2007[/TD]
[TD]Buy[/TD]
[TD]Nordnet[/TD]
[TD]KO[/TD]
[TD]EUR[/TD]
[TD]0,95[/TD]
[TD]28,00[/TD]
[TD]5,400[/TD]
[TD="align: right"]2,00[/TD]
[TD]1,0[/TD]
[TD]NordnetKO[/TD]
[TD]0[/TD]
[TD]8[/TD]
[TD]0,0[/TD]
[TD]28,00[/TD]
[/TR]
[TR]
[TD]14.3.2007[/TD]
[TD]Sell[/TD]
[TD]Nordea[/TD]
[TD]NOKIA[/TD]
[TD]EUR[/TD]
[TD]1[/TD]
[TD]50,00[/TD]
[TD]10,010[/TD]
[TD="align: right"]2,00[/TD]
[TD]1,0[/TD]
[TD]NordeaNOKIA[/TD]
[TD]4[/TD]
[TD]11[/TD]
[TD]74,0[/TD]
[TD]24,00[/TD]
[/TR]
[TR]
[TD]6.7.2007[/TD]
[TD]Buy[/TD]
[TD]Nordnet[/TD]
[TD]KO[/TD]
[TD]USD[/TD]
[TD]0,9[/TD]
[TD]2,00[/TD]
[TD]40,000[/TD]
[TD="align: right"]4,00[/TD]
[TD]1,0[/TD]
[TD]NordnetKO[/TD]
[TD]6[/TD]
[TD]12[/TD]
[TD]28,0[/TD]
[TD]30,00[/TD]
[/TR]
[TR]
[TD]9.7.2007[/TD]
[TD]Buy[/TD]
[TD]Nordnet[/TD]
[TD]CCF[/TD]
[TD]EUR[/TD]
[TD]1[/TD]
[TD]3,00[/TD]
[TD]380,000[/TD]
[TD="align: right"]4,00[/TD]
[TD]1,0[/TD]
[TD]NordnetCCF[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0,0[/TD]
[TD]3,00[/TD]
[/TR]
[TR]
[TD]9.7.2007[/TD]
[TD]Sell[/TD]
[TD]Nordnet[/TD]
[TD]MSFT[/TD]
[TD]USD[/TD]
[TD]0,9[/TD]
[TD]8,00[/TD]
[TD]5,000[/TD]
[TD="align: right"]2,00[/TD]
[TD]1,0[/TD]
[TD]NordnetMSFT[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]8,0[/TD]
[TD]0,00[/TD]
[/TR]
[TR]
[TD]9.7.2007[/TD]
[TD]Sell[/TD]
[TD]Nordea[/TD]
[TD]NOKIA[/TD]
[TD]EUR[/TD]
[TD]1[/TD]
[TD]24,00[/TD]
[TD]14,000[/TD]
[TD="align: right"]2,00[/TD]
[TD]1,0[/TD]
[TD]NordeaNOKIA[/TD]
[TD]7[/TD]
[TD]0[/TD]
[TD]24,0[/TD]
[TD]0,00[/TD]
[/TR]
[TR]
[TD]9.7.2007[/TD]
[TD]Sell[/TD]
[TD]Nordnet[/TD]
[TD]KO[/TD]
[TD]USD[/TD]
[TD]0,9[/TD]
[TD]29,00[/TD]
[TD]42,000[/TD]
[TD="align: right"]2,00[/TD]
[TD]1,0[/TD]
[TD]NordnetKO[/TD]
[TD]8[/TD]
[TD]0[/TD]
[TD]30,0[/TD]
[TD]1,00[/TD]
[/TR]
</tbody>[/TABLE]

 

[XOR LX]

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

 

[pasunmaa]

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?

 

[XOR LX]

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

 

[pasunmaa]

Yes and Yes, on K4.

 

[XOR LX]

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

 

[XOR LX]

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

 

[XOR LX]

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.

Apologies. That should have been "horizontal".

Regards

 

[pasunmaa]

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?