Conditional weighted average

[saturnexcel]

I have two columns. One has cost (column A) and the other has profit (column B). I'd like to calculate the percentage profit as a function of how much something cost. So, if it cost $100 and the profit was $20 I would like to calculate 20%. For all percentages, I'd like to average them. I understand this is a "weighted average" and was able to get this working with this formula:

Please Login or Register  to view this content.

However, I don't want to include entries where the profit column has a negative value. I tried something like this:

Please Login or Register  to view this content.

This doesn't seem to work quite right as it's not ignoring the cost entry when the profit entry is negative. Any advice?

[shg]

Welcome to the forum.

Maybe =SUMIF(B1:B20, ">0") / SUMIF(B1:B20, ">0", A1:A20)

You might want to be sure you understand what question this formula answers.

[saturnexcel]

Welcome to the forum.

Maybe =SUMIF(B1:B20, ">0") / SUMIF(B1:B20, ">0", A1:A20)

You might want to be sure you understand what question this formula answers.

Thanks for the welcome. I want a weighted profit percentage. Basically, I'd like to know how much profit I'm making based on the cost of a unit.

Your formula isn't averaging the percentages, but rather an overall average. Thus, it's not a weighted average.

I got a little closer with this:

Please Login or Register  to view this content.

It works if I specify a range (e.g. - I use B1:B20 and so forth). Doesn't work as typed though.

[shg]

Array formula: =AVERAGE(IF(B1:B20>0, B1:B20 / A1:A20))

[saturnexcel]

Array formula: =AVERAGE(IF(B1:B20>0, B1:B20 / A1:A20))

Hey, that's much closer. I don't want to limit it to rows 1-20, though. However, when I took out all the row number references:

Please Login or Register  to view this content.

it didn't work. Should I just change the 20 to something really big (e.g. - 10000000) and just deal with it or is there a better way?

[shg]

Use dynamic named ranges. You don't want Excel unnecessarily evaluating whole columns.

[saturnexcel]

Hey, that's much closer. I don't want to limit it to rows 1-20, though. However, when I took out all the row number references:

Please Login or Register  to view this content.

it didn't work. Should I just change the 20 to something really big (e.g. - 10000000) and just deal with it or is there a better way?

Oh wait, I think I had problems because my first row has header info in it. I need something like B2:BMAX. Does such a thing exist?

[saturnexcel]

Use dynamic named ranges. You don't want Excel unnecessarily evaluating whole columns.

Ok, I will look into that. Thanks!

[shg]

You’re welcome. Would you please mark the thread as Solved?

Click the Edit button on your first post in the thread

Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes