Growth Index That Works for Positive and Negative Values

[kalikj]

Can you please help with an Excel formula that will calculate a growth index that will work with negative as well as positive values.

For example:

Country A growth = 5%
Country B Growth = 7%

To calculate the index for country B growth Vs country A, we can easily calculate the growth index as follows:

(7%/5%)*100 = 140

The key principle using indexing is that the value must always be positive (>0) and needs to create a positive index measuring the relative position between two numbers. The porblems arise with this formula when indexing two negative values or a positive Vs a negative value, as illustrated below.

However, we cannot get the correct answer using this formula if for example:

Country A growth = -5%
Country B Growth = -3%

If we use the same formula: (-3%/-5%)*100 = 60 which is not the correct answer as country B's growth is actually better than country A's (in that country B is not declining as much as country A) and so should be returning an index > 100.

The same issue arises if:

Country A growth = -5%
Country B Growth = 3%

Again, if we use the simple formula: (3%/-5%)*100 = -60 which is again not the correct answer as country B's growth is better than country A's and so should be returning an index > 100


Can anyone please help with a solution that will calculate the relative growth index irrespective of positive or negative values for either country A or B.

Best regards,

Jason

 

[Gary's Student]

I think the basic idea of the index is flawed.

For example, if country A's growth is zero and country B's growth is 7%, what would you expect the index to be?

 

[kalikj]

On a practical level it is highly improbable that a growth rate will be absolute zero - even when the economists talk about "zero growth" they are "rounding" - if it actually was zero then the formula would contain logic to account for this and asign a value that would be in keeping with the intended "meaning" of the index.

Are you able to offer some practical assistance to the mathematical element of the problem on the basis that the index is intended to be indicative and show relative growth between two points irrespective of their values.

I thought I had cracked it using absolute references, but I have run into a glitch.

Assuming Country "A" value is in cell A2 and Country "B" value is in "B2", then I thought that the following formula would meet the brief:

=IF(AND(A2>0,B2<0),ABS(A2)/(ABS(A2)+ABS(B2-A2)),(ABS(A2)+(B2-A2))/ABS(A2))

This does work in most instances, except when both values are negative where country B value is < country A value. However I think the following addresses this point:

=IF(AND(A2>0,B2<0),ABS(A2)/(ABS(A2)+ABS(B2-A2)),IF(AND(A2<0,B2<0,B2<A2),(ABS(A2)-ABS(B2-A2))/ABS(A2),(ABS(A2)+(B2-A2))/ABS(A2)))


<a2),(abs(a2)-abs(b2-a2)) ABS(A2)))
What do you think?</a2),(abs(a2)-abs(b2-a2))>

 

[Gary's Student]

I believe you may be correct. I am not an economist and cannot judge the approach. However from the Excel perspective, we may have to "bite the bullet" and use a four quadrant approach. Something like:


=IF(AND(A2>0,B2>0),"first formula",IF(AND(A2<0,B2<0),"second formula",IF(AND(A2>0,B2<0),"third formula","fourth formula")))

where you remove the texts and replace them with the correct formula for that quadrant.

 

[Aterra]

Gary´s
Will you please state the four formula?

 

[D_Crickette]

Looking at previous posts and working through myself, Here's what I came up with:

=IF(AND(A2>0,B2>0),(A2/B2)*100,IF(AND(A2<0,B2<0),(ABS(B2)/ABS(A2))*100,IF(AND(A2>0,B2<0),(ABS(A2)/(ABS(A2)+ABS(B2-A2)))*100,IF(AND(A2<0,B2>0),((ABS(A2)+(B2-A2))/ABS(B2))*100,"-"))))

 

[D_Crickette]

The formulas for the A>0,B<0 and A<0,B>0 scenarios aren't quite right. This should be right (hopefully!):

=IF(AND(A2>0,B2>0),(A2/B2)*100,IF(AND(A2<0,B2<0),(ABS(B2)/ABS(A2))*100,IF(AND(A2>0,B2<0),((ABS(A2+(A2-B2))/ABS(B2)*100)),IF(AND(A2<0,B2>0),(ABS(A2)/(ABS(A2)+ABS(B2-A2)))*100,"-"))))

 

[funnypapers]

The formulas for the A>0,B<0 and A<0,B>0 scenarios aren't quite right. This should be right (hopefully!):

=IF(AND(A2>0,B2>0),(A2/B2)*100,IF(AND(A2<0,B2<0),(ABS(B2)/ABS(A2))*100,IF(AND(A2>0,B2<0),((ABS(A2+(A2-B2))/ABS(B2)*100)),IF(AND(A2<0,B2>0),(ABS(A2)/(ABS(A2)+ABS(B2-A2)))*100,"-"))))

Hi! Looking for something to solve the same problem. Can you explain your formulas here? I'm just not sure exactly what the third and fourth parts are doing mathematically. Thanks!

 

[kalikj]

The formulas for the A>0,B<0 and A<0,B>0 scenarios aren't quite right. This should be right (hopefully!):

=IF(AND(A2>0,B2>0),(A2/B2)*100,IF(AND(A2<0,B2<0),(ABS(B2)/ABS(A2))*100,IF(AND(A2>0,B2<0),((ABS(A2+(A2-B2))/ABS(B2)*100)),IF(AND(A2<0,B2>0),(ABS(A2)/(ABS(A2)+ABS(B2-A2)))*100,"-"))))

D-Crickette, apologies for not responding earlier as I missed your reply until the new message came through!

Your solution works perfectly on all the scenarios that I have tested.

My sincere thanks for your solution to a problem I have been pondering, on and off, for some considerable time!

 

[funnypapers]

D-Crickette, apologies for not responding earlier as I missed your reply until the new message came through!

Your solution works perfectly on all the scenarios that I have tested.

My sincere thanks for your solution to a problem I have been pondering, on and off, for some considerable time!

Hi kalik, are you able to explain what the formula is doing when A2 > 0, B2 < 0 and when A2 >0, B2 < 0?