Forum Discussion
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
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 😎 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)
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.
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 😎 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)
- 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
- 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 . 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 )
