Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

SOLVED
Home
#
Converting google sheets formula to excel

- Home
- Microsoft 365
- Excel
- Converting google sheets formula to excel

- Subscribe to RSS Feed
- Mark Discussion as New
- Mark Discussion as Read
- Pin this Discussion for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 11 2022 05:29 AM

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

Labels:

7 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 11 2022 05:46 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 11 2022 05:49 AM

here is a small workaround:

Convert Google Sheets spreadsheets to Excel format:

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.

Hope I could help you with these information .

Convert Google Sheets spreadsheets to Excel format:

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.

Hope I could help you with these information .

best response confirmed by
4ll5op* (Copper Contributor)*

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 11 2022 06:13 AM - edited Aug 11 2022 06:16 AM

Solution

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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 11 2022 06:26 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 11 2022 06:50 AM

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 )
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 29 2024 08:20 AM

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.

=GOOGLEFINANCE(A2,"price") / index(GOOGLEFINANCE(A2,"price", datevalue(today()-365) ) , 2,2) - 1

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.

=IFERROR(@__xludf.DUMMYFUNCTION("IFERROR(ROUND((GOOGLEFINANCE(A2, ""price"")/index(GOOGLEFINANCE(A2,""open"",datevalue(today()-365)), 2,2)- 1)*100,2))"),262.86)

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

Thank you

Derik

@ links to members

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 29 2024 08:49 AM

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)*

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 11 2022 06:13 AM - edited Aug 11 2022 06:16 AM

Solution

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)