Degree minutes seconds multiplication in excel

February 9, 2018 at 07:56:09
Specs: Windows 10
write now i am doing the below calculation in scientific calculator.

in scientific calculator:-
8~55’0” * 0~4’0”+SHIFT+DMS

The output is 0~35’40”

the same i need in excel. could you please provide anybody. thanks in advance.


See More: Degree minutes seconds multiplication in excel

Report •

#1
February 9, 2018 at 11:52:17
See here for a couple of User Defined Functions (UDF) for converting DMS to Decimal and back.

https://support.microsoft.com/en-us...

You could do the calculations in multiple cells or combine the UDFs as follows:

=Convert_Degree(Convert_Decimal("8° 55' 0""")*(Convert_Decimal("0° 4' 0""")))

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#2
February 9, 2018 at 13:28:07
Just for fun, I "upgraded" the process. Try this:

In B1 I entered Alt-0176 <space> to get ° <space>
In B2 I entered '' <space> to get ' <space> (That's 2 single quotes and a space.)
In B3 I entered " to get " (Double quote)

In Column A1:A3 and A5:A7 I entered the values shown.

In A9 I entered the formula shown.

Notes:

1 - In B2 you need to enter 2 single quotes to get one single quote because the first single quote is seen by Excel as a Text designator and does not show in the cell.

2 - In B1 and B2, you have to include the space after the ° and ' in order to get the answer that you posted.

    A     B 
1   8     ° 
2  55     ' 	
3   0     "	
4			
5   0		
6   4		
7   0
8   
9   =Convert_Degree(Convert_Decimal(A1&B1&A2&B2&A3&B3)*Convert_Decimal(A5&B1&A6&B2&A7&B3))		

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#3
February 9, 2018 at 17:00:42
Thank you DerbyDad for giving the answer and it is working fine. thanks alot.

--sankar


Report •

Related Solutions

#4
February 9, 2018 at 17:58:14
I'm glad it worked for you.

What I find interesting is that if you leave out the spaces, you get a different answer. In fact, if you hadn't of included an example of both input and the expected output, I might have posted a solution that only appeared to work - because it would have returned an answer, albeit an incorrect one.

If I was going to use that UDF in a "production environment" I might add some error checking to make sure that the input included spaces to ensure that the correct output was produced every time.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#5
February 10, 2018 at 01:05:15
Hi Derby,

I am using 2nd option which is closed to my requirement. and the output also should be in separate cells.

could you please help me.


	A	B	C

1	° 	'	"

2	08	55	00

3	00	04	00

4	 0	 35	 40


Report •

#6
February 10, 2018 at 12:33:34
You may need to play with this to make it work for every situation, but this is the general concept for one method:

Degrees:

=LEFT(Convert_Degree(Convert_Decimal(A2&A1&B2&B1&C2&C1)*
Convert_Decimal(A3&A1&B3&B1&C3&C1)),2)*1

Minutes:

=MID(Convert_Degree(Convert_Decimal(A2&A1&B2&B1&C2&C1)*
Convert_Decimal(A3&A1&B3&B1&C3&C1)),4,3)*1

Seconds:

=MID(Convert_Degree(Convert_Decimal(A2&A1&B2&B1&C2&C1)*
Convert_Decimal(A3&A1&B3&B1&C3&C1)),9,2)*1


If you know anything about VBA, you could probably adapt the UDF's to spit out just the Degrees portion, Minutes portion and Seconds portion. In other words, create multiple UDF's, one for each section of the overall answer.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#7
February 14, 2018 at 08:38:23
Hi DerbyDad,

Thank you for the answers infact this left and mid functions are working fine.

If a value is single digit, if I increase number of characters the left function the output will come two chars.

it is not working for some values.

if any suitabe VBA code is there for this problem coluld you please provide me. this will be very helful.

thank you,
Sankar
sisrajsan@gmail.com


Report •

#8
February 14, 2018 at 11:52:28
Do me a favor and save me some work.

In your original post, you used your calculator to come up with the correct answer for a specific pair of inputs. With that information, I was able to come up with a solution that matched your example. As noted earlier, just leaving out a space results in an "answer" that looks OK, but is actually incorrect. Without your example input and output, I probably wouldn't have noticed the incorrect answer.

So, use your calculator again and post a few examples of pairs and the corresponding correct answers so that I have a target to aim for. Include combinations that my suggestions work for as well as combinations that don't work, pointing out which ones are which.

In other words, help me help you by providing some real life examples - with the correct answers - so I can hone in on an solution that works in all cases.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

Ask Question