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

#### Fluff

##### MrExcel MVP, Moderator
Whilst there are similarities, not all Excel formulae work in Sheets.

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### Yongle

##### Well-known Member
I think that you may have missed post number 12
That would explain my bewilderment!

#### jasonb75

##### Well-known Member
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

##### MrExcel MVP
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

##### Well-known Member
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

##### Board Regular
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,

1,102,117
Messages
5,484,842
Members
407,469
Latest member
moshea

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...