Hi,
I am trying to create a formula which should take into account the negative and positive amounts stated in previous and current year. I have managed to create the formula to an extent, however, I am unable to do it for certain cases. These have been stated in the attachment.
The outcome from the current formula is highlighted in yellow and the comments have been stated in the adjacent column with the iterations.
Please help with rectifying the problem in the current formula.
TIA
This maybe?
=IFERROR(IF(AND(A4=0,B4=0),0,IF(AND(B4=0,A4>0),1,IF(OR(AND(B4=0,A4<0),AND(B4>0,A4=0)),-1,(B4-A4)/B4))),"NA")
Last edited by AliGW; 04-09-2019 at 05:38 AM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Thanks for this but it doesn't work in the case wherein the amount in the previous year was zero and current year is negative. it should show a de-growth of 100%.Originally Posted by AliGW
I amended the post - please look again.
Excel 2016 (Windows) 32 bit
Sample Data Current Year Previous Year Growth Comments This should be negative as the negative amount has increased This should be negative as the negative amount has increased I would need it to have this as a de-growth and not NA since it has gone from zero to negative Good! Good! Good! This should be positive growth as it went from negative to positive
Sheet: Sheet1
Thanks again but now if current year is negative and previous year is positive, it shoes a growth of 131% whereas, it should be de growth as it went from positive to negative. [Row 7]
Try this:
=IF(OR(AND(A4=0,B4<0),AND(B4=0,A4>0)),1,IF(OR(AND(A4=0,B4>0),AND(B4=0,A4<0)),-1,IF(AND(A4<0,B4>0),-(B4-A4)/B4,(B4-A4)/B4)))
Thanks and it works. However, there is one more iteration which I forgot to include and that is for both year positive number so for eg. Current year = 20,213 and previous year = 77,500, then de-growth should be -74% while the formula throws a growth of 74%. Similarly, current year = 465,348 and previous year = 25,00, then the growth should be 1761% while the formula shows -1761%.
Just add another section to the formula to cover this.
I tried with this:
=IF(OR(AND(A11=0,B11<0),AND(B11=0,A11>0)),1,IF(OR(AND(A11=0,B11>0),AND(B11=0,A11<0)),-1,IF(OR(AND(A11>0,B11>0),AND(B11>0,A11>0)),(B11-A11)/B11,IF(AND(A11<0,B11>0),-(B11-A11)/B11,(B11-A11)/B11))))
but didnt work out.
Please provide a new, more complete sample dataset if you want any further input from me.
I have attached the updated sheet with the formula not working for data highlighted in orange.
Last edited by AliGW; 04-09-2019 at 08:26 AM.
Sorry - I have run out of time. Hopefully someone else will have the time to help you today.
In different way : in "E4" : =A4-B4
In "F4"Formula:
Please Login or Register to view this content.
copy paste down.
File attach for your ready ref.
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
Thanks for this but i need to show with + and - signs only! can use word! Can you please help me with the original formula?In different way : in "E4" : =A4-B4
In "F4"Formula:Please Login or Register to view this content.
copy paste down.
File attach for your ready ref.
Finally got the formula myself! Thanks all for the help!
=IFERROR(IF(OR(AND(A4=0,B4<0),AND(B4=0,A4>0)),1,IF(OR(AND(A4=0,B4>0),AND(B4=0,A4<0)),-1,IF(AND(A4<0,B4>0),-(B4-A4)/B4,IF(AND(A4<0,B4<0),(B4-A4)/B4,IF(AND(A4>0,B4<0),(B4-A4)/B4,-(B4-A4)/B4))))),"NA")
There's no meaningful way to calculate YoY results when either period is negative. From the WSJ:
Net Income percent change is the change from the same period from a year ago. Percent change is not provided if either the latest period or the year-ago period contains a net loss. On the digest page, if a company posts a profit in the latest period against a loss in the year-ago period, the percent change is represented as a "P". Similarly, if a company posts a loss in the latest period against a profit in the year-ago period, the percent change is represented as a "L".
Last edited by shg; 04-09-2019 at 11:44 AM.
Entia non sunt multiplicanda sine necessitate
I had a rethink and came up with this:
=IF((A4-B4)>0,IFERROR(ABS((B4-A4)/B4),1),-IFERROR(ABS((A4-B4)/B4),1))
Excel 2016 (Windows) 32 bit
Sample Data Current Year Previous Year Growth
Sheet: Sheet1
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks