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

Date	Sales	Date	Sales	% Profit
Apr-17	5000	Apr-18	9000	80
May-17	4000	May-18	3000	-25
Jun-17	1000	Jun-18	6000	500
Jul-17	5000	Jul-18	4000	-20
Aug-17	2000	Aug-18	5000	150
Sep-17	1000	Sep-18	3000	200
Oct-17	5000	Oct-18	2000	-60
Nov-17	2000	Nov-18	6000	200
Dec-17	1000	Dec-18	9000	800
Jan-18	4000	Jan-19	1000	-75
Feb-18	3000	Feb-19	7000	133.3333
Mar-18	5000	Mar-19	3000	-40

<colgroup><col><col><col><col span="2"></colgroup><tbody>
</tbody>

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

Date	Sales	Date	Sales	% Profit	% between 0 to 100
Apr-17	5000	Apr-18	-6000	-220	-1.980000792	Ending point ( means 100 %)	6566.666667
May-17	4000	May-18	3000	-25	-0.22500009	Starting point ( means 0 %)	-4544.44
Jun-17	1000	Jun-18	6000	500	4.5000018	Range	11111.10667
Jul-17	3000	Jul-18	200000	6566.666667	59.10002364
Aug-17	2000	Aug-18	5000	150	1.35000054
Sep-17	1000	Sep-18	3000	200	1.80000072
Oct-17	5000	Oct-18	-222222	-4544.44	-40.89997636
Nov-17	2000	Nov-18	6000	200	1.80000072
Dec-17	1000	Dec-18	9000	800	7.20000288
Jan-18	4000	Jan-19	1000	-75	-0.67500027
Feb-18	3000	Feb-19	7000	133.3333333	1.20000048
Mar-18	5000	Mar-19	3000	-40	-0.360000144

<tbody>
</tbody>

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

Jul-17

3000

Jul-18

200000

6566.666667

59.10002364

<tbody>
</tbody>

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.

Date	Sales 2017	Date	Sales 2018	Profit/Loss (in values)	Profit/Loss (in %)
Apr-17	5000	Apr-18	9000	80	1.204516939	6566.7
May-17	4000	May-18	3000	-25	-0.376411543	-75
Jun-17	1000	Jun-18	6000	500	7.528230866	6641.7
Jul-17	3000	Jul-18	200000	6566.666667	98.87076537
Aug-17	2000	Aug-18	5000	150	2.25846926
Sep-17	1000	Sep-18	3000	200	3.011292346
Oct-17	5000	Oct-18	2000	-60	-0.903387704
Nov-17	2000	Nov-18	6000	200	3.011292346
Dec-17	1000	Dec-18	9000	800	12.04516939
Jan-18	4000	Jan-19	1000	-75	-1.12923463
Feb-18	3000	Feb-19	7000	133.3333333	2.007528231
Mar-18	5000	Mar-19	3000	-40	-0.602258469

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>