Need help with a formula

mrseibt
mrseibt ✭✭✭
edited 05/02/24 in Formulas and Functions

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

  • James Keuning
    James Keuning ✭✭✭✭✭

    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.

  • mrseibt
    mrseibt ✭✭✭

    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.

  • jpaul
    jpaul ✭✭✭✭

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

  • mrseibt
    mrseibt ✭✭✭

    @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?

  • Jgorsich
    Jgorsich ✭✭✭✭

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!