Freaquently asked questions and examples for formulas

Formula Questions/Examples 

NOTE: [L:P] = location:parameter that is picked from the location:parameter list 

 

Q: How do I write a pounds formula? 

A: The formula will be the flow in MGD multiples by the concentration in mg/L then multiplied by pounds per gallon. Example: [L:P] for flow * [L:P] for concentration * 8.34 

 

Q: How do I write a percent removal formula? 

A: The formula can be written using either concentration or loading values. The percent removal formula using the values from the influent and effluent of the process or plant. Example:  

(([L:P] Influent Concentration - [L:P] Effluent Concentration) / [L:P] Influent Concentration))*100 

 

Q: How do I write a 12 month Median formula for my DMR? 

A: A generic formula can be created and attached to the DMR column. Example: M_MED([~:~],-11,0,0,12) 

 

Q: How do I write a 12 month 95th percentile formula for my DMR? 

A: A generic formula can be created and attached to the DMR column. Example: M_PCT([~:~],-11,0,0,95,12) 

 

Q: We need a 7 day first of month formula that writes values on 7,14,21, & 28.  

A: There are two ways the formula can be written.  

Example 1: IF((DOM(0)/7)=INT((DOM(0)/7)),W_AVG([L:P],0,DOM(0)/7,3,1),BLANK) 

Example 2: IF(DOM(0)=7,W_AVG([L:P],0,1,3,1), IF(DOM(0)=14,W_AVG([L:P],0,2,3,1), IF(DOM(0)=21,W_AVG([L:P],0,3,3,1), IF(DOM(0)=28,W_AVG([L:P],0,4,3,1),BLANK)))) 

Q: Want to write the monthly percent removal on the last day of the month. 

A: Using the M_AVG function in creating the formula for Percent Removal, replace FORMULA with that formula in the example. NOTE: any formula that is using monthly values can be used to write the value to the last day of the month. Example: IF(DOW(0)=LOM(0),FORMULA,BLANK) 

 

Q: When calculating my daily pounds on BOD, I would like a value for every day even though I do not sample every day. How can this be done? 

A: The ideal way would be to use a 7 day average for the BOD and using the daily flow. For the 7 day average, use D_AVG([L:P],-6,7). This will look back for 6 days and average the values for 7 days. The total formula would be: D_AVG([L:P],-6,7)*8.34*Flow LP 

 

Q: Where can I see examples of all of the functions used in formulas? 

A: Two ways, use F1 to get to the help file and search for formula functions or when in the formula editor click on the Function Help button. 

 

Q: Our facility gets new chemical pricing at the first of each year. We use those values in the formula to calculate cost. Is there a better way then having to edit the formula every year? 

A: Best way is to setup a LP for chemical unit cost (IE: CL2 Unit Cost). Create a DataView for a single day showing the unit cost LPs. When new pricing comes in, enter the values there. The formula would then use a D_LAST function to find that value. EXAMPLE: D_LAST([Unit Cost],0,0)*lbs used of the chemical 

Q: We have a large complex formula in Excel that we would like to put into Operator10 but we do not want to type it in. What needs done to get it into the program? 

A: There are several steps that need to take place, but yes it can be added.  

1. Create a Location:Parameter (LP) you want the result written to. 

1. Make sure LPs have been created for all of the information used in the formula. 

1. Create a new formula. 

1. Copy the formula from Excel and paste it into the formula editor. 

1. Edit the copied formula changing all of the Row:Columns with the correct LP. 

1. Test and verify the formula and make any adjustments if needed to make sure the formula is correct. 

Q: Can detention times be calculated in Operator10? 

A: Yes! You can setup the formula to use the number of units in service then use the volume of each tank. In this example the result would be in hours. Example: ([Units In Service:CC In Service]*149577)*24/([Final:Flow Mgd]*1000000) 

 

Q: I am writing a formula and want to attach it to a LP that already has data entered and I do not want to loose the historical data, but want to calculate it going forward. How can this be done? 

A: To write a formula to calculate values for an LP that has data entered already, you would need to use an IF than statement using the ISDATA function. Example: IF(ISDATA([LP formula is attached to]),your formula,[LP formula is attached to]) 

 

Q: We have a scale reading we enter each day and would like to calculate the daily usage of the chemical.  

A: You can use a very simple formula that subtracts today’s value from yesterday's value. Example: D_VAL([LP],0,-1)-[Same LP] 

 

Q: I have a fecal parameter and need the geomean for it on a DataView. 

A: As a program default setting on fecal and e coli parameters the average in the summary is the geomean.  

 

Q: In creating a DMR we need to report a max 7 day average. How does Operator10 calculate the different 7 day average types? 

A: Based on which one is selected the following apply: 

1. Continuous - Overlapping 7-day averages begin on the first day of the reporting period. 

1. First of Month - 7-day averages begin on the first day of the reporting period. 

1. Sat Rule - Weekly average (Sun-Sat) when the Saturday is in the reporting period. 

1. Wed Rule - Weekly average (Sun-Sat) when the Wednesday is in the reporting period. 

1. Full Weeks - Weekly average (Sun-Sat) only when all 7 days are in the reporting period.