• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

If condition help

V

vishwas

New Member
Hi,

Kindly help with one if formula in the attached sheet
Column E, F & G are different years revenue and Column H is the difference , which explains whether revenue has increased or decreased in 2017 when compared to 2016 & 2015 . We are trying to classify the revenue difference by comparing 3 years and applying the correct category to explain the difference. Excel file is attached. I need one formula which will provide the right category based on the conditions

Kindly help

Thank you
 

Attachments

  • calculation.xlsx
    11.4 KB · Views: 5
Hi ,

You have mentioned 7 status descriptions ; are these the only possible ones , or will there be more ?

It is clear your data is not real-life data ; will realistic data change any of the rules ?

1. What is the difference between a loss and a downtrade ?

2. What is the same status is applicable in 2 years ?

3. What decides the priority ? For instance the values 0 , 1 , 0 can refer to either of the following status descriptions :

New in 2016

Loss in 2017

It will be better if you can upload a workbook with realistic data , and explain the logic for deriving the status descriptions in more detail.

Narayan
 
NARAYANK991 said:
Hi ,

Hi Narayan

Thank you for replying.I will explain below answers to your questions.

All revenue mentioned will be total financial year revenues.
Actual comparison is between 2016 and 2017. But for analyzing the difference for some conditions 3 years are considered.

Loss in 2016 = There was revenue in 2015 and it dropped [ no specific percentage] in 2016 and after that there is no revenue at all . 2017 it is zero.

loss in 2017 = There was revenue in 2016 and in 2017 it was zero

New in 2017 = No revenue in 2016, revenue only in 2017

New in 2016 = No revenue in 2015, revenue in 2016 and also continues in 2017

Uptrade in 2017 = Revenue in 2015 and 2016. Revenue in 2017 is higher than 2016

Churn = Revenue in 2017 it drops when compared to 2016. If the % drop between 2016 and 2017 is between -100% and -50%

Downtrade in 2017 = Revenue in 2017 it drops when compared to 2016 . If the % drop between 2016 and 2017 is between -49% and -1%

The data is a part of an analysis and can't be separated. I sincerely hope you will help me.

Thanks
Click to expand...
 

Attachments

  • calculation.xlsx
    11.8 KB · Views: 5
Last edited:
Hi Narayan and all the Ninjas,

In my last reply I have explained all the 7 conditions that will be considered in analyzing the variance in revenue between 2 financial years. The comparison is primarily between the 2016 and 2017. In some conditions, the review of 2015 is required.
Answering remaining questions

1. Are these the only conditions - There could be one more condition where there is no movement between 2016 & 2017. the variance in this case is zero, which in real life is most unlikely. In real-life the condition's won't change. This is the only possible condition used for the analysis.

This data as mentioned is the customer revenue data. The purpose is to make a waterfall graph for by explaining the variance between 2 years.

I thank in advance for your valuable time.

Regards
 
Hi Narayan

Thank you very much for your time. I will go through the file and will test check with the live data. If I see any new issues, I will reach out to you.

Thanks again for the timely help

Regards
 
You must log in or register to reply here.
Back
Top