SOLVED

# 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

5 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 (New Contributor)
Solution

# Re: Converting google sheets formula to excel

I agree with @Hans Vogelaar 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 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 )``````