+ Reply to Thread
Results 1 to 11 of 11

Exclude Cells with Zero from Complicated SMALL Formula

  1. #1
    Forum Contributor
    Join Date
    06-10-2005
    MS-Off Ver
    2007
    Posts
    223

    Exclude Cells with Zero from Complicated SMALL Formula

    Hi,
    I have a combination Index and Match function which pulls in the weights of my portfolio for a given list of tickers. I have it handling the #N/A error if the portfolio does not hold a particular security by using IF(ISNA... and returning a zero instead. However, this is a problem when I use the SMALL formula on the resulting list of weights because it will include the securities with a "weighting" of zero. My formula for SMALL only ranks the securities if they are of a particular asset class: Equity, Fixed or Gold. Here is the formula:
    =SMALL(INDEX(($D$222:$D$10014={"Equity","Fixed","Gold"})*$C$222:$C$10014,0),ROW(A1))
    where C has the weights and D has the asset class list

    Any suggestions on how to make this formula ignore cells with a zero?

    Thanks,
    Phillycheese
    Attached Files Attached Files
    Last edited by Phillycheese5; 01-20-2016 at 04:19 PM.

  2. #2
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Exclude Cells with Zero from Complicated SMALL Formula

    may be
    =SMALL(INDEX(IF(($D$222:$D$10014={"Equity","Fixed","Gold"})*($C$222:$C$10014)>0,($D$222:$D$10014={"Equity","Fixed","Gold"})*($C$222:$C$10014)),0),ROW(A1))
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  3. #3
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,404

    Re: Exclude Cells with Zero from Complicated SMALL Formula

    In your IF(ISNA formula, try returning blank ("") instead of zero.
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  4. #4
    Forum Contributor
    Join Date
    06-10-2005
    MS-Off Ver
    2007
    Posts
    223

    Re: Exclude Cells with Zero from Complicated SMALL Formula

    Hi Hemesh, Hi Aardigspook,
    Thanks for your replies. Unfortunately, both yield a "#VALUE" error. I initially tried using the double quotes to blank out the cell, but the SMALL formula gave me the #N/A error which is why I then tried using the IF(ISNA error-handling statement with the zero.

    Any other suggestions are welcome!

    Phillycheese

  5. #5
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Exclude Cells with Zero from Complicated SMALL Formula

    can you upload a sample book

  6. #6
    Forum Contributor
    Join Date
    06-10-2005
    MS-Off Ver
    2007
    Posts
    223

    Re: Exclude Cells with Zero from Complicated SMALL Formula

    Hi Hemesh,
    I uploaded an abbreviated file which shows the bottom 5 ranks that I'm looking to get. In columns E, F and G there are the different formulas and then in columns J K and L are the results using the SMALL function. The highlighted result in column M is what I am expecting.
    Please take a look and hopefully this makes it more clear.
    Thanks,
    Phillycheese

  7. #7
    Forum Expert
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    1,277

    Re: Exclude Cells with Zero from Complicated SMALL Formula

    For Excel 2010, you can solve your problem using the aggregate.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Insert the formula in the first cell J10 and pull down and to the right.

  8. #8
    Forum Contributor
    Join Date
    06-10-2005
    MS-Off Ver
    2007
    Posts
    223

    Re: Exclude Cells with Zero from Complicated SMALL Formula

    Hey Czeslaw,
    I used that formula and changed a few of the syntax (semicolons and backslashes to commas) and it seems to work! See below:
    =AGGREGATE(15,6,F$10:F$59/($D$10:$D$104={"Equity","Fixed Income","Gold"}),ROW(A1))

    I have put this put this in my original file and it appears to work just fine :-)

    Thank you for your help!!!

    Phillycheese

  9. #9
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,404

    Re: Exclude Cells with Zero from Complicated SMALL Formula

    Ok, it's taken a while playing with Aggregate, but this should work:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Aggregate function 15 is 'Small', option 6 is 'ignore errors'. So to get it to ignore errors and zeros, the zeros need to be converted to errors, so the array (your Index) is divided by itself not equal to zero, which creates #DIV/0 errors from all the zeros. (I hope that made sense).

    Edit: Ok, that's what comes of taking a break while struggling with a formula - I was beaten to it - and with a shorter version too!
    Last edited by Aardigspook; 01-20-2016 at 04:49 PM. Reason: beaten to it

  10. #10
    Forum Contributor
    Join Date
    06-10-2005
    MS-Off Ver
    2007
    Posts
    223

    Re: Exclude Cells with Zero from Complicated SMALL Formula

    I appreciate the effort Aardigspook, as well as the alternative formula!!

    Phillycheese

  11. #11
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,404

    Re: Exclude Cells with Zero from Complicated SMALL Formula

    I'm glad you got what you needed - please consider giving Czeslaw some rep for getting the solution for you (click the star icon to the bottom-left of his post).

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Help with IF function, small, exclude zero's, other
    By MWORSHAM in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-31-2012, 01:38 AM
  2. Replies: 2
    Last Post: 06-20-2012, 12:22 PM
  3. Need a formula that sums a column but could exclude some cells
    By bj in forum Excel Formulas & Functions
    Replies: 24
    Last Post: 09-06-2005, 07:05 PM
  4. [SOLVED] Need a formula that sums a column but could exclude some cells
    By bj in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-06-2005, 02:05 PM
  5. Need a formula that sums a column but could exclude some cells
    By bj in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-06-2005, 07:05 AM
  6. Need a formula that sums a column but could exclude some cells
    By bj in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-06-2005, 04:05 AM
  7. Need a formula that sums a column but could exclude some cells
    By Watercolor artist in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  8. [SOLVED] Need a formula that sums a column but could exclude some cells
    By Watercolor artist in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-04-2005, 06:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1