An example best illustrates my problem:

I have a long column of product codes, like the following

01051110
01051190
01051900
01059100
01059900
01060010
01060020
01060030
01060090
02062200
02062900
02063000
02064100
02064900
03011000
03019100
03019200
03019300


As you can see products start 01 then go to 02 then 03. In practice
this continues-04,05 etc until i come to 09, then it changes to 11, 12,
13,..19 then changes again to 21, 22, 23,..29. It does this until 91,
92,...99.

I wish to count the number of 2-digit products and present them in a
table. For example there are, in this example, 9 products that begin
with 01, 5 products that begin 02, and 4 products that begin 04.

Is there a quick way of doing this?


--
cj21
------------------------------------------------------------------------
cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=482177

look in Help index for LEFT and COUNTIF

--
Don Guillett
SalesAid Software

"cj21" wrote in message
...

An example best illustrates my problem:

I have a long column of product codes, like the following

01051110
01051190
01051900
01059100
01059900
01060010
01060020
01060030
01060090
02062200
02062900
02063000
02064100
02064900
03011000
03019100
03019200
03019300


As you can see products start 01 then go to 02 then 03. In practice
this continues-04,05 etc until i come to 09, then it changes to 11, 12,
13,..19 then changes again to 21, 22, 23,..29. It does this until 91,
92,...99.

I wish to count the number of 2-digit products and present them in a
table. For example there are, in this example, 9 products that begin
with 01, 5 products that begin 02, and 4 products that begin 04.

Is there a quick way of doing this?


--
cj21
------------------------------------------------------------------------
cj21's Profile:
http://www.excelforum.com/member.php...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=482177

This has speeded things up, Thankyou.

I created a column with the formula: =LEFT(D2,2). This created a list
of the products. Then i put in the formula in my "counting table"
=COUNTIF(L2:L6088,"01").

This has worked but when i fill down the count formula, the next line
becomes: =COUNTIF(L3:L6089,"01"). I want it to read
=COUNTIF(L2:L6088,"02").

In otherwords I just want the "criteria" to change. Do you know how to
do this?

Chris


--
cj21
------------------------------------------------------------------------
cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=482177

Assuming source data in A1:A20
List the product codes in B1 down: 1,2,3, ...
Put in C1, and copy down:
=SUMPRODUCT(--(LEFT(TEXT($A$1:$A$20,"00000000"),2)=TEXT(B1,"00") ))
Adapt the range to suit ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"cj21" wrote in message
...

An example best illustrates my problem:

I have a long column of product codes, like the following

01051110
01051190
01051900
01059100
01059900
01060010
01060020
01060030
01060090
02062200
02062900
02063000
02064100
02064900
03011000
03019100
03019200
03019300


As you can see products start 01 then go to 02 then 03. In practice
this continues-04,05 etc until i come to 09, then it changes to 11, 12,
13,..19 then changes again to 21, 22, 23,..29. It does this until 91,
92,...99.

I wish to count the number of 2-digit products and present them in a
table. For example there are, in this example, 9 products that begin
with 01, 5 products that begin 02, and 4 products that begin 04.

Is there a quick way of doing this?


--
cj21
------------------------------------------------------------------------
cj21's Profile:
http://www.excelforum.com/member.php...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=482177

The results in B1:C3 would be, for the sample data as posted:

1 9
2 5
3 4

which tallies with:

... For example there are, in this example, 9 products that begin
with 01, 5 products that begin 02, and 4 products that begin 04.

except for the typo in: "... 4 products that begin 04" (04 should be 03)

The formula would work even if the numbers in B1:B3
were entered as text numbers: 01, 02, 03
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

"cj21" wrote:
... Then i put in the formula in my "counting table"
=COUNTIF(L2:L6088,"01").

One way.. put instead in your starting cell, and copy down:
=COUNTIF($L$2:$L$6088,TEXT(ROW(A1),"00"))

Btw, you may also wish to try the SUMPRODUCT alternative
suggested in my earlier response
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

That is brilliant, but i have a more difficult problem:

ProductCode Tariff
01011100 3
01011900 40
01012000 40
01021000 3
01029010 7
01029020 7
01029090 40
01031000 3
01039100 40
01039200 40
01041010 3
01041090 40
01042010 3
01042090 40
01051110 3
01051190 40
01051900 40
01059100 40
01059900 40
01060010 3
01060020 7
01060030 7
01060090 40
02011000 40
02012000 40
02013000 40
02021000 40
02022000 40
02023000 40
02031100 60
02031200 60
02031900 60
02032100 60
02032200 60
02032900 60
02041000 40
02042100 40
02042200 40
02042300 40
02043000 40
02044100 40
02044200 40
02044300 40
02045000 40
02050000 40
02061000 40
02062100 40
02062200 40
02062900 40
02063000 40
02064100 60
02064900 60
02068000 60
02069000 60
02071000 40
02072100 40
02072200 40
02072300 40
02073100 40
02073910 40
02073990 40
02074100 40
02074200 40
02074300 40
02075000 40
02081010 15
02081090 60
02082000 60
02089010 15
02089090 60
02090000 60

