LinkBack URL
About LinkBacks
Hi,
I'm trying to rank the top five position for Longs and Short.
I tried =sumif(B:V,"short positions",large(N:N,1))
Any helps?
Hi,
I'm trying to rank the top five position for Longs and Short.
I tried =sumif(B:V,"short positions",large(N:N,1))
Any helps?
See file. i think that you might need to be more explicit in what you want. please don't use jargon phrases. I'm an analytical chemist, not a financier. use column/.row references.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Hi Glen,
I would like to rank the top 5 market value in column n, with the condition "short positions" in column c.
Right now, in cell AA10, it is ranking the largest value as 609,532.26, however, I'm only interested in the condition "short positions" in column c.
Please help.
Mmm. I attached the wrong file last night. Here's the array formula that I used:
=IFERROR(LARGE(IF($B$2:$B$17=AA$9,$N$2:$N$17),ROWS(AA$10:AA10)),"")
Notes: For long positions (also asked for in Post 1) there are only 3 values. 3577.50, 22.50 and 0.00. Rather than return #NUM, I added an error trap to give a blank rather than a zero. That is more factually correct. A zero is a result, whereas a blank indicates an absence of a result. Your (IMHO unnecessary) total formula in row 16 doesn't like the blanks. You can make the LARGE formula return zeros (substitute 0 for "" at the end of the formula). But that's not reflective of the actual situation. besides, again IMHO, a check should use a different principle to reach the same result in orde that it is effective. Identifying the 5 largest separately and adding them up is pretty much the same as adding them up collectively...
As per your request, this gives the mathematically correct answers. Unless you mean the non-zero short positions.... or those with the largest ABSOLUTE amount (positive or negative). If so, let me know & I'll amend.
Hi Glenn,
Thank you for the help.
My goal is to automatically rank the top 5 short and long position automatically from the raw data. Right now, it is taking up too much time sorting and manipulating the data to do that.
You're formulas look great, but would the formulas be able to generate the Expected result? I provided an example of the expected result in starting in Cell Z21. See attached.
In AB22
=SMALL(IF($B$2:$B$100=$AA$21,$N$2:$N$100),ROWS($1:1))
Enter with Ctrl+****+Enter
Copy down
in AA22
=INDEX($F$2:$F$100,MATCH(AG22,$N$2:$N$100,0))
in AD22
=LARGE(IF($B$2:$B$100=$AC$21,$N$2:$N$100),ROWS($1:1))
Enter with Ctrl+****+Enter
in AC22
=INDEX($F$2:$F$100,MATCH(AI22,$N$2:$N$100,0))
Hi John,
Wow, the formulas =SMALL(IF($B$2:$B$100=$AA$21,$N$2:$N$100),ROWS($1:1)) and =LARGE(IF($B$2:$B$100=$AC$21,$N$2:$N$100),ROWS($1:1)) work perfectly.
I'm not able to get the formulas =INDEX($F$2:$F$100,MATCH(AG22,$N$2:$N$100,0)) and =INDEX($F$2:$F$100,MATCH(AI22,$N$2:$N$100,0)) to work.
It populated "NFLX 161118P00100000" for all rows. (see attached).
Expectation:
1st AMZN (32,775.54) CF 571,000.00
2nd ACM (23,543.75) CTXS 264,930.00
3rd ATR (21,603.88) CSRA 255,440.00
4th BOBE (16,230.56) PLAY 236,600.00
5th ACIW (11,416.50) GLW 170,030.00
See attached (yellow box)
my error ,,,
=INDEX($F$2:$F$100,MATCH(AB22,$N$2:$N$100,0))
=INDEX($F$2:$F$100,MATCH(AD22,$N$2:$N$100,0))
I didn't amend the formulae from my test ranges.
Apologies
Last edited by JohnTopley; 11-29-2016 at 02:47 PM.
I've been working. Finally, you gave us what your spec was/what your expected results look like. At least John was able to sort you out while I was offline.
Thank you to John and Glenn. You guys rock.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks