Formula for Comparisons and Subtraction

Glasgowsmile

Board Regular
Joined
Apr 14, 2018
Messages
234
Office Version
  1. 365
Platform
  1. Windows
Good Morning,

I've posted a photo so you can see what I'm doing but basically...

I want to compare two columns and then subtract the numbers next to them if they match.

I want to compare column K and column N, find the matches and then subtract column L from column O to paste into column Q.

https://imgur.com/kwG55fr
bJdyMIt

kwG55fr
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,342
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
may be


Book1
KLMNOPQ
1Comparison
2A12B610
3B10D44
4C5C8-3
5D9A25
6E8E53
7F7F16
8G3H10
9H6I75
10I5G3-2
Sheet2
Cell Formulas
RangeFormula
Q2=L2-INDEX($O$2:$O$10,MATCH(K2,$N$2:$N$10,0))
 

Glasgowsmile

Board Regular
Joined
Apr 14, 2018
Messages
234
Office Version
  1. 365
Platform
  1. Windows
This is great, how could I expand this if I increase the data set?

For example, let's say K:L are Week 1, N:O are Week 2. What if I wanted to add a Week 3 / 4 and it compares across all 4 weeks what the changes are?
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,342
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
it's basically just copy the formula across


Book1
JKLMNOPQRSTUVWX
1Comparison
2Wk1Wk2Wk3Wk4Wk1 > Wk2Wk2 > Wk3Wk3 > Wk4
3A12B6A6B1110-45
4B10D4B1D1145-10
5C5C8C2C5-36-3
6D9A2D9A15-5-2
7E8E5E6E13-15
8F7F1F9F116-8-2
9G3H1G10H10-77
10H6I7H12I105-1111
11I5G3I4G3-23-6
Sheet1
Cell Formulas
RangeFormula
V3=INDEX($K$3:$K$11,MATCH($J3,$J$3:$J$11,0))-INDEX($N$3:$N$11,MATCH($J3,$M$3:$M$11,0))
W3=INDEX($N$3:$N$11,MATCH($J3,$M$3:$M$11,0))-INDEX($Q$3:$Q$11,MATCH($J3,$P$3:$P$11,0))
X3=INDEX($Q$3:$Q$11,MATCH($J3,$P$3:$P$11,0))-INDEX($T$3:$T$11,MATCH($J3,$S$3:$S$11,0))
 

Glasgowsmile

Board Regular
Joined
Apr 14, 2018
Messages
234
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Not quite what I had in mind, what if I wanted a column that was Week 1 vs Week 2 Comparison and then another Column that compared Week 1 to Week 2 & 3, and then a 3rd that compared Week 1 to 2, 3, & 4?
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,342
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Not quite what I had in mind, what if I wanted a column that was Week 1 vs Week 2 Comparison and then another Column that compared Week 1 to Week 2 & 3, and then a 3rd that compared Week 1 to 2, 3, & 4?

say for A

Wk1 10
Wk2 9
Wk3 8
Wk4 7

what results are you expected for compared Week 1 to Week 2 & 3, and
Week 1 to 2, 3, & 4 ?
 

Glasgowsmile

Board Regular
Joined
Apr 14, 2018
Messages
234
Office Version
  1. 365
Platform
  1. Windows
For example for A, I'm trying to see how A has grown or declined over the last 3 weeks compared to week 1 - so I want to compared week 1 against all other weeks to pull A-G, in this example, so I can see how it stacks up over time.
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,342
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
ok, have a look of this


Book1
JKLMNOPQRSTUVWX
1Comparison
2Wk1Wk2Wk3Wk4Wk2Wk3Wk4
3A12B6A6B1110611
4B10D4B1D1149-1
5C5C8C2C5-330
6D9A2D9A150-2
7E8E5E6E1327
8F7F1F9F116-2-4
9G3H1G10H10-70
10H6I7H12I105-65
11I5G3I4G3-21-5
Sheet1
Cell Formulas
RangeFormula
V3=INDEX($K$3:$K$11,MATCH($J3,$J$3:$J$11,0))-INDEX($N$3:$N$11,MATCH($J3,$M$3:$M$11,0))
W3=INDEX($K$3:$K$11,MATCH($J3,$J$3:$J$11,0))-INDEX($Q$3:$Q$11,MATCH($J3,$P$3:$P$11,0))
X3=INDEX($K$3:$K$11,MATCH($J3,$J$3:$J$11,0))-INDEX($T$3:$T$11,MATCH($J3,$S$3:$S$11,0))
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,598
Messages
5,832,645
Members
430,150
Latest member
amitk1

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
Top