Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IFs shortcut (office XP)

    hi, attached is a workbook that contains 21 columns with labels 1,2...21 that contains whether 'B' or 'S'. and some additional following columns to the right side to state 'B' if that contents of column label 1 until 21 are all 'B' and also state 'S' if the contents of column label 1 until 21 are all 'S'. well, i get the 'TARGET' with these few columns, can anyone perhaps shrink these formulas to simpler one? THANKS.

  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: IFs shortcut (office XP)

    I think you meant:

    <pre>=IF(COUNTIF(A10:U10,"B")=21,"B",IF(COUNTIF(A1 0:U10,"S")=<font color=red>21</font color=red>,"S",0))</pre>


    Another option (other than displaying zero) is to display the count of each:
    =IF(COUNTIF(A11:U11,"B")=21,"All Bs",IF(COUNTIF(A11:U11,"S")=21,"all Ss",COUNTIF(A11:U11,"B")&" B, "&COUNTIF(A11:U11,"S")&" S"))

    Steve

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: IFs shortcut (office XP)

    Thanks for that Steve. You are of course correct.

    Original corrected now.

    Andrew

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: IFs shortcut (office XP)

    I idon't really understand what the objective is bit if you enter the following formual in the Target cells you might get what you are after :

    =IF(COUNTIF(A10:U10,"B")=21,"B",IF(COUNTIF(A10:U10 ,"S")=21,"S",0))

    Enter that in AG10 and copy down.

    If i have misunderstood the problem, post back.

    Andrew C

  5. #5
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IFs shortcut (office XP)

    hi Steve, hi Andrew, i got your formulas in my spreadsheet, please look back at the file because your result does not match my result. notice Steve's formula =IF(COUNTIF(A11:U11,"B")=21,"All Bs",IF(COUNTIF(A11:U11,"S")=21,"all Ss",COUNTIF(A11:U11,"B")&" B, "&COUNTIF(A11:U11,"S")&" S")) says all B in row10 and Target should be B, row33 is all S and Target is S, row79 is all B and Target is B. rows that has mixture of B and S has Target 0, like in row 84 has 20B and 1S and Target is 0, row 99 has 20B and 1S and Target is 0. and rows with continous all B has Target 0 until next all S comes. and if next all S comes in again, Target is 0. there will always be B,S,B,S,... alternate moving. first all B or first all S will be B or S. otherwise, following all B or following all S remain Target 0. so, first all B or first all S will have Target B or Target S and Target 0 happens when there is mixture of B and S, and when this row has the following all B or all S. if not, i will have to insert two more columns in AI with formula =IF(AH10=0,AI9,AH10) and in column AJ with formula =IF(AI10=AI9,0,AI10) to make my Target. the this formula =IF(COUNTIF(A10:U10,"B")=21,"B",IF(COUNTIF(A10:U10 ,"S")=21,"S",0)) is in column AH, column before AI and AJ. better not, 3 columns is far better than 12 columns. Steve, i notice your formula is referenced as 'top' maybe that is named formula i will track it down so that i could know how to make it and see the difference in my file memory. and i am not experienced in manipulating formulas, so i start with one formula at a time until i get the last one. there are some other parts in my spreadsheet that need shortcut and i will shrink them later. sorry guys, maybe i should explained above before and THANKS.

  6. #6
    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: IFs shortcut (office XP)

    I am confused at what you want to get in your results. (I have no clue what the columns V-AG are supposed to be for).

    Could you explain what you want the formula to achieve? I thought we were determining the number of Bs and Ss. If you want to do something different you need to explain what you want to achieve.

    <hr>Steve, i notice your formula is referenced as 'top' <hr>

    I don't know what you mean by this? I never referenced anything with 'top'

    Steve

  7. #7
    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: IFs shortcut (office XP)

    If your "goal" is to get the values in the "target column" (AG) in AH enter the formula:
    <pre>=IF(COUNTIF(A10:U10,"B")=21,"B",IF(COUNTIF(A1 0:U10,"S")=21,"S",IF(OR(AH9="B",AH9=1),1,2)))</pre>


    Then in AJ use:
    <pre>=IF(OR(AH10=AH9,AH10=1,AH10=2),0,IF(AND(AH10= "B",AH9<>1),"B",IF(AND(AH10="S",AH9<>2),"S",0) ))</pre>


    I would not try to combine them into 1 formula, but keep this one intermediate (col AH) formula (it could be hidden).

    Once you confirm you can delete the current intermediate columns and just use these 2 formulas.

    Steve

  8. #8
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IFs shortcut (office XP)

    Hi Steve, THANK YOU SO MUCH for all your formulas. Your formula still not get when there is if in row11 all are S you cannot get a S in Target, but I learned a bit about your formula and get it correct: in AK10, the formula is =IF(COUNTIF(A10:U10,"B")=21,"B",IF(COUNTIF(A10:U10 ,"S")=21,"S",AK9)) and in AL10 the formula is =IF(AK10=AK9,0,AK10). That

  9. #9
    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: IFs shortcut (office XP)

    Glad it is all worked out.

    You have cell AH10 named "top" (insert - name -define) which is why referense to that use the name. You can delete it if you don't use the name for anything.

    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
  •