PDA

View Full Version : Solved: Finding complicated average



john3j
08-19-2009, 10:58 AM
Hello All:

I have two numbers that I have to come up with so that I can calculate an average.

First, a total number of actions. In order to do this, the following conditions have to be met. G2:G1000 = "09" and AL2:AL1000 = "Awarded"

For the other number, I have to get a dollar amount so I dont know if I could use a sumproduct or not. For this total obligated number the following conditions need to be met. if G2:G1000 = "09" it should sum those conditions that are met within the range U2:U1000. In other words, if it was an action in 09, the we need to go ahead an add it to the sum.

From here I would calculate the average by dividing the two. If you could even write me a formula that does all of this on one line that would be great. Please help!

Thanks:dunno

mdmackillop
08-19-2009, 11:06 AM
Maybe
=SUMPRODUCT(--(G2:G1000="09"),--(AL2:AL1000="Awarded"),(U2:U1000))/COUNT(U2:U1000)

john3j
08-19-2009, 02:55 PM
hello,

what you had sent me did not do anything. I created a test sheet and gave all of the information that I am looking for. Please let me know if you can help me.

Thanks

Bob Phillips
08-19-2009, 03:16 PM
Try this array formula

=AVERAGE(IF((A1:A10=9)*(E1:E10="Contract Awarded"),G1:G10))

mdmackillop
08-19-2009, 05:19 PM
In order to do this, the following conditions have to be met. G2:G1000 = "09" and AL2:AL1000 = "Awarded"

Did you try my code with the conditions stated in your question, or with the totally different conditions as shown in your example?

Bob Phillips
08-20-2009, 12:59 AM
Malcolm,

Your average would be off because you don't apply the conditions to the divisor, so you only add the numbers where the conditions are met, but divide by all of the numbers.