Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A formula to AVERAGE IF but only average a set number of values | Excel Worksheet Functions | |||
Find monthly average but have average automatically configured | Excel Discussion (Misc queries) | |||
Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
Weighed Average of a weiged average when there are blanks | Excel Discussion (Misc queries) | |||
how does one convert text to a formula "average(A:A)" to =average( | Excel Worksheet Functions |