#1
|
|||
|
|||
Google sheet formula convert to excel
Hello,
I have a formula in google sheet but when you download as excel the formula is changed as below. Google sheet - =((QUERY(transpose(I413:T413),"Select Col1 where Col1 is not null limit 1",0))/(ArrayFormula(iferror(lookup(1,I413:T413/I413:T413,I413:T413)))))^(1/(counta(I413:T413)-1))-1 Convereted to below in excel =IFERROR(@__xludf.DUMMYFUNCTION("((QUERY(transpose (I413:T413),""Select Col1 where Col1 is not null limit 1"",0))/(ArrayFormula(iferror(lookup(1,I413:T413/I413:T413,I413:T413)))))^(1/(counta(I413:T413)-1))-1"),0.000510855849876756) Need your help in fixing this issue. |
#2
|
||||
|
||||
Hope you've got the FILTER function available (otherwise a rethink).
This one might do (it treats negative numbers at the left/right of the range differently; Excel returns the imaginary number, Google Sheets returns an error, also it might give different results if the there are exact zeroes in the range) Code:
=(INDEX(FILTER(I413:T413,ISNUMBER(I413:T413)),1,1)/LOOKUP(1,I413:T413/I413:T413,I413:T413))^(1/(COUNTA(I413:T413)-1))-1 Consider also using built-in function RRI: Code:
=RRI(COUNTA(I413:T413)-1,LOOKUP(1,I413:T413/I413:T413,I413:T413),INDEX(FILTER(I413:T413,ISNUMBER(I413:T413)),1,1)) ps. Yes, it's always a good idea to say thanks, even better to do so before you discover you need more help. Last edited by p45cal; 12-15-2021 at 09:34 AM. |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
a formula for this excel sheet: | emvella | Excel | 1 | 09-14-2019 05:57 PM |
Update Excel worksheet from Google sheet | tasneemj | Excel | 0 | 02-11-2018 12:17 PM |
Convert From Google to Microsoft for everything | MarkNicholson100 | Office | 0 | 02-23-2017 01:41 AM |
Error in exporting Google Sheet to Excel | cmcmeekin | Excel | 2 | 08-03-2016 11:32 AM |
Excel formula from looking the base date and convert into desire result | PRADEEPB270 | Excel | 1 | 02-17-2013 03:11 AM |