Hi guys,

I think i've learned more about excel in the last few weeks of reading these
posts then the last 3 years of using them ;)

my problem this time .. i have a spreadsheet in which information is being
added by date and by a code number. I need to a formula similar to the Mode
function which will tell me the most often occuring code number in a specific
week number.

So basically i need to search conditionally by week number and identify not
only the most often occuring code but preferably the the 2nd and 3rd most
often occuring for that week.

That make sense ? ..

week number is just simply 43, 44 etc
Code numbers are in the format T1234 or PS0123

Any help would be great ! ..

Consider using a pivot table. In the Rows area, specify by date, by code
number
In the data area, specify Count of code number
--
Gary''s Student - gsnu200753


"Karma2400" wrote:

Hi guys,

I think i've learned more about excel in the last few weeks of reading these
posts then the last 3 years of using them ;)

my problem this time .. i have a spreadsheet in which information is being
added by date and by a code number. I need to a formula similar to the Mode
function which will tell me the most often occuring code number in a specific
week number.

So basically i need to search conditionally by week number and identify not
only the most often occuring code but preferably the the 2nd and 3rd most
often occuring for that week.

That make sense ? ..

week number is just simply 43, 44 etc
Code numbers are in the format T1234 or PS0123

Any help would be great ! ..

Thnx Gary, ive got a pivot table set up on an additional page but i really
want to use this information to auto fill in an additional page which has
various graphs and tables.

Using a pivot table would require the additional table to be filled in
manually each time the data is needed i would imagine .. not used them very
much.

"Gary''s Student" wrote:

Consider using a pivot table. In the Rows area, specify by date, by code
number
In the data area, specify Count of code number
--
Gary''s Student - gsnu200753


"Karma2400" wrote:

Hi guys,

I think i've learned more about excel in the last few weeks of reading these
posts then the last 3 years of using them ;)

my problem this time .. i have a spreadsheet in which information is being
added by date and by a code number. I need to a formula similar to the Mode
function which will tell me the most often occuring code number in a specific
week number.

So basically i need to search conditionally by week number and identify not
only the most often occuring code but preferably the the 2nd and 3rd most
often occuring for that week.

That make sense ? ..

week number is just simply 43, 44 etc
Code numbers are in the format T1234 or PS0123

Any help would be great ! ..

Assuming that A2:A100 contains the week number, and B2:B100 contains
the code, try the following formula that needs to be confirmed with
CONTROL+SHIFT+ENTER...

D2, copied down:

=INDEX($B$2:$B$100,MODE(IF($A$2:$A$100=44,IF($B$2: $B
$100<"",IF(ISNA(MATCH($B$2:$B$100,$D$1:D1,0)),MAT CH($B$2:$B$100,$B
$2:$B$100,0))))))

Hope this helps!

On Nov 1, 12:12 pm, Karma2400
wrote:
Hi guys,

I think i've learned more about excel in the last few weeks of reading these
posts then the last 3 years of using them ;)

my problem this time .. i have a spreadsheet in which information is being
added by date and by a code number. I need to a formula similar to the Mode
function which will tell me the most often occuring code number in a specific
week number.

So basically i need to search conditionally by week number and identify not
only the most often occuring code but preferably the the 2nd and 3rd most
often occuring for that week.

That make sense ? ..

week number is just simply 43, 44 etc
Code numbers are in the format T1234 or PS0123

Any help would be great ! ..

Thnx .. Excellent as always :) .. just working my way through the formula so
i can understand it .. can you tell me what this part does ? ..
(MATCH($B$2:$B$100,$D$1:D1,0)) .. the $D$1:D1,0 has me confused ..


" wrote:

Assuming that A2:A100 contains the week number, and B2:B100 contains
the code, try the following formula that needs to be confirmed with
CONTROL+SHIFT+ENTER...

D2, copied down:

=INDEX($B$2:$B$100,MODE(IF($A$2:$A$100=44,IF($B$2: $B
$100<"",IF(ISNA(MATCH($B$2:$B$100,$D$1:D1,0)),MAT CH($B$2:$B$100,$B
$2:$B$100,0))))))

Hope this helps!

On Nov 1, 12:12 pm, Karma2400
wrote:
Hi guys,

I think i've learned more about excel in the last few weeks of reading these
posts then the last 3 years of using them ;)

my problem this time .. i have a spreadsheet in which information is being
added by date and by a code number. I need to a formula similar to the Mode
function which will tell me the most often occuring code number in a specific
week number.

So basically i need to search conditionally by week number and identify not
only the most often occuring code but preferably the the 2nd and 3rd most
often occuring for that week.

That make sense ? ..

week number is just simply 43, 44 etc
Code numbers are in the format T1234 or PS0123

Any help would be great ! ..

Thnx .. Excellent as always :) .. just working my way through the formula so
i can understand it .. can you tell me what this part does ? ..
(MATCH($B$2:$B$100,$D$1:D1,0)) .. the $D$1:D1,0 has me confused ..

Cell D1 is either left blank or contains a label. In either case, no
match will exist between each value in B2:B100 and D1. As such, the IF
statement associated with the MATCH function will be evaluated as true
for each value in B2:B100, and the second MATCH function returns the
corresponding values.

When the formula is copied to D3, D1:D1 changes to D1:D2. Cell D2 will
now contain one of the values in B2:B100. Now, one or more cells in
B2:B100 will match the value in D2. As such, the IF statement will
evaluate as true those that don't match and false for those that do. In
effect, we've eliminated from further evaluation those values in B2:B100
that match D2.

When the formula is copied to D4, D1:D2 changes to D1:D3. Now we have
two values from B2:B100 in D2 and D3. And again, one or more cells in
B2:B100 will match the values in D2 and D3. So the IF statement will
evaluate as true those that don't match and false for those that do.
Again, we've eliminated from further evaluation those values in B2:B100
that match D2 and D3.

This process continues each time the formula is copied to the next cell
below.

Hope this helps!

In article ,
Karma2400 wrote:

Thnx .. Excellent as always :) .. just working my way through the formula so
i can understand it .. can you tell me what this part does ? ..
(MATCH($B$2:$B$100,$D$1:D1,0)) .. the $D$1:D1,0 has me confused ..


" wrote:

Assuming that A2:A100 contains the week number, and B2:B100 contains
the code, try the following formula that needs to be confirmed with
CONTROL+SHIFT+ENTER...

D2, copied down:

=INDEX($B$2:$B$100,MODE(IF($A$2:$A$100=44,IF($B$2: $B
$100<"",IF(ISNA(MATCH($B$2:$B$100,$D$1:D1,0)),MAT CH($B$2:$B$100,$B
$2:$B$100,0))))))

Hope this helps!

On Nov 1, 12:12 pm, Karma2400
wrote:
Hi guys,

I think i've learned more about excel in the last few weeks of reading
these
posts then the last 3 years of using them ;)

my problem this time .. i have a spreadsheet in which information is
being
added by date and by a code number. I need to a formula similar to the
Mode
function which will tell me the most often occuring code number in a
specific
week number.

So basically i need to search conditionally by week number and identify
not
only the most often occuring code but preferably the the 2nd and 3rd most
often occuring for that week.

That make sense ? ..

week number is just simply 43, 44 etc
Code numbers are in the format T1234 or PS0123

Any help would be great ! ..