the angle format (º ‘ ‘’) and calculation?

Jamal NUMAN · #16 04-25-2012, 02:56 PM

Quote:

Originally Posted by macropod

Hi Jamal,

The underscore under the degree symbol is part of the font you're using. If you don't like it, use a different font.

When using the custom format, all you need to remember is that values in it must be multiplied by 24 to get the 'real' values and 'real' values must be divided by 24 for the custom format. Of course, when comparing the custom format values to a 'real' value, you can apply the conversion to either the custom format value or the 'real' value.

Instead of your IF formula, you can use:
=MOD(B3,15)*24 or =MOD(B3*24,180)

More challenges!

If the subtraction of two angles is minus, then the value is not given!

For example:

As the value of C11-C9 is -000º00’25’’, then the Excel doesn’t provide this value! Why it doesn’t produce a negative value? It is a need in our calculation

Best

Jamal

macropod · #17 04-25-2012, 03:57 PM

Hi Jamal,
The formula in H4 can be reduced to:
=MOD(H3+C4+7.5,15)
You can then copy this down to H8.

BTW, you really should correct the basic angles. Surely the sum in C9 should be exactly 540º00'00''?

macropod · #18 04-25-2012, 04:16 PM

Quote:

Originally Posted by Jamal NUMAN

If the subtraction of two angles is minus, then the value is not given!

That's because you're actually working with a time format and times cannot be negative. However, you can use:
=ABS(C11-C9)
and combine this with a conditional format that uses the formula =C11<C9 and colours the cell accordingly.

Jamal NUMAN · #19 04-26-2012, 06:11 AM

Quote:

Originally Posted by macropod

That's because you're actually working with a time format and times cannot be negative. However, you can use:
=ABS(C11-C9)
and combine this with a conditional format that uses the formula =C11<C9 and colours the cell accordingly.

Many thanks Paul. This is quite helpful

I’m attaching the entire table of calculation.

As the angles and distances are collected from the fields then the need to be corrected if they don’t fit the trigonometric rules.

We need to have the negative value so that we subtract this value from each angle and therefore the error is distributed and eliminated.

Is there some work around to have the angle written in minus?

Best

Jamal

macropod · #20 04-26-2012, 06:27 AM

Hi Jamal,

You could use:
=IF(C11>C9,"","-")&TEXT(ABS(C11-C9),"[hh]ºmm'ss''")
but then the contents of C12 becomes text and cannot be used as a numeric input for another formula.

As a result, you then need to change C13 to:
=IF(C11>C9,"","-")&TEXT(ABS(C11-C9)/C10,"[hh]ºmm'ss''")
and you need to change E3 to:
=C3+(C$11-C$9)/C$10

You might then also want to change the alignment in C12, C13 and D3 to D7 to right-aligned.

Jamal NUMAN · #21 04-28-2012, 01:14 PM

Quote:

Originally Posted by macropod

Hi Jamal,

You could use:
=IF(C11>C9,"","-")&TEXT(ABS(C11-C9),"[hh]ºmm'ss''")
but then the contents of C12 becomes text and cannot be used as a numeric input for another formula.

As a result, you then need to change C13 to:
=IF(C11>C9,"","-")&TEXT(ABS(C11-C9)/C10,"[hh]ºmm'ss''")
and you need to change E3 to:
=C3+(C$11-C$9)/C$10

You might then also want to change the alignment in C12, C13 and D3 to D7 to right-aligned.

Many thanks for your creative ideas. I can’t imagine this forum without your prompt support.

In the AutoCAD, they do have the angle in DMS as data format but the calculations are much difficult to be processed there. In Excel, they don’t have the angle as data format while the calculations are a bit easier to be processed.

The data tabulated need to be further processed.

1. I need to have the cumulative error in the D column (=D3+C$13)

2. I need to add the values of D to the values of C (=C3+D3)

could you please help with this part? The issue of “negative time” requires much more workaround!

The file is attached

Best

Jamal

macropod · #22 04-28-2012, 04:27 PM

Hi Jamal,

You will get better results if you make all of the changes I have suggested. Even so, I think you have the wrong error values. In your worksheet, you display them as negative values:

Code:

Sum               540º00'25''
Count (n)                 5
Total Angles      540º00'00''
Total Error       -00º00'25''
Error/Angle       -00º00'05''

