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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,530
Office Version
365
Platform
Windows
Whilst there are similarities, not all Excel formulae work in Sheets.
 

Some videos you may like

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,)

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
9,926
Office Version
2019
Platform
Windows
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
Joined
Nov 12, 2010
Messages
12,865
Office Version
365, 2010
Platform
Windows, Mobile
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).

1593633139146.png


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)

1593633101472.png
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
9,926
Office Version
2019
Platform
Windows
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
Joined
Apr 18, 2017
Messages
55
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,
 

Watch MrExcel Video

Forum statistics

Threads
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...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top