View Single Post
 
Old 12-15-2021, 05:19 AM
p45cal's Avatar
p45cal p45cal is offline Windows 10 Office 2019
Expert
 
Join Date: Apr 2014
Posts: 871
p45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond repute
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