Convert some formulas to one UDF Function

[YasserKhalil]

Hello everybody
I have data in columns (B:E) and in column G & I & K there are formulas that lead to specific result
I need to convert these formulas to UDF function not code ...

Attention: range("B2:E2") is just an example .. I mean the range may be dynamic .. may be any range in the same row,eg. may be (A2:J2)
Hope it is clear

[cyiangou]

Here's the first one:

Public Function MultiTan(ParamRng As Excel.Range) As Double
    Const PI = 3.1415927
    Dim Cell As Excel.Range
    Dim Ret As Double
    Ret = 0
    For Each Cell In ParamRng
        Ret = Ret + Tan(Cell.Value * PI / 180)
    Next
    Ret = Atn(Ret)
    MultiTan = Ret
End Function

Enter in G2 as

=MultiTan(B2:E2)

[cyiangou]

The complete calc:

Public Function FullCalc(ParamRng As Excel.Range) As Double
    Const PI = 3.1415927
    Dim Cell As Excel.Range
    Dim Ret As Double
    Ret = 0
    For Each Cell In ParamRng
        Ret = Ret + Tan(Cell.Value * PI / 180)
    Next
    Ret = Atn(Ret) * 180 / PI
    If Ret < 0 Then Ret = Ret + 360
    FullCalc = Ret
End Function

[YasserKhalil]

Thanks for offering help
But I can't get my desired results
I could devise it now

Function AnglesAverage(Rng As Range)
    Application.Volatile
    Dim Cell As Range, Counter As Long, Temp
    For Each Cell In Rng
        Temp = Temp + Tan(Cell.Value * Application.WorksheetFunction.PI() / 180)
    Next Cell
    Temp = Atn(Temp) * 180 / Application.WorksheetFunction.PI()
    If Temp <= 0 Then Temp = Temp + 360
    AnglesAverage = Temp
End Function

[cyiangou]

At first I thought you were looking for a set of 3 formulas.

[YasserKhalil]

Thanks a lot for help
In your post #3
Your UDF function is very near of mine .. although I have got different results ... Any idea about that?

[MrShorty]

You call the function "anglesaverage", suggesting that this function is supposed to give some kind of "average" of the four input angles. Note that, by using the built in VBA function Atn(), your results will always be in the first or fourth quadrant (I'm assuming you are familiar with the unit circle here). Based on the given inputs, where three of four inputs for each computation are in the first and fourth quadrant, perhaps this is sufficient. I guess I would suggest that you review the trigonometry behind the problem and make certain that you will never have a scenario where the result should be in the second and third quadrants before accepting this as a solution.

[nilem]

maybe so

Function AnglesAverage(Rng As Range) As Double
Dim v, t#
With WorksheetFunction
    For Each v In Rng.Value
        t = t + Tan(.Radians(v))
    Next
    t = Atn(t) * 180 / .Pi
End With
AnglesAverage = IIf(t < 0, t + 360, t)
End Function

edited
Oh, it was in the post #3

[shg]

How about just a formula?

Row\Col	B	C	D	E	F	G	H	I	J	K	L	M	N
2	297	271	160	350		-1.554		-89.042		270.958		270.958	M2: =MOD(DEGREES(ATAN(SUMPRODUCT(TAN(RADIANS(B2:E2))))), 360)
3	300	273	161	351		-1.524		-87.314		272.686		272.686
4	303	275	162	352		-1.497		-85.743		274.257		274.257
5	306	277	163	353		-1.471		-84.260		275.740		275.740

I can't imagine what the formula usefully computes.

[YasserKhalil]

@Mr. shg
Excellent formula
Great solution thanks a lot for sharing this solution
Regards

[shg]

You're welcome.