Results 1 to 9 of 9
Thread: IFs shortcut (office XP)

20040911, 06:39 #1
 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.

20040911, 10:14 #2
 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

20040911, 10:23 #3
 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

20040911, 10:23 #4
 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

20040911, 12:44 #5
 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.

20040911, 16:44 #6
 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 VAG 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

20040911, 17:22 #7
 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

20040912, 13:00 #8
 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

20040912, 19:02 #9
 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