Average IF?
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 |
Average IF?
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 --------------- Jacob Skaria "Scott A" wrote: 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 |
All times are GMT +1. The time now is 05:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com