Problems working with arrays

EVE Technology and Research Center EVE x Microsoft Excel Add-in
1

I am trying to make a spreadsheet that tells me the buy orders percentile price for the materials required to build an item.

image
image1130×128 6.25 KB

B1: I write down the object that I want to look at.
B2: INVENTORYSEARCH(CONCAT(B1," Blueprint"),TRUE) retrieves the blueprint for the object I want to look at.
B3: Retrieves the blueprint explosion for the object I want to look at.
B4: SEARCHREGION(“The Forge”, TRUE) is used later to get the system ID of Jita.

D2: B3.materials gives me all the materials required to build the object.
E2: D2#.type.id unfolds the D2 array and gives me the ID for each material.
F2: MARKET_ORDERS_STATS($B$4.id,E2).buy.percentile gives me the percentile price for the buy orders of Isogen.

The problem comes when I try to get this data for every material. I tried multiple approaches and nothing seems to work.

  • Failed approach 1 (#FIELD!): Attempted passing the unfolded list to the function
    =EVEONLINE.MARKET_ORDERS_STATS($B$4.id,E2#).buy.percentile

  • Failed approach 2 (#CALC!): Attempted using BYROW with LAMBDA to get the Statistics for in The Forge object
    =BYROW(E2#,LAMBDA(id,EVEONLINE.MARKET_ORDERS_STATS($B$4.id,id)))

I’ve been watching tutorials, but they use autofill with the formulas manually, I want it to work automatically without having to be readjusting stuff all around the spreadsheets for every item I want to check…

Any suggestions?

2

Only real approach I’ve seen is sadly just IF wrapping where the price formula is, so it doesn’t run on empty cells. Since as you see the LAMBDA doesn’t work with custom functions :frowning:

I’ll try to check the excel addin issues if the lambda issue has any news. But I wouldn’t hold my breath for it.