Formula help

[Scrumpywill]

Hello,

Could anyone please help, I have last years sales figures month by month and this years sales figures I would like a formula to compare sales percent increase or decreases per month and add a minus highlighting the percentage decrease

Many Thanks for your help and advice

Cheers

 

[r1998]

Hello
Data from a1 to e13 is this
[TABLE="width: 361"]
<colgroup><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Sales[/TD]
[TD]Date[/TD]
[TD]Sales[/TD]
[TD]% Profit[/TD]
[/TR]
[TR]
[TD="align: right"]Apr-17[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]Apr-18[/TD]
[TD="align: right"]9000[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD="align: right"]May-17[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]May-18[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]-25[/TD]
[/TR]
[TR]
[TD="align: right"]Jun-17[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]Jun-18[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD="align: right"]Jul-17[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]Jul-18[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]-20[/TD]
[/TR]
[TR]
[TD="align: right"]Aug-17[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]Aug-18[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]150[/TD]
[/TR]
[TR]
[TD="align: right"]Sep-17[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]Sep-18[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD="align: right"]Oct-17[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]Oct-18[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]-60[/TD]
[/TR]
[TR]
[TD="align: right"]Nov-17[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]Nov-18[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD="align: right"]Dec-17[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]Dec-18[/TD]
[TD="align: right"]9000[/TD]
[TD="align: right"]800[/TD]
[/TR]
[TR]
[TD="align: right"]Jan-18[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]Jan-19[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]-75[/TD]
[/TR]
[TR]
[TD="align: right"]Feb-18[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]Feb-19[/TD]
[TD="align: right"]7000[/TD]
[TD="align: right"]133.3333[/TD]
[/TR]
[TR]
[TD="align: right"]Mar-18[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]Mar-19[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]-40[/TD]
[/TR]
</tbody>[/TABLE]
in e2 use this formula n then copy down till e13

=D2*100/B2-100

 

[Scrumpywill]

That’s cool, how would I format the percentage cell in e so it’s either 1 to 100 as an increase or -1 to -100 for a decrease

Currently using =(d2-d2)/abs(d2)

What do you think

Thanks again

 

[r1998]

Column E is already showing percentage
Sorry I am not able to understand your question
U want to add % sign in column E ?

 

[Scrumpywill]

Yes please - what result would the ABS example do

 

[r1998]

