Rate function with #Num! result

[PA]

Col"A" B C D E F

Y1 Y2 Y3 Y4 Y1-Y4
Revenue (13) (4) - 1 #NUM!


Dear All, I try to use function =Rate to Calculate % at cell F4
Cell F4 =IF(E4=0,0,RATE(3,,-1,((+E4-B4)/B4)+1))
The Result is #Num!.

Appreciate anyone can help with modify formula on this?

Many Thanks & Best Regards

PA

 

[excelent]

I think B4 is the problem - how did u format the ()?


"PA" skrev:

 

[PA]

Hello excelent,

Yes, B4 is the problem.actually the description should be Profit(loss)..B4
=Loss -13 (minus 13) at Y1, C4 =minus 4, D4= nil(breakeven point), E4=+ 1
(profit)..

This formula -->Rate try to calculate average growth of profit/loss from Y1
(minu 13) to Y4 ( positive 1).I can get the % using try and Error ,result is
minus 142.5% (minus try to make the negative to positive 1 at Y4), but use
Rate function it will not work.

Here is my Try and Error calculation

Y4= -13 * (1 + (-142.5%)^3= +1


Appreciate if any one have any idea or other function that will result same
result without using try and error method?

Many thanks

PA