Average IF?

[Scott A]

Hello Again,

I am looking for a formula for cell S35 that will do three things:
1-If S7 & S23 are both 0's, show a 0
2-Add (S11*S7)+(S23*S19) divided by S31
3-If either S7 or S19 are zeros, then it needs act as if they are 1's for
the formula

I hope this makes sense? The forluma that I came up wth is:
=SUM(IF(S23+S11<>0,((S11*S7)+(S23*S19))/S31))
The problem is that the amount in S11 is counted as 0 (since S7 is a 0) and
provides an incorrect average amount in S35.

See below for sample:

R S
4 Set #1
5 4/3/2009
6
7 Complete Rehabs 0.00
8 Average Rehabs 2.60
9 Complete Demos 1.00
10 Average Demos 2.28
11 Cost Per Rehab $337.92
12
13 Average Per Unit $917.49
14
15
16 Set #2
17 4/3/2009
18
19 Complete Rehabs 5.00
20 Average Rehabs 3.20
21 Complete Demos 0.00
22 Average Demos 3.00
23 Cost Per Rehab $720.90
24
25 Average Per Unit $932.45
26
27
28 Totals
29 4/3/2009
30
31 Complete Rehabs 5.00
32 Average Rehabs 5.80
33 Complete Demos 1.00
34 Average Demos 5.28
35 Cost Per Rehab $788.48 (This is the correct amount, not what the
formula calculates)

Any help would be greatly appreciated!

Scott A

 

[Jacob Skaria]

Scott

Try using the MAX() inbetween for getting values from S7 and S19..as below

=SUM(IF(S23+S11<>0,((S11*MAX(1,S7))+(S23*MAX(1,S19)))/S31))

If this post helps click Yes