Forum Discussion
Issue with converting google spreadsheet into excel
Hello
I have been working on google spreadsheets and recently had to convert it into an excel file; however the formulas for certain columns cudnt carry into excel as expected.
Attached below is an excel document and my concern is on sheet - "Stock Summary USD".
The units column 'L' seems to have got distorted and the formula is not functioning as intended. im assuming its because of a function called 'array formula' from google spreadsheet. Can anybody please have a look and guide me on how to resolve this issue. feel free to edit the excel sheet, if the formula is functioning properly the value of L13 in the "Stock Summary USD" sheet shud be 10.
Thankyou for all your assistance.
I've never worked with google sheets, but it appears to me that excel didn't recognize the existing formula, so put the existing formula in quotes and wrapped it with an iferror function to return the last calculated value.
If you have office 365 and excel's version of the filter function, then I think your formula in L2 would be:
=iferror(INDEX(filter('Transactions USD'!J:J,('Transactions USD'!C:C<>"")*(row('Transactions USD'!C:C)=max(if('Transactions USD'!C:C=B2,row('Transactions USD'!C:C),0)))),1),0)
But, you should explain further the purpose of the formula as there may be a better way to do it. For the formula in 'Stock Summary USD'!L2, It appears you are trying to return the last entry from 'Transactions USD' column J where the stock name in column C equals B2? If so, and you have office 365, then you might take a look at excel's xlookup function as it can perform a lookup from the bottom up.
https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929
Otherwise, you could try:
=LOOKUP(2,1/('Transactions USD'!C:C=B2),'Transactions USD'!J:J)
6 Replies
mohammed3786 same issue with me when I download a file of https://zucchero.com.au/ and then convert into excel but with the help of you people solved my problem. thanks Everyone
Also, if you're staying with excel, you should consider converting your data tables to structured tables (select your table, then click on the Insert tab and click the table button). When you click on the structured table, a Table Tools tab will appear on the ribbon.
Then, when you write your formulas you can just select the ranges that have data and excel will use a structured table reference in the formula. You won't need to use entire column references in your formula - as your table grows, your formulas will automatically include the new rows when you use structured tables. This should help a lot with calculation speed.I've never worked with google sheets, but it appears to me that excel didn't recognize the existing formula, so put the existing formula in quotes and wrapped it with an iferror function to return the last calculated value.
If you have office 365 and excel's version of the filter function, then I think your formula in L2 would be:
=iferror(INDEX(filter('Transactions USD'!J:J,('Transactions USD'!C:C<>"")*(row('Transactions USD'!C:C)=max(if('Transactions USD'!C:C=B2,row('Transactions USD'!C:C),0)))),1),0)
But, you should explain further the purpose of the formula as there may be a better way to do it. For the formula in 'Stock Summary USD'!L2, It appears you are trying to return the last entry from 'Transactions USD' column J where the stock name in column C equals B2? If so, and you have office 365, then you might take a look at excel's xlookup function as it can perform a lookup from the bottom up.
https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929
Otherwise, you could try:
=LOOKUP(2,1/('Transactions USD'!C:C=B2),'Transactions USD'!J:J)- I apologize for the delayed response. i couldn't reply as I was traveling.
yes, you have understood my requirement perfectly and it looks like the lookup formula mentioned above works perfectly as needed
thankyou very much for your response.
mohammed3786 Don't know anything about Google Sheets, but when I open your file, I find this in L2 on the Summary sheet:
=IFERROR(@__xludf.DUMMYFUNCTION("iferror(INDEX(arrayformula(filter('Transactions USD'!J:J,'Transactions USD'!C:C<>"""",row('Transactions USD'!C:C)=max(if('Transactions USD'!C:C=B2,row('Transactions USD'!C:C),0)))) ,1),0)"),6.5)It seems you have used a user defined function in GS. Obviously, it is not recognised by Excel. and "array formula" isn't something Excel would recognise either. In addition to that, the expressions in the "filter" function aren't compatible with those for Excel either. So, what it is that you want to calculate in column L?
- Thankyou for your reply Riny, JMB17 has got my issue resolved.
