Is there a different way of displaying the max value?

Kristina Todoric
Kristina Todoric ✭✭✭✭
edited 08/22/22 in Formulas and Functions

Hi everyone,

I'd like to build a chart that displays spend vs. total budget with the chart colour changing depending if the project is over/under budget, and the chart also displaying the max budget.

I used a really helpful tutorial to build the example chart below but wondered if there's a different way of detailing/displaying the max budget? I've also added a screenshot of my example table that the chart is built from.

Any help would be greatly appreciated.

Thanks,

Kristina

Best Answer

  • Paul McGuinness
    Paul McGuinness Overachievers
    Answer ✓

    HI @Kristina Todoric

    You could possibly go for something like this?

    Budget forms part of the project name so everyone can see what they are working to, you cant auto change series color, but if you separate it out you can only display it in the event it happens?

    Stacked graphs would be far better but the displayed values overwrite each other (its a known smartsheet development issue) so the above are currently the only ways I can think you could do what you need automatically?

    Here is the updated data table

    formula in overspend column

    =IF(Spend@row > [Total Budget]@row, Spend@row - [Total Budget]@row, "")

    formula in spend in budget column

    =IF(ISBLANK(Overspend@row), Spend@row, [Total Budget]@row)

    Hope it helps

    Thanks

    Paul

Answers

  • Paul McGuinness
    Paul McGuinness Overachievers
    Answer ✓

    HI @Kristina Todoric

    You could possibly go for something like this?

    Budget forms part of the project name so everyone can see what they are working to, you cant auto change series color, but if you separate it out you can only display it in the event it happens?

    Stacked graphs would be far better but the displayed values overwrite each other (its a known smartsheet development issue) so the above are currently the only ways I can think you could do what you need automatically?

    Here is the updated data table

    formula in overspend column

    =IF(Spend@row > [Total Budget]@row, Spend@row - [Total Budget]@row, "")

    formula in spend in budget column

    =IF(ISBLANK(Overspend@row), Spend@row, [Total Budget]@row)

    Hope it helps

    Thanks

    Paul

  • Kristina Todoric
    Kristina Todoric ✭✭✭✭

    Thanks Paul! That's really helpful

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!