• 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.

Negative Margin

Brandy_M

Brandy_M

New Member
I followed Chandoo's cool tips to use conditional formatting to show a change in margin from the previous month. The problem that I am having is that if there is a negative margin one month and the next month it increases to an even large negative margin, then the icon is showing green with an up arrow. How can I correctly reflect when margin is decreasing when it is negative?
 
Hi ,

I do not know which blog post or formula you are referring to ; however , my impression is that change is always calculated as the difference between the current month and the previous month ; thus if the current month is more positive than the previous month , then the change is an improvement , else it is a deterioration.

Thus , if the current month is more negative than the previous month , the difference will be negative , and this means performance has deteriorated.

Any formula which works this way will always give the correct result.

Narayan
 
Here is the example.

All margin

Jan <179.84>
Feb <2478.65>
Mar <3365.49>
Apr <1665.61>

When I use the value field settings in the pivot table to show difference from % based on previous month it shows Feb has a 1278.25% increase with a green arrow, March has a 35.78% increase with a green arrow, and April 50.51 decrease with a red arrow based on the conditional formatting tips I followed in the Chandoo tutorial for showing performance with icons in a pivot table. I checked another customer that didn't have negative margins and the increase/decrease differences worked perfectly, but is not working with this poor performing customer's set of data. Any suggestions? Is the issue because it started off with a negative margin?
 
Hi ,

I don't understand ; the value 179.84 is a positive value or a negative value ?

What about the other values ?

Try this formula :

=SIGN(Previous_Month)*(Current_Month-Previous_Month)/Previous_Month

Narayan
 
they are all negative margins and the problem with using the % difference from previous amount field is that if the base item is negative then the % change can look wacky. I read online that a workaround is to use new value - old value/old value (abs) - but I don't know if that is possible in a pivot table if I am trying to calculate the change based on the previous month.
 
see attached. The change column is based on using value field settings difference from % previous month. Is there a way to keep the same layout, but put in a calculated field or something that will allow the SIGN or ABS option?
 

Attachments

Hi ,

It would help if you could upload the workbook with the sample data instead of a picture within a workbook.

Narayan
 
see attached. I appreciate your help. My ultimate goal is to use the handy trick showed in Chandoo's blog to show the % change from month to month with an icon arrow showing up or down. This becomes an issue when the base item is negative and I am wondering if there is a work around in the pivot table that works like the ABS or SIGN formula.
 

Attachments

Yes, that is what I am looking for. Last question is that I have a large group of customers and I am tracking them month over month and across different product lines. For example, I have 100 customers with 5 different products with data from Oct 13 to April 14. I would love to not have to sort the data to be able to setup the change column which is looking at the previous row. Is there a formula you can think of that will look for the same customer with the same product in the previous month to calculate the change so that I can easily dump in new data each month that is unsorted and still get a result?
 
it will be in month/year order and each month I will drop the most recent month's data at the end of the spreadsheet and update the pivot. It will also be in company name order after the date order, but the product may not be in order.
 
You must log in or register to reply here.
Back
Top