Each product has a corresponding tariff (in a %). I want to find the
average tariff for the two digits.

So average tariff for product 01 would equal the sum of the tariff for
each line (that begins with 01), divided by the number of its
corresponding products. In this example the division for product
cattogry01 would be by: sum of tariffs/23.

I know how to work out the average by using the wizard, but i want a
nice formula that just fills down like in my last problem.


Chris


--
cj21
------------------------------------------------------------------------
cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=482177

Assuming the posted table is in A1:B72 (data from row2 to 72)

List the codes say, in D2 down: 1,2,3 ...

Put in E2, array-enter (press CTRL+SHIFT+ENTER):
=AVERAGE(IF(--(LEFT(TEXT($A$2:$A$72,"00000000"),2)
=TEXT(D2,"00")),$B$2:$B$72))
Copy E2 down

Col E will return the average tariff for the product codes listed in col D
Adapt the ranges to suit ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"cj21" wrote in message
...

That is brilliant, but i have a more difficult problem:

ProductCode Tariff
01011100 3
01011900 40
01012000 40
01021000 3
01029010 7
01029020 7
01029090 40
01031000 3
01039100 40
01039200 40
01041010 3
01041090 40
01042010 3
01042090 40
01051110 3
01051190 40
01051900 40
01059100 40
01059900 40
01060010 3
01060020 7
01060030 7
01060090 40
02011000 40
02012000 40
02013000 40
02021000 40
02022000 40
02023000 40
02031100 60
02031200 60
02031900 60
02032100 60
02032200 60
02032900 60
02041000 40
02042100 40
02042200 40
02042300 40
02043000 40
02044100 40
02044200 40
02044300 40
02045000 40
02050000 40
02061000 40
02062100 40
02062200 40
02062900 40
02063000 40
02064100 60
02064900 60
02068000 60
02069000 60
02071000 40
02072100 40
02072200 40
02072300 40
02073100 40
02073910 40
02073990 40
02074100 40
02074200 40
02074300 40
02075000 40
02081010 15
02081090 60
02082000 60
02089010 15
02089090 60
02090000 60

Each product has a corresponding tariff (in a %). I want to find the
average tariff for the two digits.

So average tariff for product 01 would equal the sum of the tariff for
each line (that begins with 01), divided by the number of its
corresponding products. In this example the division for product
cattogry01 would be by: sum of tariffs/23.

I know how to work out the average by using the wizard, but i want a
nice formula that just fills down like in my last problem.


Chris


--
cj21
------------------------------------------------------------------------
cj21's Profile:
http://www.excelforum.com/member.php...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=482177

I tried to do this latter approach for averages but it didn't work.

My product codes are in column D, their corresponding tariffs are in
column H and my list of 2-digit products ( that correspond to the
8-digit products e.g. 02134567 becomes 02) are in cloumn M.

I put in the following formula:

=AVERAGE(IF(--(LEFT(TEXT($D$2:$D$6088,"00000000"),2)
=TEXT(M2,"00")),$H$3:$H$6088))

This returns an average for product 01 as 24.80, when it should be 23,
what am i doing wrong. Also when i fill down the formula there are just
zeros in every column.

Chris


--
cj21
------------------------------------------------------------------------
cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=482177

=AVERAGE(IF(--(LEFT(TEXT($D$2:$D$6088,"00000000"),2)
=TEXT(M2,"00")),$H$3:$H$6088))

Do you have a typo in the range: $H$3:$H$6088 ?
It should be identical to the range in col D, viz. $H$2:$H$6088
(or the other way around)

And, did you array-enter the formula, i.e. press CTRL+SHIFT+ENTER,
instead of just pressing ENTER ?

Here's a sample implementation of the suggested formula
based on your data as posted previously:
http://cjoint.com/?lhnUb4WyIl
AverageIF_cj21_misc.xls

Perhaps check and try it again, it should work fine ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"cj21" wrote in message
...

I tried to do this latter approach for averages but it didn't work.

My product codes are in column D, their corresponding tariffs are in
column H and my list of 2-digit products ( that correspond to the
8-digit products e.g. 02134567 becomes 02) are in cloumn M.

I put in the following formula:

=AVERAGE(IF(--(LEFT(TEXT($D$2:$D$6088,"00000000"),2)
=TEXT(M2,"00")),$H$3:$H$6088))

This returns an average for product 01 as 24.80, when it should be 23,
what am i doing wrong. Also when i fill down the formula there are just
zeros in every column.

Chris


--
cj21
------------------------------------------------------------------------
cj21's Profile:
http://www.excelforum.com/member.php...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=482177

There was a slight typo, it should of said:

=AVERAGE(IF(--(LEFT(TEXT($D$2:$D$6088,"00000000"),2)=TEXT(M2,"00 ")),$H$2:$B$6088))

However this doesn't alter things, my product code list is:


ProductCode
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97


As opposed to your

1
2
3
etc

This might be where the problem lies

