3

So I've got quite the specific task. I want to create a function that will add decibel units together. Currently, you have to input something like

=10*LOG10(10^(A1/10)+10^(A2/10))

and make this even longer if you have like 15 parts you want to add together. Kind of a pain.

Ideally, it would work like the excel function SUM and just take in any input. Can someone help me put this together, or at least show me what SUM would look like if you had to create it from scratch?

Best, T. Heng

Hongbin Wang
  • 1,186
  • 2
  • 14
  • 34
T. Heng
  • 41
  • 2
  • End code ended up being: Function DBADD3(ParamArray nums()) As Double Dim DBPrTot As Variant DBPrTot = 0 For i = LBound(nums) To UBound(nums) DBPrTot = DBPrTot + 10 ^ (nums(i) / 10) Next i DBADD3 = 10 * WorksheetFunction.Log10(DBPrTot) End Function – T. Heng Apr 28 '16 at 15:30

2 Answers2

5

This little UDF() will give you more flexibility:

Public Function decibelle(rng As Range, N As Long) As Double
    Dim wf As WorksheetFunction, i As Long, Z As Double
    Set wf = Application.WorksheetFunction
    For i = 1 To N
        Z = Z + 10 ^ (rng(i) / 10)
    Next i
    decibelle = 10 * wf.Log10(Z)
End Function

where the first argument is the range of inputs and the second argument is the number of inputs:

enter image description here

EDIT#1:

and if you want the UDF() to be more like SUM() consider:

Public Function decibelle2(rng As Range) As Double
    Dim wf As WorksheetFunction, r As Range, Z As Double
    Set wf = Application.WorksheetFunction
    For Each r In rng
        Z = Z + 10 ^ (r.Value / 10)
    Next r
    decibelle2 = 10 * wf.Log10(Z)
End Function

so you could use it like:

=decibelle2(A1:A2)
Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • Oh this is nifty and works well! What if I wanted to choose parameters that were not successive? Like A1:A2, A4, A6 and so forth? Such as with ParamArray below? – T. Heng Apr 28 '16 at 15:16
  • @T.Heng I agree that `ParamArray` would be a BIG improvement to this code.................sadly, I am not an expert of `ParamArray`'s use...........I suggest that once you are satisfied with a version of the *UDF()*, open a new question on the `ParamArray` issue. – Gary's Student Apr 28 '16 at 15:23
1

The below is a very simple example of a User Defined Function (which should be typed into a standard module) that can take any number of parameters and return the sum of these parameters. Your example seems to involve more advanced logic, so you will have to expand upon my example. Let us know if you need more assistance.

Function AddSomeDigits(ParamArray nums()) As Double
    Dim vRunningTotal As Variant

    vRunningTotal = 0

    For i = LBound(nums) To UBound(nums)
        vRunningTotal = vRunningTotal + nums(i)
    Next i

    AddSomeDigits = vRunningTotal
End Function
basodre
  • 5,720
  • 1
  • 15
  • 23
  • I like using *ParamArray* ...............you could make something REALLY close to *SUM()* using it. – Gary's Student Apr 28 '16 at 13:37
  • Very good example! This is pretty much like SUM. If I have to factor in the equation, I want the part that's going into the LOG10 function to be using those inputs. I'm having trouble with using those iterations. Can you assist me? Is it clear what I'm asking based off of my original post? Function DBADD3(ParamArray nums()) As Double Dim DBPrTot As Variant DBPrTot = 0 For i = LBound(nums) To UBound(nums) DBPrTot = DBPrTot + 10 ^ (nums(i) / 10) Next i DBADD3 = 10 * wf.Log10(DBPrTot) End Function Is what I have now – T. Heng Apr 28 '16 at 15:20
  • 'code'Function DBADD3(ParamArray nums()) As Double Dim DBPrTot As Variant DBPrTot = 0 For i = LBound(nums) To UBound(nums) DBPrTot = DBPrTot + 10 ^ (nums(i) / 10) Next i DBADD3 = 10 * wf.Log10(DBPrTot) End Function – T. Heng Apr 28 '16 at 15:26
  • @T.Heng Good to hear that you figured it out. Basically, `nums` works like any other array, where you'd reference each value using its indexer. For example, the first value would be `nums(1)`, etc. – basodre Apr 28 '16 at 17:24