Simple subtraction

[elersbuh]

I am trying use a simple subtraction formula on excel referencing values that appear using a vlookup.

As you can see in the image below, you can enter a point number in B36 and B37, the relevant easting and northing for both points will appear in adjacent cells. What I am trying to do is calculate the delta easting/northing between both points. It is a simple subtraction but when whenever I try to input the formula is does not work and I am not too sure why?

 

[Alex Blakenburg]

Numbers that are left aligned like you have in C36 & C37 are a strong indicator that Excel is seeing them as being text.
One option would be:

=VALUE(C37)-VALUE(C36)

It preferable to handle as close to the source as possible, so ideally the sheet you are looking up, if not there then in the vlookup (wrap the result in Value(Vlookup()), the above is the last resort.

 

[elersbuh]

Thanks for the tip. It has not worked yet and I think its because cells C36:D37 all have separate vlookup formulas in each of them and when referencing the cell to be subtracted its seeing the formula in the cell and not the value? I am pretty new to using excel like this so had to keep things as basic as I could but Im just not too sure how to go about it.

 

[Alex Blakenburg]

Ideally instal the Mr Excel XL2BB add-in and post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

In the meantime show us your vlookup formula in the same way you showed the subtraction formula and also show us the data where the coming from (again showing it both in the cell and the formula bar).

 

[elersbuh]

CPPSIS5060 EXCELL Assessment 1 Worksheet.xlsx
	A	B	C	D	E	F	G
23		Point	Eastings	Northings
24		1	567768	6376890
25		2	567702	6376035
26		3	567835	6377836
27		4	567936	6376471
28		5	568035	6376044
29		6	566834	6377837
30		7	567413	6378635
31		8	566036	6378999
32		9	568634	6377941
33		10	566345	6376534
34
35
36			FROM	TO
37		Point	6	5			TOTAL DISTANCE
38		Easting	566834	568035	Delta E
39		Northing	6377837	6376044	Delta N
Sheet1

Cell Formulas
Range	Formula
C38:D38	C38	=VLOOKUP(C37,Table2,2,FALSE)
C39:D39	C39	=VLOOKUP(C37,Table2,3,FALSE)

 

[elersbuh]

I need a formula of some sort in both cell F38 and F39 that can ignore the formula in those cells and subtract the values that appear in the relevant cells.

e.g if you enter any point number in cell C37 or D37 the E/N values will appear in the cells below.

 

[Alex Blakenburg]

It seems to be working for me.

Can you try and put the length formulas I have here and see if they match the length of the number (ie do you have any non-printing characters in your data that didn't come through in the XL2BB

Book2
	A	B	C	D	E	F	G
23		Point	Eastings	Northings
24		1	567768	6376890
25		2	567702	6376035
26		3	567835	6377836
27		4	567936	6376471
28		5	568035	6376044
29		6	566834	6377837
30		7	567413	6378635
31		8	566036	6378999
32		9	568634	6377941
33		10	566345	6376534
34
35
36			FROM	TO
37		Point	6	5			TOTAL DISTANCE
38		Easting	566834	568035	Delta E		1201
39		Northing	6377837	6376044	Delta N		-1793
40
41		Length
42		Easting	6	6
43		Northing	7	7
Sheet1

Cell Formulas
Range	Formula
C38:D38	C38	=VLOOKUP(C$37,Table2,2,FALSE)
C39:D39	C39	=VLOOKUP(C$37,Table2,3,FALSE)
G38:G39	G38	=D38-C38
C42:D43	C42	=LEN(C38)

 

[elersbuh]

CPPSIS5060 EXCELL Assessment 1 Worksheet.xlsx
	L	M	N	O	P	Q	R
23	Point	Eastings	Northings
24	1	567768	6376890
25	2	567702	6376035
26	3	567835	6377836
27	4	567936	6376471
28	5	568035	6376044
29	6	566834	6377837
30	7	567413	6378635
31	8	566036	6378999
32	9	568634	6377941
33	10	566345	6376534
34
35
36		FROM	TO
37	Point	6	5			TOTAL DISTANCE
38	Easting	566834	568035	Delta E		#VALUE!
39	Northing	6377837	6376044	Delta N		#VALUE!
40
41	Length
42	Easting	7	7
43	Northing	8	8
44
Sheet1

Cell Formulas
Range	Formula
M38:N38	M38	=VLOOKUP(C$37,Table2,2,FALSE)
M39:N39	M39	=VLOOKUP(C$37,Table2,3,FALSE)
Q38:Q39	Q38	=D38-C38
M42:N43	M42	=LEN(C38)

 

[elersbuh]

I copy and pasted your working onto excel and this the result. I havent changed anything from what you have done but there is obviously some issue with my excel program?

 

[Alex Blakenburg]

It's more likely your source data. Did you get it from the internet ?

What do get if you enter
=unicode(right(c38,1))