Chris


--
cj21
------------------------------------------------------------------------
cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=482177

My 2-digit product codes are also formatted as text.

Chris


--
cj21
------------------------------------------------------------------------
cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=482177

I actually think this is the problem.

Why do you have to press ctrl+shif+enter?

also,i did it for the first one-23. Then filled down and they continued
to be wrong

Chris


--
cj21
------------------------------------------------------------------------
cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=482177

"cj21" wrote
There was a slight typo, it should of said:


=AVERAGE(IF(--(LEFT(TEXT($D$2:$D$6088,"00000000"),2)=TEXT(M2,"00 ")),$H$2:$B$
6088))

The range: $H$2:$B$6088 is wrong (B?)
It should be: $H$2:$H$6088

However this doesn't alter things, my product code list is:
ProductCode
01
02
As opposed to your
1
2
etc

This might be where the problem lies
My 2-digit product codes are also formatted as text.

No, I don't think so. The TEXT(D2,"00") formula within the AVERAGE(IF ...)
would take care of that by converting the product codes (if numerical) into
text numbers for matching purposes.

Take a look at this revised working sample
where I've replicated your actual situation closely:
col D =product codes, col H = Tariff,
col M = product code (entered as text),
col N = the computed average tariff
http://cjoint.com/?lhoVJfJMFs
AverageIF_cj21_misc_1.xls

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

"cj21" wrote:
I actually think this is the problem.
Why do you have to press ctrl+shif+enter?

Ah, zo ! .. Because it's an array formula, and array formulas
need to be confirmed in this manner: CTRL+SHIFT+ENTER
(instead of just pressing ENTER)

And after confirming it correctly, if you look closely in the formula bar,
the entire formula will appear within curly braces {...}
These curly braces are inserted by Excel
(we don't enter these braces manually)

also,i did it for the first one-23.
Then filled down and they continued to be wrong

The CTRL+SHIFT+ENTER needs to be re-done each time to re-enter correctly the
array formula, if it is subsequently edited. Recheck carefully that the
formula in the starting cell is correctly array-entered (the curly braces
must be there) before you drag/copy down the column.

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

On Fri, 4 Nov 2005 09:07:43 -0600, cj21
wrote:


That is brilliant, but i have a more difficult problem:

ProductCode Tariff
01011100 3
01011900 40
01012000 40
01021000 3
01029010 7
01029020 7
01029090 40
01031000 3
01039100 40
01039200 40
01041010 3
01041090 40
01042010 3
01042090 40
01051110 3
01051190 40
01051900 40
01059100 40
01059900 40
01060010 3
01060020 7
01060030 7
01060090 40
02011000 40
02012000 40
02013000 40
02021000 40
02022000 40
02023000 40
02031100 60
02031200 60
02031900 60
02032100 60
02032200 60
02032900 60
02041000 40
02042100 40
02042200 40
02042300 40
02043000 40
02044100 40
02044200 40
02044300 40
02045000 40
02050000 40
02061000 40
02062100 40
02062200 40
02062900 40
02063000 40
02064100 60
02064900 60
02068000 60
02069000 60
02071000 40
02072100 40
02072200 40
02072300 40
02073100 40
02073910 40
02073990 40
02074100 40
02074200 40
02074300 40
02075000 40
02081010 15
02081090 60
02082000 60
02089010 15
02089090 60
02090000 60

Each product has a corresponding tariff (in a %). I want to find the
average tariff for the two digits.

So average tariff for product 01 would equal the sum of the tariff for
each line (that begins with 01), divided by the number of its
corresponding products. In this example the division for product
cattogry01 would be by: sum of tariffs/23.

I know how to work out the average by using the wizard, but i want a
nice formula that just fills down like in my last problem.


Chris

You can do this easily using a helper column along with either Pivot Table (my
preference) or SubTotals.

First set up a third helper column -- let's call it Type.

Formula: =INT(ProductCode/10^6)

That'll give you the first two digits of the Product Code.

For a Pivot Table, with the active cell someplace in the table, select
Data/PivotTable Finish

Drag Type to the Rows area
Drag Type to the Data area
Drag Tariff to the Data area

Right click on "sum of type"; Field Settings and select Count
Rename it as you wish and also select Number if you don't like the
default number format.

Right click on "sum of tariff"; Field Settings and select Average
Rename it as you wish and also select Number if you don't like the
default number format.

Finally, with the cursor in the Pivot Table, select from the top menu bar
Format/Auto Format and select an attractive format for your report.

=============================

For the Data/Subtotals wizard, you first have to ensure your data is sorted by
Type (i.e. first one or two digits).

Then select Data/Subtotals

At each change in Type
Use Function Count
Add Subtotal To (You can choose any field here; the difference will be
where it appears in the report)

Then again select Data/Subtotals
Ensure Replace Current Subtotals is DEselected

At each change in Type:
Use Function Average:
Add Subtotal to Tarriff

Then collapse the table using the buttons on the left side to display how you
want.

Rename the labels to your liking.


--ron