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

 

[kalikj]

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

Hi Funnypapers,

Having revisited the formula once again (prompted by your question) I found that the result was not giving me the relationship expected for all the cases.
I have therefore. Reworked the formula as follows (which now gives me the indexed relationship that I would anticipate for each of the cases for “A” and “B”).

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

This assumes I am measuring the index for the figure in B2 Vs the figure in A2.

To understand the formula, simply break it down into its constituent parts and substitute actual values for the references for A2 & B2 (ignoring the “ABS” function.

The formula therefore breaks down into 4 “cases with the following formula for each:

1. IF(AND(A2>0,B2>0)): formula is: (B2/A2)*100

2. IF(AND(A2<0,B2<0)): formula is: (A2/B2)*100

3. IF(AND(A2>0,B2<0)): formula is: (A2+(A2-B2)-(A2-B2))/(A2+(A2-B2))*100

4. IF(AND(A2<0,B2>0)): formula is: ((B2-A2)/B2)*100

Have a play with some values using these formulae (ensuring that you comply with the relevant case for each, in terms of the values for A2 & B2) and you will be able to see the values being generated / understand what the formulae are doing, which is basically to establish the correct relationship between the two values in each case (allowing for the different combinations of positive Vs negative values), in order to calculate the correct INDEX (which is simply an expression of this relative relationship).