UKC

Hi

Hope someone can help? I am trying to write a formula in Excel to add decibels (dB) and not sure how I do it.  My knowledge of Excel beyond =sum() is none existent.

The formula is

Lw = 10log(10L1/10 + 10L2/10 etc)dB

L1 = 30.8

L2 = 38.9

L3 = 42.4

L4 = 36.8

L5 = 33

L6 = 34.2

L7 = 35

L8 = 33.9

 

On the calculator I insert it as:  10log(10̂̂  ̂3.08+10  ̂3.89+10  ̂4.29 etc)  but I can’t seem to work out how I write the same formula in Excel 2010 so that the total (Lw) updates automatically when the values in the following boxes changes.

 

Just for ease of use the value in excel box

A7 = L1

B7 = L2

C7 = L3

D7 = L4

Etc

Etc

 

Hope this makes sense to someone.  Any help will be appreciated.
I typed this on a phone for some reason once I posted it has changed the power function to a #. It should look like an upside down v like it does on most calculators.

In reply to Alan M:
Something like.

=LOG10(10^(A2/10)+10^(A3/10))
I think anyway.

Note if it doesnt auto refresh you might need to set the spreadsheet to auto recalculate.
File ->Options then Formulas and Workbook calculation - automatic.

In reply to dissonance:
Thanks, its not working I get a message saying the formula contains errors. Any ideas what might be happening?

Also, I made a mistake its Excel 2007 not 10 as first mentioned.

In reply to Alan M:
It might represent multiplication but adding logs is just addition.

30.8 + 38.9 + 42.4 + 36.8 + 33 + 34.2 + 35 + 33.9 = 285

Just sum it like you would any other set of numbers in Excel.

=SUM(A1:A8) for cells A1 to A8

=SUM(A7:H7) for a row A7 to H7

In reply to Alan M:


should be the same in both and runs for me when copied back, so shouldnt be symbol mistranslation.
What error are you getting?

In reply to elsewhere:







A straight addition doesn't take in to account the formula. According to the calculator the answer should be about 45dB.

I have a lot of acoustic data to workout for kitchen extraction systems. had hoped to find away to do it in excel before I spend the next few days with a calculator, a pencil and loads of stress!!

In reply to dissonance:

It just says the formula contains an error will recheck.
Thanks

In reply to Alan M:

Alan

I've PMed you. I've an Excel spreadsheet that has the formula in - I put formula in years ago so can't remember how but your welcome to a copy.

Cheers

Rich

In reply to RichT:

You have mail. Thanks

In reply to Alan M:
I've tried

=10*LOG10(10^(A7/10)+10^(B7/10)+10^(C7/10)+10^(D7/10)+10^(E7/10)+10^(F7/10)+10^(G7/10)+10^(F7/10))

which gave the answer 46.18

Possibly you were missing the * between the first 10 and the log.

But not too sure about the version of excel I'm using.....

In reply to Alan M:

Of course, you're not adding decibels; you're converting dB values into relative sound powers to find the summed sound power expressed in decibels wrt the 1picowatt 0dB SWL reference level (e.g. 30.8 dB SWL is a relative sound power of 1202.2, and thus an absolute sound power of 1202.2 pW, or 1.2022 nW).

So, you convert the decibel SWL values back to relative power by doing the power(10, value/10), sum the powers, and then convert that into a decibel figure using 10*log10(sum).

One easy way is to do the operation long hand, which is to create another column (or row), each cell containing the relative power figure. then sum all of these in another cell, and have a final decibel figure for the sum in yet another cell. Saves typing and errors in a long, tedious formula, because you just enter one simple power(10, value/10) formula and drag copy into the other cells.

Or you could just use the calculator here:

http://www.sengpielaudio.com/calculator-spl.htm

Oh, and you need to make sure your readings are dB sound power (SWL), and not dB sound pressure (SPL). The electrical equivalent of this distinction would be power and voltage; double the power increases the dB by 3. Double the voltage increases the dB by 6. If your figures were dB SPL, the sum would be 54.4 dB SPL.

In reply to captain paranoia:


Thanks I finally got it working like the other poster pointed out it was simply I had missed out the * between the 10 and the first Log. Also, Richard (another poster) sent over a spreadsheet he had created which works a treat also

Yeah I am aware of the difference between Sound power level and sound pressure level etc my job involves acoustics and noise surveys most days. Normally I do the calculations the old fashioned way using a calculator, some paper and a pencil but this week I have been given a shed load of kitchen extraction systems to deal with. There was no chance that I was going to tackle that job with a calculator.

In reply to derekParsons:






Thanks for running it, it is the same answer as I got and the same as the manufacturer. It was simply that I had made a mistake typing in the formula that was causing the error. Thanks.