Help with ROI Calc

Paul69g

Hi All

I need to calculate the ROI on projects with savings and the below formula is working correctly when projects have a cost and saving.

=([Expected cost savings in 1st 12 months]@row - [Total project costs]@row ) / [Total project costs]@row

However, some project have no cost, or no savings or both and these return minus ROI's or #DIVIDE BY ZERO.

What do i need to do to the formula to ignore projects with no spend or savings?

Kind regards

Paul

Sing C

Hi Paul,

You could try adding some error-handling to catch those scenarios. Something like:

=IF(OR(ISBLANK([Expected Cost Savings in 1st 12 months]@row ), ISBLANK([Total Project Costs]@row )), "", ([Expected Cost Savings in 1st 12 months]@row - [Total Project Costs]@row ) / [Total Project Costs]@row )

This checks if either of the two columns is blank then return blank, otherwise use your RoI calc.

Note: depending on how you capture no cost or no savings in those columns, this formula may not work. If no cost or no savings are captured as 0 (zero) instead of blank, then this formula will work in both scenarios:

=IF(AND(ISNUMBER([Expected cost savings in 1st 12 months]@row ), ISNUMBER([Total project costs]@row ), [Total project costs]@row <> 0),
([Expected cost savings in 1st 12 months]@row - [Total project costs]@row ) / [Total project costs]@row ,
"")

This checks that both columns have a number in order to run the RoI calc, otherwise display blank.

Hope that helps! Let me know if that does the trick for you.

Thanks,

Sing

Sing C

Hi Paul,

You could try adding some error-handling to catch those scenarios. Something like:

=IF(OR(ISBLANK([Expected Cost Savings in 1st 12 months]@row ), ISBLANK([Total Project Costs]@row )), "", ([Expected Cost Savings in 1st 12 months]@row - [Total Project Costs]@row ) / [Total Project Costs]@row )

This checks if either of the two columns is blank then return blank, otherwise use your RoI calc.

Note: depending on how you capture no cost or no savings in those columns, this formula may not work. If no cost or no savings are captured as 0 (zero) instead of blank, then this formula will work in both scenarios:

=IF(AND(ISNUMBER([Expected cost savings in 1st 12 months]@row ), ISNUMBER([Total project costs]@row ), [Total project costs]@row <> 0),
([Expected cost savings in 1st 12 months]@row - [Total project costs]@row ) / [Total project costs]@row ,
"")

This checks that both columns have a number in order to run the RoI calc, otherwise display blank.

Hope that helps! Let me know if that does the trick for you.

Thanks,

Sing

Paul69g

Brilliant, thank you so much for your help Sing. The second formula works perfectly!

Kind regards

Paul

Sing C

Glad to hear it Paul! Thanks for letting me know.

I'd appreciate if you could help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!