##### Resources

# Simple Excel Worksheet Predicts Control Valve Aerodynamic Noise

Submitted by Jon Monsen Ph.D., PE || Valin Corporation

Recently an article was published that presented an easily constructed Excel sheet that estimates hydrodynamic noise in control valves1. A number of people have asked me if there is something similar for aerodynamic noise. It turns out that Hans D. Baumann, an internationally recognized control valve expert who has spent many years investigating control valve noise mechanisms and theory and who developed the method in Reference 1 also has a similar simplified method for estimating control valve aerodynamic noise. He has kindly given me permission to use it as a basis for an Excel worksheet which is the subject of this article.

IEC 60534-8-4:2015 is nearly universally recognized as the preferred method for predicting control valve aerodynamic noise and most control valve manufacturers include it in their control valve sizing and selection applications. For the user who wants to make quick noise predictions or who wants to construct their own valve sizing application, programming the IEC noise standard can be a daunting task. For example, I have constructed an Excel sheet that exactly implements the IEC standard. That Excel sheet consists of 261 lines of formulas, a task that many would not have the time or enthusiasm for. Thus, the justification for a simplified noise prediction method.

Reliable valve noise test data is difficult to find unless you are a valve manufacturer and then you tend not to share it. I did find 10 noise tests that I believe to be reliable and using this simplified noise prediction method the average absolute error was 3.3 dB(A). Control valve noise prediction methods normally claim accuracy of plus or minus 5 dB(A).

The calculation method is shown in Table 1. The method presented here is simple enough to be implemented with a pocket calculator or one of the many calculator apps available for cell phones but once one has constructed the Excel sheet, the calculations can be done even more quickly. We refer to this as the ABC method, partly because the term “ABC” is often recognized as something that is simple, and also because the main noise calculation is divided into three parts, named A, B and C. The balance of this article describes how to construct an Excel sheet that will implement these calculations Screen 1 is an Excel worksheet that implements the calculations. To construct the worksheet, start by entering the text shown in black, all of which is simply plain text. Everything shown in green is the result of formulas that are entered in those cells.

Column D, Rows 5 through 16, is where the user’s process and valve data are entered. The numbers shown in red are the sample process and valve data that were used in the sample calculation. To test the worksheet, if you enter these sample data you should get the calculation results shown in green on Screen 1.

The formulas for all the calculations rely on the cells in Column D having the names to their left in Column C. To name them, highlight both Columns C and D, rows 5 through 31. Select the Excel “Formulas” tab, select “Create from Selection” then check “Left column” and click “OK.” Now all the cells in Column D, Rows 5 through 31 will have the names in the cells to their left. This makes it possible for all the formulas to use the actual names of each variable instead of simply cell references. For example, the inlet pressure, P_1, appears in all the formulas that depend on it as “P_1” instead as simply “D5.” You can do this in one step. Excel will ignore any cells that have nothing in them.

There are also formulas in B5, 6, 8 and 10. The worksheet includes the ability to change the engineering units of inlet and outlet pressure, the gas sonic velocity and the downstream pipe diameter. The choices of engineering units are made by entering a conversion factor in Column D, Rows 20, 21 and 22. The available options are listed in Column A Rows 20, 21 and 22. The user’s selection entries in D 20, 21 and 22 use formulas in Column B Rows 5, 6, 8 and 10 to reflect the engineering units that the user has selected. The user’s selection of units also modifies the calculation formulas appropriately.

Next, enter the formulas shown on Screen 2 in the appropriate cells. This requires great care for the Excel sheet to work properly. If you have accurately entered the process and valve data, and the formulas, you will get the calculated results shown in green on Screen 1.

Also enter the formulas that show the user’s selected engineering units as shown on Screen 3.

Figure 1 shows a comparison of an actual blowdown noise test and the calculations made with the Excel sheet described in this article.

As an option you can add an additional column of noise calculations by doing the following: First copy all the data entries that are in Column D (Cells D5 through D16) into the same rows of Column E. This will make it easier to follow your progress as you copy and convert formulas from Column D to Column E.

