Division zero constraint

[linoyel]

Hi,

In chart dimension calculation, I have a division, but we know that it's impossible to divide  into zero. Here's my calculation:

(Sum(INCOME) / (Sum(COST)+ Sum(SALARY))) - 1 

The result is percent.

If (Sum(COST)+ Sum(SALARY) = 0, then the result should be 100%.

How to write it? Pls help!

[luis_pimentel]

Try something like:

IF ( (Sum(COST)+ Sum(SALARY) = 0, 1,(Sum(INCOME) / (Sum(COST)+ Sum(SALARY))) - 1  )

[kiranmanoharrode]

Hello Linoy,

Please post a sample(Scenario) application for better understanding

Regards,

Kiran

[#4 · Accepted solution]

HI linoy

try this expression:

if((sum(Cost)+sum(Salary))=0,1,(Sum(INCOME) / (Sum(COST)+ Sum(SALARY))) - 1 )

[vikasmahajan]

Expression is

IF(  Sum(COST)+ Sum(SALARY) >0,(Sum(INCOME) / (Sum(COST)+ Sum(SALARY))) - 1 ,0)

in Numbers select show in %.

Vikas

[linoyel]

Well, I'm confused myself.. no, it's not supposed to be 100%, cause let's say if:

case 1:

I invested 100 and sold 120, so my ROI would be ((120/100)-1)*100 = 20%

case 2:

I invested 100 and sold 80, so my ROI would be ((80/100)-1)*100 = -20%

case 3:

WHAT HAPPENS IF I INVEST 0 and sell 100??

What's my ROI then??

[linoyel]

The thing is that if Sum(COST)+ Sum(SALARY) = 0, then the result is not supposed to be 0 but quite an opposite!

This part (Sum(COST)+ Sum(SALARY)) is my investment. If it's 0, it means that all Sum(INCOME) is a benefit. How much it will be in percents though?..

[vikasmahajan]

Request you to share sample application so that help you.

Vikas

[linoyel]

case 1:

I invested 100 and sold 120, so my ROI would be ((120/100)-1)*100 = 20%

case 2:

I invested 100 and sold 80, so my ROI would be ((80/100)-1)*100 = -20%

case 3:

WHAT HAPPENS IF I INVEST 0 and sell 100??

What's my ROI then?? (logically)

[#10]

Logically, then ROI is infinity.