Need better solution for multiple nested IF statement

K Morano
K Morano ✭
in Formulas and Functions

Hello,

I have a sheet that tracks sprint numbers based on their date ranges. How can I identify the sprint number for a task using the task start date? Right now I'm using this very long formula in the Return sprint number column, and I'm hoping there is a better way to do this. Thank you!

=IF(AND([Task Start Date]@row >= [Start Sprint]1, [Task Start Date]@row <= [End Sprint]1), [Sprint Number]1, IF(AND([Task Start Date]@row >= [Start Sprint]2, [Task Start Date]@row <= [End Sprint]2), [Sprint Number]2, IF(AND([Task Start Date]@row >= [Start Sprint]3, [Task Start Date]@row <= [End Sprint]3), [Sprint Number]3, IF(AND([Task Start Date]@row >= [Start Sprint]4, [Task Start Date]@row <= [End Sprint]4), [Sprint Number]4, IF(AND([Task Start Date]@row >= [Start Sprint]5, [Task Start Date]@row <= [End Sprint]5), [Sprint Number]5, IF(AND([Task Start Date]@row >= [Start Sprint]6, [Task Start Date]@row <= [End Sprint]6), [Sprint Number]6, IF(AND([Task Start Date]@row >= [Start Sprint]7, [Task Start Date]@row <= [End Sprint]7), [Sprint Number]7, IF(AND([Task Start Date]@row >= [Start Sprint]8, [Task Start Date]@row <= [End Sprint]8), [Sprint Number]8, IF(AND([Task Start Date]@row >= [Start Sprint]9, [Task Start Date]@row <= [End Sprint]9), [Sprint Number]9, IF(AND([Task Start Date]@row >= [Start Sprint]10, [Task Start Date]@row <= [End Sprint]10), [Sprint Number]10))))))))))

sprint number sheet.png

Tags:
· Share on FacebookShare on Twitter

Answers

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭✭

    Hi @K Morano,

    Give this a try.

    =INDEX(COLLECT([Sprint Number]:[Sprint Number], [Start Sprint]:[Start Sprint], @cell <= [Task Start Date]@row, [End Sprint]:[End Sprint], @cell >= [Task Start Date]@row), 1)

    Hope this helps,

    Dave

    · Share on FacebookShare on Twitter
  • K Morano
    K Morano ✭
    edited 07/02/24

    Hi @DKazatsky2. Thank you so much! That worked. Can I complicate the matter and ask how to write the formula if I am looking up the sprint number from a another sheet? For example, I have a sheet called Project Sheet with the columns Task Start Date and Sprint Number and I need to reference the sheet Sprint Dates (with the sprint number, start sprint, end sprint as in the attachment above). Are you able to show me what that formula looks like? Thanks again! In any case, you've been so helpful and at least got me started and showed me the Index Collect formula.

    · Share on FacebookShare on Twitter
  • K Morano
    K Morano ✭

    @DKazatsky2 Here is the formula I created attempting to reference another sheet. The sheet with the sprint number, start sprint, and end sprint columns is called Oracle Sprint Dates. The other sheet where I am attempting to capture the sprint number is called Reports Project and has the columns Task Start Date and Sprint Number. I created the formula in the Sprint Number column on the Reports Project sheet. Here is the formula I created, but it doesn't work. Any thoughts?

    =INDEX(COLLECT({Oracle Sprint Dates Sprint Number}:{Oracle Sprint Dates Sprint Number}, {Oracle Sprint Dates Start Sprint}:{Oracle Sprint Dates Start Sprint}, @cell <=[Task Start Date]@row, {Oracle Sprint Dates End Sprint}:{Oracle Sprint Dates End Sprint}, @cell>=[Task Start Date]@row), 1)

    Screenshot 2024-07-02 at 11.27.17 AM.png

    · Share on FacebookShare on Twitter
  • K Morano
    K Morano ✭

    One more comment - I figured out how to write the formula referencing another sheet, in case others are interested:

    =INDEX(COLLECT({Oracle Sprint Dates Sprint Number}, {Oracle Sprint Dates Start Sprint}, @cell <= [Task Start Date]@row, {Oracle Sprint Dates End Sprint}, @cell >= [Task Start Date]@row), 1)

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!