+ Reply to Thread
Results 1 to 7 of 7

How can I convert this google sheet formula to excel?

  1. #1
    Registered User
    Join Date
    03-28-2023
    Location
    Hong Kong
    MS-Off Ver
    2016
    Posts
    3

    How can I convert this google sheet formula to excel?

    Hi all,

    I have a Google Sheet file that I'd like to bring offline to Excel. I am using Excel 2016.

    There are a few columns which show up as an error. I'm aware that (arrayformula... doesn't work in Excel 2016. I've tried to shorten the formulas, but what I have tried is either incorrect or showing up as errors.

    I'm unable to include the Google sheet link here (first time posting), but the formula I am having issues to convert is as follows:

    Summary Sheet:
    Columns: J and K

    =iferror(INDEX(arrayformula(filter(Transactions!O:O,Transactions!C:C<>"",row(Transactions!C:C)=max(if(Transactions!C:C=B2,row(Transactions!C:C),0)))) ,1),0)

    Columns: O and P

    =arrayformula(sumproduct(Transactions_OSV!C:C=B2,Transactions_OSV!B:B="Sell",Transactions_OSV!P:P))

    Transactions Sheet:
    Columns I and L

    =iferror(if(row()<>2,INDEX(arrayformula(filter($J1:$J$2,$C1:$C$2<>"",row($C1:$C$2)=max(if($C1:$C$2=C2,row($C1:$C$2),0)))) ,1),0),0)


    The excel workbook, is attached. Apologize in advance if the workbook is a bit laggy. I think its because of the dummy functions in the excel...


    Any help is much appreciated!!
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: How can I convert this google sheet formula to excel?

    I'm guessing the problem is not going to be the ARRAYFORMULA part, it will be that you don't have the FILTER function in Excel 2016. A formula to filter data, pre-365, is much more complicated.

    That's probably why there have been no replies. I suspect that Glenn Kennedy might be able to work some magic with FILTERXML but that's beyond me.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    03-28-2023
    Location
    Hong Kong
    MS-Off Ver
    2016
    Posts
    3

    Re: How can I convert this google sheet formula to excel?

    I see. Hope he replies! If not, I guess I'll have to figure out some alternatives.

    Thanks for the reply!

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: How can I convert this google sheet formula to excel?

    I have asked for others to help but, specifically, Glenn.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,895

    Re: How can I convert this google sheet formula to excel?

    Argh!!!

    Without any explanation of what it is meant to be doing... and manually calculated expected answers, I am lost.

    So... please explain how the values are calculated and add expected answers.

    The SUMPRODUCT formulae have been fixed. See file. NEVER use SP with whole column references, unless you have 1,000,000+ rows. It will get SLOWWWW.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  6. #6
    Registered User
    Join Date
    03-28-2023
    Location
    Hong Kong
    MS-Off Ver
    2016
    Posts
    3

    Re: How can I convert this google sheet formula to excel?

    Hi Glenn,

    The user is only meant to input data into cells highlighted in yellow, and blue cells do all the calculations.

    The file is meant to be a stock portfolio tracker, where all transactions/trades are input into the Transactions sheet. Every transaction is a new line, followed in chronological order. Summary sheet is meant to be an overview of the portfolio.

    Didn't realize when Excel repaired the document, some formulas were changed to values on the Transactions sheet. That's been fixed.

    Ill try my best to explain how the values are calculated.

    Transactions Sheet
    Column I (Previous Units)

    I think the formula filters through Column J, (cumulative units), matching it with the stock in Column C, to the cell in the row number. if yes, then it returns the max value up to the row in question. If not, then return a 0. I think I might be missing something here though...

    As for Column J, it calculates the number of shares of the stock in Column C, depending on 'Buy' or 'Sell' in Column B.


    Column L (Previous Cost)
    Same as above, Column I, but filters through Column O (cumulative cost).

    Summary Sheet

    Column J
    Meant to calculate the number of shares of the stock code listed in Column C of this sheet.

    Column K
    Meant to show the Cost of shares by filtering through Transactions Sheet, with the most recent Column O, matched with Column C.


    As you have already fixed Column O and P, I'll skip those. Thank you!

    I hope this helps to understand it better... if you need more clarification, please let me know!


    I'm breaking the link to the sheet into 3 parts of text, I'm not sure if that is allowed, but I think it'll help clear things up!

    link to google sheets:

    docs.google
    .com/spreadsheets/d/
    1MQoSG0rdHxpfgOHE5q6zCVfTRqLIiIuEq7-T8U30PY8/edit?usp=sharing

    Test Stock Portfolio TrackerGKv2.xlsx

  7. #7
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: How can I convert this google sheet formula to excel?

    It looks like the transactions sheet is in date order, in which case:
    I2: =IF($C2="",0,IFERROR(LOOKUP(2,1/($C$1:$C1=$C2),J$1:J1),0))
    and copy down
    L2: =IF($C2="",0,IFERROR(LOOKUP(2,1/($C$1:$C1=$C2),O$1:O1),0))
    and copy down.

    Your formulas on the summary sheet refer to sheet Transactions_OSV which doesn't exist in the workbook you posted here. If that's the Transactions sheet, then I think the formulas are similar to the above.
    Rory

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. convert .xlsm file to google sheet document using vba
    By kriskris12 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-23-2022, 10:53 AM
  2. Excel formula is not working in Google Sheet # I need Help
    By rashadul88 in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 04-16-2021, 09:16 PM
  3. Google sheet: Help to attachment file from Google Drive and send email fill in Google Form
    By sbv1986 in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 03-01-2021, 10:47 PM
  4. Convert short VBA code to google sheet Json
    By tagazou in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-14-2020, 07:44 AM
  5. Convert Excel Macro To Google Sheet Macro
    By jo15765 in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 05-04-2020, 03:15 PM
  6. Convert Google sheet into Excel
    By Sailaja A in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-29-2015, 03:07 AM
  7. Replies: 0
    Last Post: 02-14-2013, 06:56 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1