Formula returning incorrect results

[Rob_010101]

Hello,

Apologies but my work won't allow XL2BB for security reasons.

The formula in column G is:

=IFERROR(SUM(F9-C9)/F9, "")

This returns a percentage increase or decrease between the figures in columns F and C.

However, the exact same formula in column J:

=IFERROR(SUM(I9-F9)/I9, "")

Which is supposed to return a percentage increase or decrease between the figures in columns I and F, returns incorrect values in all the rows.

The figures are custom formatted as follows: 0.00% "increase";0.00% "decrease";- but removing this doesn't change the result.

I'm stumped, can anyone help?

Thanks

 

[Joe4]

Everything looks to be working correctly to me. Note that the formula in column J is NOT exactly the same as the formula in column G.
Column G is looking at columns F and C, with column J is looking at columns I and F.

If things are not working the way you expect, please tell us that your expected value for cell G9 is, and explain the logic on how you get there.

Also, the SUM function in your formula serves no purpose whatsoever. The SUM function is used to sum (add up) a range of cells. It serves no purpose in simple subtraction and is not necessary.
Just use:

=IFERROR((F9-C9)/F9, "")

or

=IF(F9=0,"",(F9-C9)/F9)

 

[KRice]

I'm assuming, based on the column headings, that the desired formulas are:

=IFERROR((F9-C9)/C9, "")

=IFERROR((I9-F9)/F9, "")

Then the percentage basis is taken relative to the previous year, not the current year.

 

[Rob_010101]

Everything looks to be working correctly to me. Note that the formula in column J is NOT exactly the same as the formula in column G.
Column G is looking at columns F and C, with column J is looking at columns I and F.

If things are not working the way you expect, please tell us that your expected value for cell G9 is, and explain the logic on how you get there.

Also, the SUM function in your formula serves no purpose whatsoever. The SUM function is used to sum (add up) a range of cells. It serves no purpose in simple subtraction and is not necessary.
Just use:

=IFERROR((F9-C9)/F9, "")

or

=IF(F9=0,"",(F9-C9)/F9)

Hi Joe,

Thanks for your quick response. When I said exactly the same, I meant it is referencing this year vs last year's figures.

I would expect, for example, the figure in J13 to return "50% decrease" (as 7 is 50% of 14).

 

[Joe4]

Hi Joe,

Thanks for your quick response. When I said exactly the same, I meant it is referencing this year vs last year's figures.

I would expect, for example, the figure in J13 to return "50% decrease" (as 7 is 50% of 14).

Then look at Krice's response -- you are dividing by the wrong value!
It is not always a matter of just copying formulas from other columns - it is important to understand what you want to accomplish, and logically how the formulas want to be written.
The basis you want to use is always the number you will need to use in your divisor.

 

[Rob_010101]

I'm assuming, based on the column headings, that the desired formulas are:

=IFERROR((F9-C9)/C9, "")

=IFERROR((I9-F9)/F9, "")

Then the percentage basis is taken relative to the previous year, not the current year.

That's the trick! Looks like a combination of bad maths on my part and incorrect combination.

Thank you!

 

[Rob_010101]

Then look at Krice's response -- you are dividing by the wrong value!
It is not always a matter of just copying formulas from other columns - it is important to understand what you want to accomplish, and logically how the formulas want to be written.
The basis you want to use is always the number you will need to use in your divisor.

Agreed, incorrect combination (and bad maths on my part!)

Thanks for your help

 

[Joe4]

You are welcome.
Glad we were able to help!