SOLVED

# Converting google sheets formula to excel

Copper Contributor

# Converting google sheets formula to excel

Hi i have a formula which works fine in google sheets but im wanting to move over to excel but the formula doesn't work. im sure it may need a slight adjustment but i cant figure it out.

this is the formula in google:

=IFERROR(IF(VLOOKUP(B4,FILTER({\$B\$3:\$B3,\$I\$3:\$J3},\$B\$3:\$B3=B4),2,1)>0,ROUNDDOWN((SUM(\$I\$3:\$I3))*1%,1),IF(VLOOKUP(B4,FILTER({\$B\$3:\$B3,\$I\$3:\$I3},\$B\$3:\$B3=B4),2,2)<0,VLOOKUP(B4,FILTER(\$B\$3:\$I3,\$B\$3:\$B3=B4),8,1)*-2,IF(VLOOKUP(B4,FILTER({\$B\$3:\$B3,\$I\$3:\$I3},\$B\$3:\$B3=B4),2,2)=0,VLOOKUP(B4,FILTER(\$B\$3:\$I3,\$B\$3:\$B3=B4),2,1)*1))))

Thanks
Stu

7 Replies

# Re: Converting google sheets formula to excel

That's a long formula; it's hard to visualize what it does. Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.

# Re: Converting google sheets formula to excel

here is a small workaround:
In the Sheets app, click → File and then → Download as.
Now you can choose between *.xlsx, *.ods and other file formats and select a storage location for them.
This creates a copy of the file in Excel format, the original Google Sheets file remains unchanged. You can then take the formulas in Excel format from the saved Excel file.

best response confirmed by 4ll5op (Copper Contributor)
Solution

# Re: Converting google sheets formula to excel

I agree with @HansVogelaar that a sample sheet would be helpful but I do see a couple things:

``````=IFERROR(
IF(VLOOKUP(B4,FILTER({\$B\$3:\$B3,\$I\$3:\$J3},\$B\$3:\$B3=B4),2,1)>0,
ROUNDDOWN((SUM(\$I\$3:\$I3))*1%,1),
IF(VLOOKUP(B4,FILTER({\$B\$3:\$B3,\$I\$3:\$I3},\$B\$3:\$B3=B4),2,2)<0,
VLOOKUP(B4,FILTER(\$B\$3:\$I3,\$B\$3:\$B3=B4),8,1)*-2,
IF(VLOOKUP(B4,FILTER({\$B\$3:\$B3,\$I\$3:\$I3},\$B\$3:\$B3=B4),2,2)=0,
VLOOKUP(B4,FILTER(\$B\$3:\$I3,\$B\$3:\$B3=B4),2,1)*1
)
)
)
)``````

so I think the problem is the FILTER({...} ...

you can't create a set of ranges like that.  Just filter the entire range and pick the column accordingly.  I also tried to simplify the whole equation by checking the sign of that result instead of <0, =0 and >0:

``````=IFERROR(
CHOOSE(SIGN(VLOOKUP(B4,FILTER(\$B\$3:\$J3,\$B\$3:\$B3=B4),8,1))+2,
VLOOKUP(B4,FILTER(\$B\$3:\$I3,\$B\$3:\$B3=B4),8,1)*-2,
VLOOKUP(B4,FILTER(\$B\$3:\$I3,\$B\$3:\$B3=B4),2,1)*1,
ROUNDDOWN((SUM(\$I\$3:\$I3))*1%,1)
),
"error"
)``````

that said there are a couple other things I question:

a) in the VLOOKUPs the last parameter was a 2 in a couple cases when that is really a true/false but I assume any value will come back the same as true

b) in the <0 case you are returning a lookup from column I (3rd parameter is 8) but the =0 case you are returning from column C (3rd parameter is 2), is that supposed to be an 8 also?

c) you have an IFERROR()  but I didn't see it being used so I added a "error" statement because excel doesn't allow nothing (but apparently sheets does)

# Re: Converting google sheets formula to excel

Ok, this formula seems to fix it so thank you, 3rd paramter is meant to be different for =0 & >0

i will continue to enter data and test the formula but i think you have cracked it from what ive tested so far.

Thank you

