Help with Averageif Formula (don't think I should use Averageif)

[MUmfleet]

Column A is Resource Titles, such as Expert, Mid-level, Low-level -- with a
seperater blank row then Onsite Resources. So Column A4 starts with Offshore
Resources, A5 is Expert, A6 is Mid-level then A7 for Onsite Resources, A8 is
Expert and A9 is Mid. Column B is their rates. For example, A5 is $25, A6
is 22 then A8 is 65 and A9 is 55. Then in column C is the # of resources
used for a particular project. So for the sake of this example, A5 = 1, A6 =
2, A8 = 1, A9 = 0. I am trying to enter an Average Rate or Blended Rate and
have it reference the # of resources, so if it has 1 or 2 (or any # other
than 0) then it averages the rates in Column B to make a blended rate. If
column C is 0 then I do not want that rate to be averaged into the blended
rate.

Thank you for your help!!!
Mike

 

[PJFry]

It sounds like you want a weighted average rate. If so, put this in cell C10:
=SUMPRODUCT(B5:B9,C5:C9)/SUM(C5:C9)

Post back to let us know if it worked.

 

[MUmfleet]

Thanks PJFry. That did work, but not as I intended it too. wanted it too.
I am wanting it to average only the 3 rates, not the 4. So it is possible to
use 2 people at $22, 1 at $25, and 1 at 65 and 0 at 55. So what I want it to
do is if there is a 0 in the # of Resources then it would only count the 65,
22, and 25 once taking an average of the 3 to give me a blended rate. Does
that make sense? I think I may have confused myself.

 

[PJFry]

The fact that your Onsite Mid value of 55 has no resources should not change
the value of the calculation. Try this:

In cell C10 use the original formula:
=SUMPRODUCT(B5:B9,C5:C9)/SUM(C5:C9)

In cell C11, use this:
=SUMPRODUCT(B5:B8,C5:C8)/SUM(C5:C8)

The only difference is that there the second formula excludes the Onsite Mid
value of 55. The answer in both cases will be 33.5.

Another way to write the same formula is like so:
=((B5*C5)+(B6*C6)+(B8*C8)+(B9*C9))/SUM(C5:C9)

Now, if you were doing a non-weighted average, that zero in Onsite Mid would
affect the calculation if you excluded any rate that did have a resource.
For example, the 33.5 would now be 37.3 (25+22+65)/3.

Does that help at all?

Post back and let me know.
--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.

 

[MUmfleet]

You are absolutely correct and yes both formulas work. However, what I am
trying to do is not a true "blended rate" but rather to only count each rate
as one and omit it if the value is "O". So in the example, we use the 1 @
25, 2 @ 22, 1 @ 65 (which those variables change all the time based per
project need. So what I am trying to do is have it only count each rate once
and leave off the zero value. So the blended rate should be 37.33. While
you are correct in that the true blended rate should be 33.50 I am wanting it
to not count the # of resources but rather the rates excluding the # of rates.

Does that make sense?

Thank you immensely for your help with this.
Mike

 

[PJFry]

Ok, now I think I'm on the trolley...

To the title of the post, yes, you can use average if in cell C10:
=AVERAGEIF(C5:C9,">0",B5:B9)

Or you can be more explict with the ranges and get the same answer:
=SUMIF(C5:C9,">0",B5:B9)/COUNTIF(C5:C9,">0")

Both will give you 37.33 for an answer.

Does that do it? Let me know!

--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.