How do I show that the difference between "1st" and "10th" is +9

slam

Well-known Member
Joined
Sep 16, 2002
Messages
921
Office Version
  1. 365
  2. 2019
I have two columns that contain positions 1st through 16th, (A2:A17 and D2:D17 respectively) and need to do a comparison between the two to show the difference in column I. In addition, I would like the cell shaded depending on whether there’s a negative, positive or neutral result.

A couple of examples of what I need:

A2 contains 1st, and D2 contains 10th, I would want I2 to display +9 and be shaded green
A8 contains 7th, and D8 contains 2nd, I would want I8 to display -5 and shaded green

If the two values match (16th and 16th), I would want a 0 displayed and the cell shaded yellow.

Any help would be greatly appreciated.

Thanks
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
I have two columns that contain positions 1st through 16th, (A2:A17 and D2:D17 respectively) and need to do a comparison between the two to show the difference in column I. In addition, I would like the cell shaded depending on whether there’s a negative, positive or neutral result.

A couple of examples of what I need:

A2 contains 1st, and D2 contains 10th, I would want I2 to display +9 and be shaded green
A8 contains 7th, and D8 contains 2nd, I would want I8 to display -5 and shaded green

If the two values match (16th and 16th), I would want a 0 displayed and the cell shaded yellow.

Any help would be greatly appreciated.

Thanks
Try this...

Sheet1


<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">1st</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">10th</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">+9</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">7th</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">2nd</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">-5</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">12th</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">12th</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">15th</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">3rd</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">-12</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">8th</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">14th</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">+6</TD></TR></TBODY></TABLE>

This formula entered in E2 and copied down:

=SUBSTITUTE(D2,RIGHT(D2,2),"")-SUBSTITUTE(A2,RIGHT(A2,2),"")

Format the cells as: +General;-General;0

You'll need to provide more detail about the color you want.
 
Last edited:
Upvote 0
Sorry, I don't think i explained well. I need the result to be calculated, rather than manually entered.

And I messed up on the colors. Negative values should be red. Positive values should be green. 0 values should be yellow.
 
Upvote 0
Formula you will need if you want to remove the "st" and "th" and do the calculation is:

=SUM((LEFT(D2,(LEN(D2)-2)))-(LEFT(A2,(LEN(A2)-2))))

This will not work if you have any numbers with out the "th" or "st"

Also condional formating will sort out the colour of the cells, which version of excel are you using?
 
Last edited:
Upvote 0
Sorry, I don't think i explained well. I need the result to be calculated, rather than manually entered.

And I messed up on the colors. Negative values should be red. Positive values should be green. 0 values should be yellow.
What version of Excel are you using?
 
Upvote 0
I have Excel 2003 at work, but that should be upgraded to 2010 within a week, and I have 2010 at home also, so I'll say 2010 :)
 
Upvote 0
Formula you will need if you want to remove the "st" and "th" and do the calculation is:

=SUM((LEFT(D2,(LEN(D2)-2)))-(LEFT(A2,(LEN(A2)-2))))

This will not work if you have any numbers with out the "th" or "st"

Also condional formating will sort out the colour of the cells, which version of excel are you using?

Cool, that works for part 1, but is it possible to show the positive numbers with a + in front of them?
 
Upvote 0

Forum statistics

Threads
1,224,600
Messages
6,179,834
Members
452,947
Latest member
Gerry_F

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