# Re: Converting google sheets formula to excel

That could be

``````=IFERROR(
IF( VLOOKUP( B4, FILTER( HSTACK( \$B\$3:\$B3, \$I\$3:\$J3 ), \$B\$3:\$B3=B4 ), 2, 1)>0,
ROUNDDOWN( SUM(\$I\$3:\$I3)*1%, 1),
IF( VLOOKUP( B4,FILTER( HSTACK( \$B\$3:\$B3, \$I\$3:\$I3 ),\$B\$3:\$B3=B4 ), 2, 2)<0,
VLOOKUP( B4,FILTER( \$B\$3:\$I3, \$B\$3:\$B3=B4), 8, 1)*-2,
IF( VLOOKUP( B4,FILTER( HSTACK( \$B\$3:\$B3, \$I\$3:\$I3 ), \$B\$3:\$B3=B4 ), 2, 2)=0,
VLOOKUP( B4,FILTER( \$B\$3:\$I3, \$B\$3:\$B3=B4 ), 2, 1)*1))),
0 )``````

# Re: Converting google sheets formula to excel

Good Day,

Looking for advise on changing a =googlefinance formula to work in excel with out the dummy function conversion.

The google finance formula is for a 52 week performance of a stock that will only use one cell.

it has A2 to refer to the ticker in A2 cell,so i can use it in a table. "A2 is currently the ticker COIN"

When I save as an excel file it shows the following and is no longer a percentage value.

My understanding of excel VBA is a novice by I am getting a little better day by day.

Thank you

Derik

# Re: Converting google sheets formula to excel

As variant

``````=TAKE( STOCKHISTORY([@Ticker],TODAY()-10,TODAY(),0,0,1 ), 1) /
TAKE( STOCKHISTORY([@Ticker],TODAY()-375,TODAY()-365,0,0,1 ), 1)``````

1 best response

Accepted Solutions
best response confirmed by 4ll5op (Copper Contributor)
Solution

# Re: Converting google sheets formula to excel

I agree with @HansVogelaar that a sample sheet would be helpful but I do see a couple things:

``````=IFERROR(
IF(VLOOKUP(B4,FILTER({\$B\$3:\$B3,\$I\$3:\$J3},\$B\$3:\$B3=B4),2,1)>0,
ROUNDDOWN((SUM(\$I\$3:\$I3))*1%,1),
IF(VLOOKUP(B4,FILTER({\$B\$3:\$B3,\$I\$3:\$I3},\$B\$3:\$B3=B4),2,2)<0,
VLOOKUP(B4,FILTER(\$B\$3:\$I3,\$B\$3:\$B3=B4),8,1)*-2,
IF(VLOOKUP(B4,FILTER({\$B\$3:\$B3,\$I\$3:\$I3},\$B\$3:\$B3=B4),2,2)=0,
VLOOKUP(B4,FILTER(\$B\$3:\$I3,\$B\$3:\$B3=B4),2,1)*1
)
)
)
)``````

so I think the problem is the FILTER({...} ...

you can't create a set of ranges like that.  Just filter the entire range and pick the column accordingly.  I also tried to simplify the whole equation by checking the sign of that result instead of <0, =0 and >0:

``````=IFERROR(
CHOOSE(SIGN(VLOOKUP(B4,FILTER(\$B\$3:\$J3,\$B\$3:\$B3=B4),8,1))+2,
VLOOKUP(B4,FILTER(\$B\$3:\$I3,\$B\$3:\$B3=B4),8,1)*-2,
VLOOKUP(B4,FILTER(\$B\$3:\$I3,\$B\$3:\$B3=B4),2,1)*1,
ROUNDDOWN((SUM(\$I\$3:\$I3))*1%,1)
),
"error"
)``````

that said there are a couple other things I question:

a) in the VLOOKUPs the last parameter was a 2 in a couple cases when that is really a true/false but I assume any value will come back the same as true

b) in the <0 case you are returning a lookup from column I (3rd parameter is 8) but the =0 case you are returning from column C (3rd parameter is 2), is that supposed to be an 8 also?

c) you have an IFERROR()  but I didn't see it being used so I added a "error" statement because excel doesn't allow nothing (but apparently sheets does)