Assistance with Conditional formatting

[jazzkid]

I have a weight chart for a health care organisation and cannot figure one part of conditional formatting.


In the previous % weight gain or loss column (the document can be seen at https://docs.google.com/open?id=0BwL-nA3ga6NVUUN5UjJocEJKOUE ) I need to highlight if there is a weight gain or loss of -2% or less or +2% or more. I also want to stop any cells which do not have data being highlighted if possible.


Any assistance would be greatly appreciated.

 

[NARAYANK991]

Hi ,


Is this anything like what you are looking for ?


https://docs.google.com/open?id=0B0KMpuzr3MTVVkw3WS1FMHFDRXM


Narayan

 

[jazzkid]

Thanks Narayan - that was it. Will look at the formulas in the cells so I can do similarly in the future. Much appreciated.


Sue

 

[NARAYANK991]

Hi ,


Check out the revised file here :


https://docs.google.com/open?id=0B0KMpuzr3MTVV09KQVlXNlk4dG8


I have removed the ABS function from the formulae in column D ; the ABS is however there for the CF.


Narayan

 

[sgmpatnaik]

 

[jazzkid]

Thanks again Narayan . If you look at the original, where the weight has decreased from one month to the next, the % change was show as a negative number. I notice with the last update you did, this has occurred for a few of the months but not all. It would be better if this feature could be retained if at all possible. If you look at September 2012 for the original chart and the chart you have revised (last revision) the % change is -11.79 in the original and 11.79 (inferring an increase) in your last revision.


Thanks for the assistance and if this change could be made that would be great.


Sue

 

[NARAYANK991]

Hi Sue ,


Can you make the changes yourself ?


What I have seen is that a decrease of weight is being taken as positive ; while a weight gain is taken as negative ; if we take the formula in D21 , it is :


=IF(OR(C20=0,C21=0),0,(C20-C21)/C20)


If you change this to :


=IF(OR(C20=0,C21=0),0,(C21-C20)/C20)


you will get the results you want. Copy this changed formula everywhere.


The CF formulae need not change , since it is using the ABS function.


Please note that the formula in D13 is different from the formulae in D14:D25 ; when copying , do not copy the formulae across all these cells ; make the change in D13 individually , make it again in D14 , and then copy the formula in D14 to the cells D15 through D25.


Narayan

 

[jazzkid]

Thanks Narayan. I thought it was something like that - had not analysed it sufficiently to pick up that subtle difference. Will make the alterations. Thanks again - and for the instructions - most helpful. That's why you're a NINJA and I'm a member (pupil in my books! - especially in formula development)

You are right that, on occasions the loss of weight will be positive, however, when someone is underweight, the loss is not at all positive. So to keep it in perspective, the sheet was set up so that any loss is negative, and gain is positive.