Pre populate report

[shiftyspina]

Hi

I want to pre-populate a summary based on values

I know i can write

Please Login or Register  to view this content.

but i want to be able to write the following

Gross margin has increased by 2% since last year, Purchase price has decreased by £1........

Can i build in a formula to determine whether to write increased or decreased? based on the below figures?

ACTUAL LAST YEAR DIFFERENCE
Gross Margin 5% 3% 2%
Purchase Price £9 £10 -£1

Say the values are in celss c2:d4 i can write

Please Login or Register  to view this content.

But how can i incorporate it all so that i can have the sentance i want and include the figures, condition and base?

Base: Gross margin has
Condition: Increased/Decreased
Base: by
Figure: 2%

Any help would be great

Thanks

Dan

[FDibbins]

Maybe something like this?

="Gross margin has "&if(A1<0,"Decreased","Increased")&" by "&A1

If that doesnt work for you, I suggest you upload a sample for me to look at

[shiftyspina]

Hi FDibbins, that works brillaintly thanks only one issue is that where it is telling me how much it has increased/decreased it is returning-1.00000000, i have checked the number of decimal places in the cells im looking at and they are all to 2 decimals, as its a text line im unsure whether or not decimals can be changed?

Dan

[shiftyspina]

Not to worry i changed the &a1 to &text(a1,"£0.00") and it works great

Thanks for your help perfect!!!!

[FDibbins]

Happy to help, I figured that woud get you heading in the right direction Thanks for the feedback

[shiftyspina]

You're welcome, I started to try and was using the "s to try and pick up the cell value, which doesn't work, and didnt realise you can combine &s which I use for text strings and cells so this has saved hours re writing weekly results :-) its just being careful to include text"&" "&"text"&" "&cell etc, but that's brilliant help I really appreciate it thanks again :-)