Looking for the Excel equivalents to a few Google spreadsheet formulae.

[SaviourV]

Greetings again, good folk of Chandoo.org! Saviour-V reporting in.


SirJB7, remember my first question here, and what I said about tackling a certain beast? Well, here comes a small part of that particular beast. For reference, the original spreadsheet's featured at the following URL:


http://www.investmentmoats.com/portfolio/how-to-track-your-stock-transactions-with-google-spreadsheet/


*grins nervously...* Anyway:

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


...is one of the formulae I encountered in the spreadsheet from InvestmentMoats.com . I puzzled over this one for a bit, until I came across another post from the same site, showing an Excel equivalent in-progress:


[code]=IF(ROW()=2,0,IFERROR(LOOKUP(2,1/($C1:$C$2=C2),$I1:I$2),0))

To explain the formulae briefly, the formulae are supposed to calculate a cumulative amount of the stocks purchased. Only thing is I'm not sure if both formulae are one and the same, since the Google one has FILTER (which I'm sure is NOT part of Excel), and the Excel formula seems to be missing the part after


=max(if($C1:$C$2=C2,row($C1:$C$2),0))[/code]


Since I'm still a rookie here, does LOOKUP accomplish the same thing done by the Google formula? Also, what alternatives does Excel have against Google's FILTER? I'm guessing that maybe SUMPRODUCT is one of them, but what else could I use that doesn't involve a whole host of IF statements?


And out of curiosity, has anyone tried to create an Excel alternative to FILTER, either through formulae or VBA? This is an optional question, but I'd like to know if anyone has tried it.

 

[Luke M]

I believe they are equivalent.


Looking at Google formula:

The arrayformula function just lets the program know to treat the following as array, rather than a single input (equivalent of using Ctrl+Shift+Enter in XL). The filter function is taking the col I range and "filtering" it down to rows there col C = C2. If we stopped here, there would be multiple cells that could meet this criteria however. This is where the MAX function comes into play. It says that for the rows where col C = C2, give me the MAX row value. This gives a single output. Thus, the filter is going to give out only a single cell value.It then takes this row value and uses that to determine where from the Index (col C) it needs to look at.


In Excel side, the LOOKUP function performs this by automatically looking for the closest value that is equal to or less than lookup_value. The 1/(...) bit is converting the boolean output into numbers, and LOOKUP is then looking for last/closest value to 2 (side note: since the boolean can only be true/false, the dividend will be either 1/1 or 1/0. This is why it never "finds" 2). The lookup then takes this last found value and returns corresponding value from col I.


Is SUMPRODUCT equivalent? Pretty close. Technically, the filter function only looks at rows of interest, while SUMPRODUCT uses boolean arrays to cause unwanted rows to evaluate to 0. As long as you know how it works, this shouldn't cause a problem though.


Another alternative via VBA would be to automate a regular data filter and just select the last visible cell in col I. Or, you could use the Find method to search col C for value in C2, and just take the last value. I'm sure there's many other ways you could use as well. =)

 

[SaviourV]

Still need to go over the rest of that Google spreadsheet, but this is a good place to start as any. I'll post up a few findings as I go along.


Thanks, LukeM, for getting the ball rolling. Also, if you're looking at the Google worksheet I posted in the first message, there's a few other points where FILTER shows up.


Guess I need to look over this site and learn more about LOOKUP and SUMPRODUCT; I think those would play an important role real soon. That, and I need to figure out what those formulae do and devise Excel alternatives.


Looking at the Google spreadsheet itself, it's quite well-done, and I realize that I've a LONG way to go to get that good. ^_^;;

 

[Luke M]

No worries SaviourV, the important thing is that we all strive to learn more. =)

Have a great weekend!