Next copy all the formula entries (the ones in green in Column D of Screen 1) and paste them into the same rows of Column E. At this point Column E will be giving noise calculations for process data in Column D. Next you will need to convert the formulas you have pasted into Column E to make them rely on the data that will be in Column E. This is not too difficult. For example, to convert the formula for X in Column E, click on E25 and you will see the formula from Column D in the formula bar at the top of your Excel sheet. The formula references input data to P_1 and P_2, but in Column D. You need to change these to the references for P_1 and P_2 to their equivalent in Column E. To do this, first highlight P_1 in the formula bar. Then point to the source for P_1 in Column E (Cell E6). Then highlight P_2 in the formula bar and click on its source in Column E (Cell E7). Finally highlight the second instance of P_1 and then click on its reference in Column E. Save this converted formula by pressing “Enter.” You now have a formula in Column E for X, Pressure ratio in E25 that is based on the entered values of P_1 and P_2 that are entered in Column E. Your converted formula should be “=(E6-E7)/E6”. This needs to be done for all your copied formulas (Column D, Rows 21 and 25 through 30.

If you choose to perform the above steps it will be extremely easy to extend the worksheet to include any number of calculations that you choose. For example, if you want to add another eight calculations to the worksheet, do the following:

A significantly enhanced version of this Excel worksheet can be downloaded below:

IEC 60534-8-4:2015 is nearly universally recognized as the preferred method for predicting control valve aerodynamic noise and most control valve manufacturers include it in their control valve sizing and selection applications. For the user who wants to make quick noise predictions or who wants to construct their own valve sizing application, programming the IEC noise standard can be a daunting task. For example, I have constructed an Excel sheet that exactly implements the IEC standard. That Excel sheet consists of 261 lines of formulas, a task that many would not have the time or enthusiasm for. Thus, the justification for a simplified noise prediction method.

Reliable valve noise test data is difficult to find unless you are a valve manufacturer and then you tend not to share it. I did find 10 noise tests that I believe to be reliable and using this simplified noise prediction method the average absolute error was 3.3 dB(A). Control valve noise prediction methods normally claim accuracy of plus or minus 5 dB(A).

The calculation method is shown in Table 1. The method presented here is simple enough to be implemented with a pocket calculator or one of the many calculator apps available for cell phones but once one has constructed the Excel sheet, the calculations can be done even more quickly. We refer to this as the ABC method, partly because the term “ABC” is often recognized as something that is simple, and also because the main noise calculation is divided into three parts, named A, B and C. The balance of this article describes how to construct an Excel sheet that will implement these calculations Screen 1 is an Excel worksheet that implements the calculations. To construct the worksheet, start by entering the text shown in black, all of which is simply plain text. Everything shown in green is the result of formulas that are entered in those cells.

Column D, Rows 5 through 16, is where the user’s process and valve data are entered. The numbers shown in red are the sample process and valve data that were used in the sample calculation. To test the worksheet, if you enter these sample data you should get the calculation results shown in green on Screen 1.

The formulas for all the calculations rely on the cells in Column D having the names to their left in Column C. To name them, highlight both Columns C and D, rows 5 through 31. Select the Excel “Formulas” tab, select “Create from Selection” then check “Left column” and click “OK.” Now all the cells in Column D, Rows 5 through 31 will have the names in the cells to their left. This makes it possible for all the formulas to use the actual names of each variable instead of simply cell references. For example, the inlet pressure, P_1, appears in all the formulas that depend on it as “P_1” instead as simply “D5.” You can do this in one step. Excel will ignore any cells that have nothing in them.

There are also formulas in B5, 6, 8 and 10. The worksheet includes the ability to change the engineering units of inlet and outlet pressure, the gas sonic velocity and the downstream pipe diameter. The choices of engineering units are made by entering a conversion factor in Column D, Rows 20, 21 and 22. The available options are listed in Column A Rows 20, 21 and 22. The user’s selection entries in D 20, 21 and 22 use formulas in Column B Rows 5, 6, 8 and 10 to reflect the engineering units that the user has selected. The user’s selection of units also modifies the calculation formulas appropriately.

Next, enter the formulas shown on Screen 2 in the appropriate cells. This requires great care for the Excel sheet to work properly. If you have accurately entered the process and valve data, and the formulas, you will get the calculated results shown in green on Screen 1.

Also enter the formulas that show the user’s selected engineering units as shown on Screen 3.

Figure 1 shows a comparison of an actual blowdown noise test and the calculations made with the Excel sheet described in this article.

As an option you can add an additional column of noise calculations by doing the following: First copy all the data entries that are in Column D (Cells D5 through D16) into the same rows of Column E. This will make it easier to follow your progress as you copy and convert formulas from Column D to Column E.

Next copy all the formula entries (the ones in green in Column D of Screen 1) and paste them into the same rows of Column E. At this point Column E will be giving noise calculations for process data in Column D. Next you will need to convert the formulas you have pasted into Column E to make them rely on the data that will be in Column E. This is not too difficult. For example, to convert the formula for X in Column E, click on E25 and you will see the formula from Column D in the formula bar at the top of your Excel sheet. The formula references input data to P_1 and P_2, but in Column D. You need to change these to the references for P_1 and P_2 to their equivalent in Column E. To do this, first highlight P_1 in the formula bar. Then point to the source for P_1 in Column E (Cell E6). Then highlight P_2 in the formula bar and click on its source in Column E (Cell E7). Finally highlight the second instance of P_1 and then click on its reference in Column E. Save this converted formula by pressing “Enter.” You now have a formula in Column E for X, Pressure ratio in E25 that is based on the entered values of P_1 and P_2 that are entered in Column E. Your converted formula should be “=(E6-E7)/E6”. This needs to be done for all your copied formulas (Column D, Rows 21 and 25 through 30.

If you choose to perform the above steps it will be extremely easy to extend the worksheet to include any number of calculations that you choose. For example, if you want to add another eight calculations to the worksheet, do the following:

- Select Column E, cells (E25 through E31). (Excel will ignore any empty cells.)
- Copy these cells (ctrl + C).
- Click on Cell F21 and drag the pointer to M21
- Paste (ctrl + V)

A significantly enhanced version of this Excel worksheet can be downloaded below:

**Download Aerodynamic Noise ABC Enhanced**

**Article featured in Valve World Magazine**

##### A lesson for me is that I need to involve you earlier in the program.

You were tireless in your support and it will not be forgotten!

##### Latest from Valin's Blog

The NIST Chemistry WebBook contains a great deal of information regarding the properties of a broad range of chemicals and is helpful for those who deal with chemical processes.In this article, Jon Monsen has outlined the procedure for finding the actual density of a gas using the WebBook.