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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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
236
MARK858
MARK858
D
  • Question Question
Divide Current Month into weeks
Replies
3
Views
334
Armstrong_N14
A
L
  • Question Question
Conditional formatting using icon set to show trend AND colour coding of cell based on a %
Replies
1
Views
205
etaf
etaf
Y
  • Question Question
Multi tiered commission structure formula help
Replies
2
Views
128
Dave Patton
D
J
  • Question Question
Increase negative % or decrease positive %
Replies
1
Views
316
Snakehips
Snakehips

Forum statistics

Threads
1,224,824
Messages
6,181,187
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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