Mode for Text

[Guest]

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 ! ..

 

[Guest]

Consider using a pivot table. In the Rows area, specify by date, by code
number
In the data area, specify Count of code number

 

[Guest]

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.

 

[domenic22]

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$11,0)),MATCH($B$2:$B$100,$B
$2:$B$100,0))))))

Hope this helps!

 

[Guest]

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$11,0)) .. the $D$11,0 has me confused ..

 

[Domenic]

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$11,0)) .. the $D$11,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, D11 changes to D12. 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, D12 changes to D13. 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!