Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10
Default Help with Averageif Formula (don't think I should use Averageif)

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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 143
Default Help with Averageif Formula (don't think I should use Averageif)

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.
--
Regards,

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



"MUmfleet" wrote:

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

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10
Default Help with Averageif Formula (don't think I should use Averagei

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" wrote:

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.
--
Regards,

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



"MUmfleet" wrote:

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

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 143
Default Help with Averageif Formula (don't think I should use Averagei

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" wrote:

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" wrote:

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.
--
Regards,

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



"MUmfleet" wrote:

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

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10
Default Help with Averageif Formula (don't think I should use Averagei

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" wrote:

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" wrote:

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" wrote:

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.
--
Regards,

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



"MUmfleet" wrote:

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



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 143
Default Help with Averageif Formula (don't think I should use Averagei

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.



"MUmfleet" wrote:

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" wrote:

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" wrote:

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" wrote:

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.
--
Regards,

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



"MUmfleet" wrote:

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
AVERAGEIF T. Valko Excel Worksheet Functions 9 July 15th 09 07:01 PM
AverageIf TG Excel Discussion (Misc queries) 5 June 10th 08 08:51 PM
Averageif help smeldawg Excel Discussion (Misc queries) 7 April 15th 08 10:28 PM
averageif name Excel Worksheet Functions 1 May 4th 06 05:27 PM
averageif rudy Excel Discussion (Misc queries) 5 April 27th 06 11:20 PM


All times are GMT +1. The time now is 12:19 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"