ok finally i understood the problem
u want the profit and loss % only from 0 to 100
For example if data is like this, starting from a1 till h13
[TABLE="width: 932"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Sales[/TD]
[TD]Date[/TD]
[TD]Sales[/TD]
[TD]% Profit[/TD]
[TD]% between 0 to 100[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apr-17[/TD]
[TD]5000[/TD]
[TD]Apr-18[/TD]
[TD]-6000[/TD]
[TD]-220[/TD]
[TD]-1.980000792[/TD]
[TD]Ending point ( means 100 %)[/TD]
[TD]6566.666667[/TD]
[/TR]
[TR]
[TD]May-17[/TD]
[TD]4000[/TD]
[TD]May-18[/TD]
[TD]3000[/TD]
[TD]-25[/TD]
[TD]-0.22500009[/TD]
[TD]Starting point ( means 0 %)[/TD]
[TD]-4544.44[/TD]
[/TR]
[TR]
[TD]Jun-17[/TD]
[TD]1000[/TD]
[TD]Jun-18[/TD]
[TD]6000[/TD]
[TD]500[/TD]
[TD]4.5000018[/TD]
[TD]Range[/TD]
[TD]11111.10667[/TD]
[/TR]
[TR]
[TD]Jul-17[/TD]
[TD]3000[/TD]
[TD]Jul-18[/TD]
[TD]200000[/TD]
[TD]6566.666667[/TD]
[TD]59.10002364[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Aug-17[/TD]
[TD]2000[/TD]
[TD]Aug-18[/TD]
[TD]5000[/TD]
[TD]150[/TD]
[TD]1.35000054[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sep-17[/TD]
[TD]1000[/TD]
[TD]Sep-18[/TD]
[TD]3000[/TD]
[TD]200[/TD]
[TD]1.80000072[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oct-17[/TD]
[TD]5000[/TD]
[TD]Oct-18[/TD]
[TD]-222222[/TD]
[TD]-4544.44[/TD]
[TD]-40.89997636[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nov-17[/TD]
[TD]2000[/TD]
[TD]Nov-18[/TD]
[TD]6000[/TD]
[TD]200[/TD]
[TD]1.80000072[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dec-17[/TD]
[TD]1000[/TD]
[TD]Dec-18[/TD]
[TD]9000[/TD]
[TD]800[/TD]
[TD]7.20000288[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jan-18[/TD]
[TD]4000[/TD]
[TD]Jan-19[/TD]
[TD]1000[/TD]
[TD]-75[/TD]
[TD]-0.67500027[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Feb-18[/TD]
[TD]3000[/TD]
[TD]Feb-19[/TD]
[TD]7000[/TD]
[TD]133.3333333[/TD]
[TD]1.20000048[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mar-18[/TD]
[TD]5000[/TD]
[TD]Mar-19[/TD]
[TD]3000[/TD]
[TD]-40[/TD]
[TD]-0.360000144[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

in e2 enter formula n drag down till e13

=D2*100/B2-100

in h2 enter formula

=MAX(E2:E13)

in h3 enter formula

=MIN(E2:E13)

in h4 enter formula

=H2-(H3)

in f2 enter formula n drag down till f13

=E2*100/$H$4

 

[Scrumpywill]

Brilliant!? could you show me conditional formatting to show the sales decrease-

Many thanks for your help

 

[r1998]

Dear friend Scrumpywill,
the formula in f2 is wrong
for this data its giving wrong results
[TABLE="class: cms_table, width: 932"]
<tbody>[TR]
[TD]Jul-17[/TD]
[TD]3000[/TD]
[TD]Jul-18[/TD]
[TD]200000[/TD]
[TD]6566.666667[/TD]
[TD]59.10002364
[/TD]
[/TR]
</tbody>[/TABLE]
it must be 100 % but its giving 59.1 %

 

[r1998]

ok finally i figured out
in f2 use this formula n drag till f13

=((E2-$H$3)*100)/$H$4

 

[YusufAkhtar]

I am getting 98.87 as a result with the above formula.

[TABLE="width: 553"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Date [/TD]
[TD]Sales 2017[/TD]
[TD]Date[/TD]
[TD]Sales 2018[/TD]
[TD]Profit/Loss (in values)[/TD]
[TD]Profit/Loss (in %)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Apr-17[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]Apr-18[/TD]
[TD="align: right"]9000[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]1.204516939[/TD]
[TD="align: right"]6566.7[/TD]
[/TR]
[TR]
[TD="align: right"]May-17[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]May-18[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]-25[/TD]
[TD="align: right"]-0.376411543[/TD]
[TD="align: right"]-75[/TD]
[/TR]
[TR]
[TD="align: right"]Jun-17[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]Jun-18[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]7.528230866[/TD]
[TD="align: right"]6641.7[/TD]
[/TR]
[TR]
[TD="align: right"]Jul-17[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]Jul-18[/TD]
[TD="align: right"]200000[/TD]
[TD="align: right"]6566.666667[/TD]
[TD="align: right"]98.87076537[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Aug-17[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]Aug-18[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]2.25846926[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Sep-17[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]Sep-18[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]3.011292346[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Oct-17[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]Oct-18[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]-60[/TD]
[TD="align: right"]-0.903387704[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Nov-17[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]Nov-18[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]3.011292346[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Dec-17[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]Dec-18[/TD]
[TD="align: right"]9000[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]12.04516939[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Jan-18[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]Jan-19[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]-75[/TD]
[TD="align: right"]-1.12923463[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Feb-18[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]Feb-19[/TD]
[TD="align: right"]7000[/TD]
[TD="align: right"]133.3333333[/TD]
[TD="align: right"]2.007528231[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Mar-18[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]Mar-19[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]-40[/TD]
[TD="align: right"]-0.602258469[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]