Apr 26, 2009

[ Usage of --() or ()*1 ]

   Hi ! Here Suggest to you.

                                                  
  DataSheet= Sheet1
Let's Look & Find 
[ Usage of --() or ()*1 ]
10 
11 
12 
13 
14 
15 
16 
17 
18 
19 
20 
ABCDEFGHI
Get Textual Numeric Summary Want True's count
143333 143FALSE00
161111 161TRUE11
157777 135FALSE00
135555  011
 01616     
143333 143FALSE00
161111 161TRUE11
119999 119FALSE00
157777 157TRUE11
114444 135FALSE00
135555  022
 02929  022
 #VALUE!#VALUE!#VALUE!Formula is not FormulaArray
 02929Formula is FormulaArray
 02929SUMPRODUCT Auto Recognize Array
Answer Number from function(Left, Right , Mid Substitute) is Stored as Text
And Logical Value( True , False ) is not Number
so, cannot  count or summary for Textual Number , Logical Value
That time use method of  --() or ()*1

Used Formula ...(With Running MicrosoftExcel Ver 2003)
  If Pink Formula that is FormulaArray !!!
  WithOut {} Input Formula and Press Control+Shift+Enter
NoAddr'  If use below Formula, You'll Get Result as RightResultEtc
1B2=RIGHT(A2,1)3 
2 B2  His Formula Used This Cell  -> B2:B5  
3B6=SUM(B2:B5)0 
4 B6  His Formula Used This Cell  -> B6:D6  
5B7:B12=RIGHT(A7:A12,1)
Select All Range and Input Formula
3 
6B13=SUM(B7:B12)0 
7 B13  His Formula Used This Cell  -> B13:D13  
8B14=SUM(RIGHT(A7:A12,1))#VALUE! 
9B15=SUM(RIGHT(A7:A12,1))0 
10B16=SUMPRODUCT(RIGHT(A7:A12,1))0 
11C2=RIGHT(A2,1)*13 
12 C2  His Formula Used This Cell  -> C2:C5  
13C7:C12=RIGHT(A7:A12,1)*1
Select All Range and Input Formula
3 
14C14=SUM(RIGHT(A7:A12,1)*1)#VALUE! 
15C15=SUM(RIGHT(A7:A12,1)*1)29 
16C16=SUMPRODUCT(RIGHT(A7:A12,1)*1)29 
17D2=--RIGHT(A2,1)3 
18 D2  His Formula Used This Cell  -> D2:D5  
19D7:D12=--RIGHT(A7:A12,1)
Select All Range and Input Formula
3 
20D14=SUM(--RIGHT(A7:A12,1))#VALUE! 
21D15=SUM(--RIGHT(A7:A12,1))29 
22D16=SUMPRODUCT(--RIGHT(A7:A12,1))29 
23G2=F2>150FALSE 
24 G2  His Formula Used This Cell  -> G2:G4  
25G5=SUM(G2:G4)0 
26 G5  His Formula Used This Cell  -> G5:I5  
27G7:G11=F7:F11>150
Select All Range and Input Formula
FALSE 
28G12=SUM(G7:G11)0 
29 G12  His Formula Used This Cell  -> G12:I12  
30G13=SUMPRODUCT((E7:E11>150))0 
31H2=(F2>150)*10 
32 H2  His Formula Used This Cell  -> H2:H4  
33H7:H11=(F7:F11>150)*1
Select All Range and Input Formula
0 
34H13=SUMPRODUCT((F7:F11>150)*1)2 
35I2=--(F2>150)0 
36 I2  His Formula Used This Cell  -> I2:I4  
37I7:I11=--(F7:F11>150)
Select All Range and Input Formula
0 
38I13=SUMPRODUCT(--(F7:F11>150))2 
.

                                                  
Locations of visitors to this page