+ Reply to Thread
Results 1 to 10 of 10

Converting google sheets formula to excel

  1. #1
    Registered User
    Join Date
    04-02-2016
    Location
    Sydney
    MS-Off Ver
    2013
    Posts
    6

    Converting google sheets formula to excel

    I am trying to convert a formula from a google sheets template to excel. Unfortunately the author has not had the time nor the knowledge of how to achieve this hence I am seeking anyone's expertise here. The formula is :

    =iferror(if(row()<>2,INDEX(arrayformula(filter($E$2:$E4,$C$2:$C4<>"",row($C$2:$C4)=max(if($C$2:$C4=C5,row($C$2:$C4),0)))) ,1),0),0)

    Assume formula is on cell E5

    B C D E F
    Buy AAPL 1000 0 1000
    Buy GOOG 300 0 300
    Sell AAPL 200 1000 800
    Buy GOOG 200 300 500
    Sell GOOG 100 500 400

    E Previous Units
    F Cumulative Units

    In effect what the formula seeks to achieve is calculate the sum of the holdings. ie result would show the number of APPL shares which is 800 shares and GOOG shares would be 400. It is easy to write the formula for column F however it is difficult to write the formula collating the sum of previous units in column E.

    How would I approach this to achieve the same outcome in excel?
    Attached Files Attached Files
    Last edited by coldeskimo; 04-05-2016 at 04:20 AM.

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,153

    Re: Converting google sheets formula to excel

    If F is easy I left it for you
    For E2 I can suggest array formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Attached Files Attached Files
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    04-02-2016
    Location
    Sydney
    MS-Off Ver
    2013
    Posts
    6

    Re: Converting google sheets formula to excel

    Kaper, I believe you have found the formula successfully. I will try testing it within the re-designed excel sheet and update tomorrow.

    Thanks

  4. #4
    Registered User
    Join Date
    04-02-2016
    Location
    Sydney
    MS-Off Ver
    2013
    Posts
    6

    Re: Converting google sheets formula to excel

    Kaper, it worked. Thank you so much.

    One further question which will also involve an array formula with index function. (I need to really study these as coming across them too often)

    I need a formula to summarise the holdings as per attached sheet.

    I assume a similar formula will be used but can not tailor it correctly.

    Please let me know if you can assist with this too.

    Thanks
    Attached Files Attached Files

  5. #5
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,153

    Re: Converting google sheets formula to excel

    it's basically the same array formula.
    in sheet2 B3:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and copy down to B4
    just a reminder - array formula.


    BTW. My proposition for F2 in sheet1 would be regular formula (not array):
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and what is yours, as you not disclose it in the attachment, while may be somebody using http://www.excelforum.com/search.php will find this thread and may be for her/him it will not be "It is easy to write the formula for column F"?

  6. #6
    Registered User
    Join Date
    04-02-2016
    Location
    Sydney
    MS-Off Ver
    2013
    Posts
    6

    Re: Converting google sheets formula to excel

    Sorry my formula for cell F is:

    =IF(B2="Buy",E2+D2,IF(B2="Sell",E2-D2,IF(OR(B2="Div",B2="Fee"),E2)))

    Yes it is not an array as I need to just add or subtract the holdings as they are presented.

    Thank you Kapar for qualifying the variation of the array. I will try it now.

  7. #7
    Registered User
    Join Date
    04-02-2016
    Location
    Sydney
    MS-Off Ver
    2013
    Posts
    6

    Re: Converting google sheets formula to excel

    Kapar,

    What is the reference to ROW C1:C10 in the last formula you made =IFERROR(INDEX(Sheet1!F$1:F$10,LARGE(IF(Sheet1!C$1:C$10=A3,ROW(C$1:C$10),""),1)),0)

    This refers to a blank space on the spreadsheet tab2. Result would be blank as is the false argument at the end which is "". Is this intentional?

  8. #8
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,153

    Re: Converting google sheets formula to excel

    Yes it's intentional
    and nope, result wouldn't be blank.
    C$1:C$10 can be empty, does not matter
    but
    ROW(C$1:C$10)
    will return an array of row numbers 1,2,3...10 (despite of cells contents)
    you could use
    ROW(A$1:A$10)
    or
    ROW($1:$10)
    as well.

  9. #9
    Registered User
    Join Date
    04-02-2016
    Location
    Sydney
    MS-Off Ver
    2013
    Posts
    6

    Re: Converting google sheets formula to excel

    Thanks Kapar for your assistance. I have it working now.

  10. #10
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,153

    Re: Converting google sheets formula to excel

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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. converting excel macro into a google docs script??
    By scottatbuckman in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-11-2016, 12:47 PM
  2. email row contents based on cell values (google sheets populated by google forms)
    By reedg in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 01-13-2016, 02:55 PM
  3. [SOLVED] Converting Google Sheets document with query to Excel
    By playmate in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 08-04-2015, 10:18 PM
  4. Grab next same value in Excel/Google Sheets for RANK/MATCH formula
    By jpsear in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-17-2015, 07:52 PM
  5. Converting spreadsheets from Microsoft Excel to Google Docs
    By hammerb in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 4
    Last Post: 10-08-2013, 02:32 PM
  6. Basic Sum Formula in Google Sheets
    By lesoies in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 7
    Last Post: 08-27-2013, 04:05 PM
  7. Replies: 4
    Last Post: 08-05-2010, 01:09 PM

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