maxifs, minifs, textjoin not accepted by appsheet in ms365 excel, but accepted in google sheets

[Chris_Bean1]

Hi folks

I have some columns in a ms 365 Excel where I want to put maxifs, minifs and textjoin formulas, and appsheet is saying that these are not supported. However, I have another set of apps in google sheets where the following works fine and contains a textjoin (I have copied this from the 'Spreadsheet formula' part of the column definition):

iferror(join(", ",unique(transpose(split(arrayformula(textjoin(", ",1, rept('register'!C4:C4,('Register'!C8:C8=RC4)+0))),", ",0,1)))),"None found in sheet") 

I would use appsheet formulas, but these don't stay in the cells like excel formulas do, and I want the column data to update based on data changes that will occur outside appsheet, in the excel itself. 

Any reasons why textjoin would work ok in google with appsheet, but not in excel 365? 

Thanks

Chris

[Firdaus_AG]

Not every function in Google Sheets available in Office 365.

[Joseph_Seddik]

If you really want to do it like this, then keep the sheet with unsupported formulas outside the app, and create another table inside the app based on a sheet that copies the values (not the formulas) from the original sheet, using a function like IMPORTRANGE().

[Chris_Bean1]

Hi there, the maxifs, minifs and textjoin do work in Excel 365, it's just that appsheet doesn't accept them. Is there an if function would be acceptable to appsheet which would do the same thing as max/minifs?

Thanks in advance

Chris

[Steve]

This might guide you to a solution:

Re: FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), R... - Google Cloud Community

[Chris_Bean1]

Thanks - having looked at it though, these take a snapshot value of the data at the time the row is created. I need the column value to update later as data is added to another table, as it would if a maxifs formula were in the spreadsheet - in this example, I want the maxifs to find the latest inspection carried out at a store; inspections will be carried out in the future, and the maxrow doesn't hold in excel as a spreadsheet formula as far as I can tell. Am I wrong? 

[Steve]

You are wrong. 

[Chris_Bean1]

Excellent.

Get Outlook for Android

[Chris_Bean1 · Accepted solution]

Ok after an age of messing about, I did this: I used a  max formula in the spreadsheet then wrapped an if inside it: 

=(MAX(IF('table1'!F:F='maxrow test'!A2,'table1'!C:C)))

I then regen'd the column, removed the 'array formula' text in 'auto compute' and did a couple of tests and it seems to be working. 

Cheers

Chris

[Chris_Bean1]

Hi, I tried this with 

MAX(
SELECT(
Store inspections[date],
[store number]=[_THISROW].[store number], true))

which worked on initial 'Add' when adding a row, but this did not then make the excel cell look for updates, which is as I suspected in my post above. The Excel-based solution I posted below works (so far). 

Cheers

Chris