Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Location
    Los Angeles, California, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help on IF statements and nested IF statements (Excel97)

    The worksheet has 11 stocks. The criteria are: a) At least 80% of the equities must be equal to or greater than a market capitalization of 5 Billion; [img]/forums/images/smilies/cool.gif[/img] if the stock has a market capitalization that is between 5 Billion and 50 Billion, then that position must not be greater than 20% of the total market value; c) and, no stock with a market capitalization of greater than 50 Billion may be greater than 25% of the total market value

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Help on IF statements and nested IF statements (Excel97)

    I don't see a question and don't really understand what you are asking.

    What do you want of the if statement?

    Steve

  3. #3
    New Lounger
    Join Date
    Feb 2003
    Location
    Los Angeles, California, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help on IF statements and nested IF statements (Excel97)

    Steve,

    My apologies. If I may add a few more lines . . .
    The way I set-up the IF statements in the work sheet to in order to carry out the daily test, I think, is somewhat convoluted. And, I was wondering if there would be a way to cut a few steps in the process. I have used IF and IF AND.

    GCB

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Help on IF statements and nested IF statements (Excel97)

    I don't completely understand your spreadsheet.
    Crit A this seems true for 9 of the col J values are >5 (out of 11) so this is 81.8%) so this is true. This criteria is NOT for an indi stock but for ALL so how does this criteria combine with the others which are based on ind stocks. All stocks could be in compliance, but if this is not what do you want to do?

    I don't understand the numbers from Q. When I check all your criteria, I get them ALL compliant so maybe I don't understand. I see that:
    If J<=5 Compliant no matter what (you don't stipulate any other crit for <=5 billion (row 13 & 21)
    If 5<Col J <=50 then G must be <20% (row 15 and 22 meet both) Crit B
    If Col J>50 then G must be <25% (this is true for all the rest) Crit C

    You seem to use Col Q, where some are compliant and others are NOT, but I don't see how that relates to all the 3?

    SOme more explanation, please.

    Steve

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Help on IF statements and nested IF statements (Excel97)

    A few formulas I came up with (Assuming my last note was correct!)

    Crit A "At least 80% of the equities must be equal to or greater than a market capitalization of 5 Billion"
    I assume this means that you want 80% of the values in Col J to be >5
    This will test that condition:
    <pre>=(COUNTIF(J12:J22,">5")/COUNT(J12:J22))>0.8</pre>


    The other crit test the individual rows is to AND at the end you can AND all 11 of the statements from each row to see compliance:
    Crit B: "if the stock has a market capitalization that is between 5 Billion and 50 Billion, then that position must not be greater than 20% of the total market value"
    Means to me that If 5<Col J <=50 then G must be <20%

    Crit C: "no stock with a market capitalization of greater than 50 Billion may be greater than 25% of the total market value"
    To me means that If Col J>50 then G must be <25%

    Not stated, (but seems obvious from your statments that:
    If J<=5 Compliant no matter what the value in Col G

    You can calculate the T/F of ALL 11 of these conditions and then AND that col or you can make an array formula if you do NOT need the individual results. The combined formula for cond B, C, and (my "D" = unstated) (this is an ARRAY formula confirm with ctrl-shift-enter):

    =AND(IF((J12:J22<=5)+(J12:J22>5)*(J12:J22<=50)*(F1 2:F22/SUM(F12:F22)<0.2)+(J12:J22>50)*(F12:F22/SUM(F12:F22)<0.25),TRUE,FALSE))

    This will be TRUE if ALL the 11 stocks meet the crit B-D. If ANY do NOT then it will be false. YOu can also combine this with crit A if desired into one formula(again this is an ARRAY formula, confirm with ctrl-shift-enter):

    =AND(IF((J12:J22<=5)+(J12:J22>5)*(J12:J22<=50)*(F1 2:F22/SUM(F12:F22)<0.2)+(J12:J22>50)*(F12:F22/SUM(F12:F22)<0.25),TRUE,FALSE),(COUNTIF(J12:J22,"> 5")/COUNT(J12:J22))>0.8)

    You used the value in Q23 (=Sum(Q12:Q22)) by bringing it to E4. This number (based on YOUR CALCS) is the sum of the MarketValues (col F) when EITHER Col J was between 5 and 50 OR Col G is <0.2. This sum is: (this formula can replace the current E4, again ARRAY confirm with ctrl-shift-enter):
    <pre>=SUM(IF((J12:J22>5)*(J12:J22<50)+(F12:F22/SUM(F12:F22)<0.2),F12:F22,0))</pre>


    With these formulas I list, you can get rid of any col >K and also the items in G11:I23. J11:J23 could be moved to col G (if desired), but the formulas above should be in place first since MOST USE this column.

    Hope this helps,
    Steve

Posting Permissions

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