Another Excel Formula For Stocks (Positive, Negative, Or Even)

[rebel123]

Thanks for your help. Please post the formula in an equals formula, please
with the exact cell numbers also.

I am doing stock research and I wanted to find out
how what the answer is (where I can drag the formula down)
for rows T213 and T214.

The way the formula works is like this
$53,376 COMPARED TO $54,498 COMPARED TO $42,572 COMPARED TO $66,807

ANSWER FOR T213 is Up, Down, Up
(Because $53,376 went up to $54,498 & then went down to $42,572 & then went up to $66,807)


ANSWER FOR T214 is Up, Down, Even
(Because $200 went up to $500 and then went down to $100 and was that even with $100.

 

[Fluff]

Whilst there are similarities, not all Excel formulae work in Sheets.

 

[Yongle]

I think that you may have missed post number 12

That would explain my bewilderment!

 

[jasonb75]

Assuming that google sheets still shows a #NAME? error (or similar) when an unknown function name is used, then my formula might still work if you confirm the array correctly. I believe that this is done by adding the arrayformula function to the formula rather than using the excel method of Ctrl Shift Enter.

=ARRAYFORMULA(TEXTJOIN(", ",1,INDEX(CHOOSE(SIGN(O213:Q213-P213:R213)+2,"DOWN","EVEN","UP"),N(IF({1},{3,2,1})))))

If that doesn't work then I have no idea what will other than a series of short formulas joined together as @Yongle suggested earlier, that formula although a bit longer, is a simpler method that will work fine with google sheets as long as you remember to align it to the correct rows in your sheet.

 

[MARK858]

I was bit puzzled by this one as Ctrl-Shift + Enter and =ArrayFormula both work on Google Sheets (If you use Ctrl-Shift + Enter then it automatically converts it to =ArrayFormula) and I have looked up the functions and Google Sheets appears to have all the functions with the parameters in the same order as Excel.

I couldn't see because of the above why the formula jasonb75 posted wouldn't work with Google Sheets if it had been entered with Ctrl-Shift + Enter so I gave it a go but unfortunately I got the results in column T in the image below (I haven't had time to work out why).

I then put in column S the formula Yongle posted with some extra random numbers and I can't see any issue.
@rebel123, can you copy and paste your data directly in the thread please, including data where Yongles formula is giving incorrect results (do a straight copy/paste of the data, do not post an image as I want to see if I can copy it into Google Sheets)

 

[jasonb75]

I couldn't see because of the above why the formula jasonb75 posted wouldn't work with Google Sheets if it had been entered with Ctrl-Shift + Enter so I gave it a go but unfortunately I got the results in column T in the image below

I've just tried it with sheets, it appears that the use of N(IF({1} to coerce multiple results from a single index function is not accepted. With INDEX and N(IF({1} removed as below the formula works in sheets, however it does not give the expected results as the INDEX array is needed to reverse the order.

=TEXTJOIN(", ",1,CHOOSE(SIGN(O213:Q213-P213:R213)+2,"DOWN","EVEN","UP"))

 

[rebel123]

yes thanks fellas... i realize I am 100% unorganized since I changed the rows... But now I am studying stocks and there are 3,534 rows. I think you guys could make money in stocks if you figured out the patterns.
I am new to trading stocks, but I have sold 100's of real estate properties.

Thanks, and I will keep posting and NEXT TIME, I will test them on Excel since I have them on my Macbook,