rank login attempts

jamesddelaney

New Member
Joined
Jun 24, 2012
Messages
4
i have a pp table of usernames and a date time stamp of each login. so if a user logged into a site 5 times, he would have 5 entries in the table.

i'd like to add a column to this table call login number.

username
login_datetime
login_number
james
1/1/12 5:00 AM
1
james
1/1/12 11:00 AM
2
james
1/2/12 6:00 AM
3
james
2/1/12 5:00 AM
4
james
3/10/12 5:00 AM
5
john
1/3/12 4:00 PM
1
john
1/3/12 8:00 PM
2
john
1/3/12 11:00 PM
3

<tbody>
</tbody>

what formula would you use to do the calculation for "login_number".
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">username </td><td style=";">login_datetime </td><td style=";">login_number</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">james </td><td style="text-align: right;;">1/1/2012 5:00</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">james </td><td style="text-align: right;;">1/1/2012 11:00</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">james </td><td style="text-align: right;;">1/2/2012 6:00</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">james </td><td style="text-align: right;;">2/1/2012 5:00</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">james </td><td style="text-align: right;;">3/10/2012 5:00</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">john </td><td style="text-align: right;;">1/3/2012 16:00</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">john </td><td style="text-align: right;;">1/3/2012 20:00</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">john </td><td style="text-align: right;;">1/3/2012 23:00</td><td style="text-align: right;;">3</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet5</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C2</th><td style="text-align:left">=COUNTIF(<font color="Blue">$A$2:A2,A2</font>)</td></tr></tbody></table></td></tr></table><br />

Drag formula down
 

jamesddelaney

New Member
Joined
Jun 24, 2012
Messages
4
this data is a powerpivot table, not in excel. any thoughts on how to do it in a powerpivot table?
 

jamesddelaney

New Member
Joined
Jun 24, 2012
Messages
4

ADVERTISEMENT

do i need PowerPivot 2012 RTM to make this work?
 

masplin

Active Member
Joined
May 10, 2010
Messages
406
ah yes sorry they only put the ranking function in the new version.
 

jamesddelaney

New Member
Joined
Jun 24, 2012
Messages
4
i have taken this a bit further but i an now running into this error:

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

I also had to change around the syntax of your formula just a bit but given the error i am guessing i didn't do it quite right.

Code:
=RANKX(Table1,FILTER(Table1,[username]=EARLIER([username])),[login_datetime],1,skip)

thanks.
 

masplin

Active Member
Joined
May 10, 2010
Messages
406
Sorry sloppy with my syntax. "skip" is the default so havent put it after the 1. The ",," before the 1 is because the "value" expression goes here (i'm not quite sure what "value" means!)

=RANKX(FILTER(Table1,[username]=EARLIER([username])),[login_datetime],,1)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,334
Messages
5,601,012
Members
414,421
Latest member
tonybear1994

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top