• Status: Solved
• Priority: Medium
• Security: Public
• Views: 855

# An Average Formula

Objective: To write the required formula with correct and precise results:

Below is the formula for a desired output

1623col
------------------------------------ - 110
((1600col/480) + (1603col/470)) - 1622col

- 1600col, 1603col, 1622col & 1623col are all columns
- Each column is required to be run as an average
- Each division needs nullif  for 0
- AVG is used to total hourly reports and produce an average output of those reports

Current formula: below (not working) I originally worked out the formula in long hand and it doesn't seem to hold the parantheses.

STR((NULLIF (AVG(dbo.Table_II.[1623col]), 0)
/ NULLIF ((AVG(dbo.Table_II.[1600col] / 480), 0)
+ NULLIF (AVG(dbo.Table_II.[1603col] / 470), 0))
- (AVG(dbo.Table_II.[1622col]) - 110), 8, 2) AS AVG1

0
rhservan
• 2
• 2
1 Solution

select STR((NULLIF (AVG(dbo.Table_II.[1623col]), 0) )
/ NULLIF (AVG(dbo.Table_II.[1600col] / 480), 0)
+ NULLIF (AVG(dbo.Table_II.[1603col] / 470), 0)
- (AVG(dbo.Table_II.[1622col]) - 110), 8, 2) AS AVG1
0

Commented:

select
STR(
(NULLIF (AVG(dbo.Table_II.[1623col]), 0)
/ (NULLIF (AVG(dbo.Table_II.[1600col] / 480), 0)
+ NULLIF (AVG(dbo.Table_II.[1603col] / 470), 0)
- AVG(dbo.Table_II.[1622col]))
- 110)
, 8, 2)
AS AVG1
0

Author Commented:
I wish it were that simple.  I attempted your parenthese change and it would not run. "select" is a given.
0

Commented:
Can you test wish each of operation
e.g

1623col <==> NULLIF (AVG(dbo.Table_II.[1623col]), 0)
1600col/480<==>NULLIF(AVG(dbo.Table_II.[1600col] / 480),0)
1603col/470<==>NULLIF(AVG(dbo.Table_II.[1603col] / 470),0)
1622col<==> NULLIF (AVG(dbo.Table_II.[1622col]), 0)

select (1623col/(((1600col/480) + (1603col/470)) - 1622col)-110

replace with above value we have

select (NULLIF (AVG(dbo.Table_II.[1623col]), 0)/(((NULLIF(AVG(dbo.Table_II.[1600col] / 480),0)) +
(NULLIF(AVG(dbo.Table_II.[1603col] / 470),0))) - NULLIF (AVG(dbo.Table_II.[1622col]), 0)
)-110

then add Str function if you want

select STR(str,n1,n2)

so

select STR((NULLIF (AVG(dbo.Table_II.[1623col]), 0)/(((NULLIF(AVG(dbo.Table_II.[1600col] / 480),0)) +
(NULLIF(AVG(dbo.Table_II.[1603col] / 470),0))) - NULLIF (AVG(dbo.Table_II.[1622col]), 0)
)-110,8,2)
0

Author Commented:
HuyBD your first response was correct already. thanks for the good eye on that one.
0

## Featured Post

• 2
• 2
Tackle projects and never again get stuck behind a technical roadblock.