Need help with a formula
I am trying to get a formula for my Speaker Position column that will rank each speaker if there are multiple on a given date. I have tried using the AI generate formula and it keeps coming back with an error. Please help!
Answers
-
So you have three people all speaking at 10:10. So each of these people is number 1? And what happens on the second day? They are all number 2? Or do they reset to number 1 again?
You should fil out that Speaker Position with your desired result, and explain the logic of how that result is determined, and then we can help create the formula.
-
There is only 1 speaker a day. Those signed up on the same day are the backups in case the first person can not make it. So in the example above, in the Speaker Position column, I would need it to return the value of 1 on row 113, 2 on row 114, and 3 on row 115. I tried the AI formula generator inside Smartsheet and it kept giving me an error.
-
@mrseibt , there doesn't appear to be any information within the row for the formula to know who is 1,2 or 3. Adding a column and naming them "Primary", "Backup 1", and "Backup 2" would allow for a formula to set their precedence.
-
@jpaul the names get onto the sheet by people filling out a form. If I created a new column and used the Created Date, would it be able to make a formula based on this? They might all submit a response on the same day, but times will most likely be different. Could that work?
-
Add a helper column title "row" that just has the row numbers.
Here is a thread where various methods were described:
once you've got that, make your formula something that counts all instances of your speaker and time on that row and earlier rows, like this "=countifs([speaker and time]:[speaker and time],[speaker and time]@row,row:row,< = row@row)"
Help Article Resources
Categories
Check out the Formula Handbook template!
