#16
|
|||
|
|||
Quote:
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
__________________
Jamal NUMAN, Jamal432@gmail.com, P.O.BoX: 731, Ramallah, West Bank. |
#17
|
||||
|
||||
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''?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#18
|
||||
|
||||
Quote:
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#19
|
|||
|
|||
Quote:
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
__________________
Jamal NUMAN, Jamal432@gmail.com, P.O.BoX: 731, Ramallah, West Bank. |
#20
|
||||
|
||||
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#21
|
|||
|
|||
Quote:
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
__________________
Jamal NUMAN, Jamal432@gmail.com, P.O.BoX: 731, Ramallah, West Bank. |
#22
|
||||
|
||||
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'' =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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#23
|
|||
|
|||
Quote:
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
__________________
Jamal NUMAN, Jamal432@gmail.com, P.O.BoX: 731, Ramallah, West Bank. |
#24
|
||||
|
||||
Hopefully you know enough now to solve them for yourself
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#25
|
||||
|
||||
Guess what I just found!: https://www.msofficeforums.com/excel...mat-excel.html
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#26
|
|||
|
|||
Quote:
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
__________________
Jamal NUMAN, Jamal432@gmail.com, P.O.BoX: 731, Ramallah, West Bank. |
|
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 |