Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-14-2021, 05:04 PM
OriginFarms OriginFarms is offline Google sheet formula convert to excel Windows 10 Google sheet formula convert to excel Office 2019
Novice
Google sheet formula convert to excel
 
Join Date: Oct 2021
Posts: 4
OriginFarms is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 12-15-2021, 05:19 AM
p45cal's Avatar
p45cal p45cal is offline Google sheet formula convert to excel Windows 10 Google sheet formula convert to excel Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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
A link to the Google sheet might help.


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.
Reply With Quote
Reply

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
Google sheet formula convert to excel 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

Other Forums: Access Forums

All times are GMT -7. The time now is 02:33 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft