# Converting google sheets formula to excel

1. ## 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
Sell AAPL 200 1000 800
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?  Register To Reply

2. ## Re: Converting google sheets formula to excel

If F is easy I left it for you
For E2 I can suggest array formula
Formula:  `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.  Register To Reply

3. ## 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  Register To Reply

4. ## 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  Register To Reply

5. ## Re: Converting google sheets formula to excel

it's basically the same array formula.
in sheet2 B3:
Formula:  `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:  `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"?  Register To Reply

6. ## Re: Converting google sheets formula to excel

Sorry my formula for cell F is:

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.  Register To Reply

7. ## 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?  Register To Reply

8. ## 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.  Register To Reply

9. ## Re: Converting google sheets formula to excel

Thanks Kapar for your assistance. I have it working now.   Register To Reply

10. ## Re: Converting google sheets formula to excel  Register To Reply