How to fix timeline code

Question

Hi

I have created a timeline with with category column for High Risk, Med Risk, Low Risk, milestones etc and this feeds into the chart. However I updates the category list from High, Med and Low Risk to High Priority, Low Priority, Medium Priority, but when I do this, it does not pull into chart. How can I update this IF statement please

=IF(AND($C10="Goal",L$7>=$I10,L$7<=$I10+$J10-1),2,IF(AND($C10="Milestone",L$7>=$I10,L$7<=$I10+$J10-1),1,""))

Answer 1

The formula you mention returns 2 if C10 = "Goal" and if two other criteria are met. It returns 1 if C10 = Milestone" and if the two other criteria are met. Other it returns a blank. Where do "High Risk, Med Risk, Low Risk" come into play with this formula?

It's not clear what you mean by "... and this feeds into the chart". Which 'chart' ?

Please share (a link on OneDrive or similar, giving us access) your file or at least some screenshots so that we can see what you are working on?

Answer 2

To update your IF statement to accommodate the new category names (High Priority, Medium Priority, Low Priority), you will need to modify the conditions in your formula to match these new values. Here’s how you can adjust your existing formula:

=IF(AND($C10="Goal", L$7>=$I10, L$7<=$I10+$J10-1), 2, IF(AND($C10="Milestone", L$7>=$I10, L$7<=$I10+$J10-1), 1, IF(AND($C10="High Priority", L$7>=$I10, L$7<=$I10+$J10-1), 3, IF(AND($C10="Medium Priority", L$7>=$I10, L$7<=$I10+$J10-1), 4, IF(AND($C10="Low Priority", L$7>=$I10, L$7<=$I10+$J10-1), 5, "")))))

In this modified formula:

• I added conditions for "High Priority", "Medium Priority", and "Low Priority". You can assign different numeric values (3, 4, 5) to represent these categories as needed.
• Ensure that the references to the cells (like $C10, L$7, $I10, and $J10) are correct according to your spreadsheet layout.
• After updating the formula, check if the chart is correctly linked to the updated data range that includes these new categories.

This should help your chart reflect the changes in the category names accordingly.