but the total error is really '00º00'25''', a positive value, since your included angle is too great. This amount, in total, has to be deducted from the other angles. A negative error would have to be added. You can get the correct result by changing C12 from:
=IF(C11>C9,"","-")&TEXT(ABS(C11-C9),"[hh]ºmm'ss''")
to:
=IF(C11<C9,"","-")&TEXT(ABS(C9-C11),"[hh]ºmm'ss''")
and changing C13 from:
=IF(C11>C9,"","-")&TEXT(ABS(C11-C9)/C10,"[hh]ºmm'ss''")
to:
=IF(C11<C9,"","-")&TEXT(ABS(C9-C11)/C10,"[hh]ºmm'ss''")
You will now get the correct values in column D also (00º00'05'').

Then, if you use the following formula in column E:
=C3-(C$9-C$11)/C$10
you will get the correct result for that column.

The formula in column I, for working with the corrected angles, should therefore be:
=MOD(I3+E4+7.5,15)

Similarly, the formulae in columns O & P, for calculation the distance corrections, should be:
=(H4/G$9)*M$10
and:
=(H4/G$9)*M11
and the formulae in columns Q & R, for calculating the correct distances, should be:
=M4-O4
and:
=N4-P4

See attached worksheet.

Jamal NUMAN · #23 05-01-2012, 01:36 AM

Quote:

Originally Posted by macropod

Hi Jamal,

You will get better results if you make all of the changes I have suggested. Even so, I think you have the wrong error values. In your worksheet, you display them as negative values:

Code:

Sum               540º00'25''
Count (n)                 5
Total Angles      540º00'00''
Total Error       -00º00'25''
Error/Angle       -00º00'05''

but the total error is really '00º00'25''', a positive value, since your included angle is too great. This amount, in total, has to be deducted from the other angles. A negative error would have to be added. You can get the correct result by changing C12 from:
=IF(C11>C9,"","-")&TEXT(ABS(C11-C9),"[hh]ºmm'ss''")
to:
=IF(C11<C9,"","-")&TEXT(ABS(C9-C11),"[hh]ºmm'ss''")
and changing C13 from:
=IF(C11>C9,"","-")&TEXT(ABS(C11-C9)/C10,"[hh]ºmm'ss''")
to:
=IF(C11<C9,"","-")&TEXT(ABS(C9-C11)/C10,"[hh]ºmm'ss''")
You will now get the correct values in column D also (00º00'05'').

Then, if you use the following formula in column E:
=C3-(C$9-C$11)/C$10
you will get the correct result for that column.

The formula in column I, for working with the corrected angles, should therefore be:
=MOD(I3+E4+7.5,15)

Similarly, the formulae in columns O & P, for calculation the distance corrections, should be:
=(H4/G$9)*M$10
and:
=(H4/G$9)*M11
and the formulae in columns Q & R, for calculating the correct distances, should be:
=M4-O4
and:
=N4-P4

See attached worksheet.

Thank you very much Paul for your distinct effort in resolving this thread. Such creative solution reflects your massive experience and knowledge in how to overcome challenges innovatively.

I’ll see If i may encounter in more challenges regarding angles format!

Appreciated

Best

Jamal

macropod · #24 05-01-2012, 01:52 AM

Hopefully you know enough now to solve them for yourself

macropod · #25 05-11-2012, 11:11 PM

Guess what I just found!: https://www.msofficeforums.com/excel...mat-excel.html

Jamal NUMAN · #26 05-19-2012, 01:50 PM

Quote:

Originally Posted by macropod

Guess what I just found!: https://www.msofficeforums.com/excel...mat-excel.html

I do remember that thread. You helped me at that time but I got busy with other issues!

Thank you Paul for offering help all the time

Appreciated

Best

Jamal

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Date Calculation	Lights	Excel	5	04-18-2012 04:31 AM
calculation of start date	ketanco	Project	1	02-29-2012 07:01 AM
Formula calculation	danbl	Excel	8	02-23-2012 04:35 AM
Calculation within Cells	manich1	Excel	2	12-07-2011 02:59 PM
Angle Bracket Problem, STYLEREF in Header of a Protected Document	wordjunkie	Word	0	06-18-2010 04:34 AM