Formula help

S

Scrumpywill

New Member
Joined
Jul 28, 2018
Messages
23
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
 
Sort by date Sort by votes
Hello YusufAkhtar and welcome to MRExcel forum :)
my formula in cell f5 is
Code: 
=((E5-$H$3)*100)/$H$4
and i am getting 100 value in f5
 
Last edited:
Upvote 0

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
If I have grand total figures last year and this year of the last quarter I want to see the increases & decrease as a percentage of each total - what’s the best formula

Thanks again for your help
 
Upvote 0
Hello Scrumpywill :biggrin:
Sorry but I am not able to understand your question without data :(
 
Upvote 0
[TABLE="width: 751"]
<tbody>[TR]
[TD="width: 221, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent, align: right"]Apr-17
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]May-17
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]Jun-17
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]Jul-17
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]Aug-17
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]Apr-18
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]May-18
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]Jun-18
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]Jul-18
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]Aug-18
[/TD]
[TD="width: 76, bgcolor: transparent"]Grand Total
[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Show stoppers
[/TD]
[TD="bgcolor: transparent, align: right"]833
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]974
[/TD]
[TD="bgcolor: transparent, align: right"]785
[/TD]
[TD="bgcolor: transparent, align: right"]1,701
[/TD]
[TD="bgcolor: transparent, align: right"]736
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]709
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]1,194
[/TD]
[TD="bgcolor: transparent, align: right"]6,932
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2017-2018
[/TD]
[TD="bgcolor: transparent, align: right"]833
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]974
[/TD]
[TD="bgcolor: transparent, align: right"]785
[/TD]
[TD="bgcolor: transparent, align: right"]1,701
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]4,293
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2018-2019
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]736
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]709
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]1,194
[/TD]
[TD="bgcolor: transparent, align: right"]2,638
[/TD]
[TD="align: right"]-39%
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Card type
[/TD]
[TD="bgcolor: transparent, align: right"]995
[/TD]
[TD="bgcolor: transparent, align: right"]827
[/TD]
[TD="bgcolor: transparent, align: right"]1,445
[/TD]
[TD="bgcolor: transparent, align: right"]1,278
[/TD]
[TD="bgcolor: transparent, align: right"]2,225
[/TD]
[TD="bgcolor: transparent, align: right"]885
[/TD]
[TD="bgcolor: transparent, align: right"]852
[/TD]
[TD="bgcolor: transparent, align: right"]720
[/TD]
[TD="bgcolor: transparent, align: right"]784
[/TD]
[TD="bgcolor: transparent, align: right"]1,109
[/TD]
[TD="bgcolor: transparent, align: right"]11,119
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2017-2018
[/TD]
[TD="bgcolor: transparent, align: right"]995
[/TD]
[TD="bgcolor: transparent, align: right"]827
[/TD]
[TD="bgcolor: transparent, align: right"]1,445
[/TD]
[TD="bgcolor: transparent, align: right"]1,278
[/TD]
[TD="bgcolor: transparent, align: right"]2,225
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]6,769
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2018-2019
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]885
[/TD]
[TD="bgcolor: transparent, align: right"]852
[/TD]
[TD="bgcolor: transparent, align: right"]720
[/TD]
[TD="bgcolor: transparent, align: right"]784
[/TD]
[TD="bgcolor: transparent, align: right"]1,109
[/TD]
[TD="bgcolor: transparent, align: right"]4,350
[/TD]
[TD="align: right"]-36%
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]weld a type
[/TD]
[TD="bgcolor: transparent, align: right"]305
[/TD]
[TD="bgcolor: transparent, align: right"]429
[/TD]
[TD="bgcolor: transparent, align: right"]402
[/TD]
[TD="bgcolor: transparent, align: right"]533
[/TD]
[TD="bgcolor: transparent, align: right"]721
[/TD]
[TD="bgcolor: transparent, align: right"]1,357
[/TD]
[TD="bgcolor: transparent, align: right"]751
[/TD]
[TD="bgcolor: transparent, align: right"]654
[/TD]
[TD="bgcolor: transparent, align: right"]461
[/TD]
[TD="bgcolor: transparent, align: right"]871
[/TD]
[TD="bgcolor: transparent, align: right"]6,483
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2017-2018
[/TD]
[TD="bgcolor: transparent, align: right"]305
[/TD]
[TD="bgcolor: transparent, align: right"]429
[/TD]
[TD="bgcolor: transparent, align: right"]402
[/TD]
[TD="bgcolor: transparent, align: right"]533
[/TD]
[TD="bgcolor: transparent, align: right"]721
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]2,389
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2018-2019
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]1,357
[/TD]
[TD="bgcolor: transparent, align: right"]751
[/TD]
[TD="bgcolor: transparent, align: right"]654
[/TD]
[TD="bgcolor: transparent, align: right"]461
[/TD]
[TD="bgcolor: transparent, align: right"]871
[/TD]
[TD="bgcolor: transparent, align: right"]4,093
[/TD]
[TD="bgcolor: transparent, align: right"]71%
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jon 2
[/TD]
[TD="bgcolor: transparent, align: right"]780
[/TD]
[TD="bgcolor: transparent, align: right"]780
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]780
[/TD]
[TD="bgcolor: transparent, align: right"]780
[/TD]
[TD="bgcolor: transparent, align: right"]780
[/TD]
[TD="bgcolor: transparent, align: right"]780
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]780
[/TD]
[TD="bgcolor: transparent, align: right"]819
[/TD]
[TD="bgcolor: transparent, align: right"]6,279
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2017-2018
[/TD]
[TD="bgcolor: transparent, align: right"]780
[/TD]
[TD="bgcolor: transparent, align: right"]780
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]780
[/TD]
[TD="bgcolor: transparent, align: right"]780
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]3,120
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2018-2019
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]780
[/TD]
[TD="bgcolor: transparent, align: right"]780
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]780
[/TD]
[TD="bgcolor: transparent, align: right"]819
[/TD]
[TD="bgcolor: transparent, align: right"]3,159
[/TD]
[TD="bgcolor: transparent, align: right"]1%
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]shapadowa
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]450
[/TD]
[TD="bgcolor: transparent, align: right"]769
[/TD]
[TD="bgcolor: transparent, align: right"]653
[/TD]
[TD="bgcolor: transparent, align: right"]467
[/TD]
[TD="bgcolor: transparent, align: right"]587
[/TD]
[TD="bgcolor: transparent, align: right"]2,926
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2018-2019
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]450
[/TD]
[TD="bgcolor: transparent, align: right"]769
[/TD]
[TD="bgcolor: transparent, align: right"]653
[/TD]
[TD="bgcolor: transparent, align: right"]467
[/TD]
[TD="bgcolor: transparent, align: right"]587
[/TD]
[TD="bgcolor: transparent, align: right"]2,926
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]rgv
[/TD]
[TD="bgcolor: transparent, align: right"]1,047
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]875
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]265
[/TD]
[TD="bgcolor: transparent, align: right"]899
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]510
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]410
[/TD]
[TD="bgcolor: transparent, align: right"]4,005
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2017-2018
[/TD]
[TD="bgcolor: transparent, align: right"]1,047
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]875
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]265
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]2,186
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2018-2019
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]899
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]510
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]410
[/TD]
[TD="bgcolor: transparent, align: right"]1,819
[/TD]
[TD="align: right"]-17%
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]sample six
[/TD]
[TD="bgcolor: transparent, align: right"]180
[/TD]
[TD="bgcolor: transparent, align: right"]180
[/TD]
[TD="bgcolor: transparent, align: right"]360
[/TD]
[TD="bgcolor: transparent, align: right"]360
[/TD]
[TD="bgcolor: transparent, align: right"]180
[/TD]
[TD="bgcolor: transparent, align: right"]300
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]480
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]240
[/TD]
[TD="bgcolor: transparent, align: right"]2,280
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2017-2018
[/TD]
[TD="bgcolor: transparent, align: right"]180
[/TD]
[TD="bgcolor: transparent, align: right"]180
[/TD]
[TD="bgcolor: transparent, align: right"]360
[/TD]
[TD="bgcolor: transparent, align: right"]360
[/TD]
[TD="bgcolor: transparent, align: right"]180
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]1,260
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2018-2019
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]300
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]480
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]240
[/TD]
[TD="bgcolor: transparent, align: right"]1,020
[/TD]
[TD="align: right"]-19%
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
hi can you see what im trying to analyse from this sample and does it look correct using the ABS formula

formula is =(L5-L4)/ABS(L4)

cheers
 
Last edited:
Upvote 0
Hello Scrumpywill :)
in N3 use this formula and drag down till N21
Code: 
=(L3*100/L2)-100
this will give the % increase or decrease
 
Upvote 0
Do you think this is better than the abs formula? Only you don’t get a decimal point in the formatting text & percent % in the text
 
Upvote 0
As long as we are getting the correct answer, both seem to be fine :biggrin:
if you want a decimal in your answer, you can format the cell as Percentage with 2 decimal places, so then you will have 2 decimals along with the % sign too :beerchug:
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Calculate last 12 months sales figures
Replies
5
Views
170
MARK858
MARK858
D
  • Question Question
Divide Current Month into weeks
Replies
3
Views
305
Armstrong_N14
A
J
  • Question Question
Increase negative % or decrease positive %
Replies
1
Views
298
Snakehips
Snakehips
E
  • Question Question
SUMPRODUCT to pick up more than 1 column
Replies
2
Views
284
FormR
FormR
K
  • Question Question
Equitable Pricing Model
Replies
3
Views
84
kachauya
K

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top