0

I have a formula for calculating rankings of certain events across different organizations:

=AVERAGEIFS(C2:C100, B2:B100, "A", C2:C100, ">0") * IF(COUNTIF(B2:B100, "A") < 3, 0.7, IF(COUNTIF(B2:B100, "A") < 10, 0.9, IF(COUNTIF(B2:B100, "A") > 30, 1.1, IF(COUNTIF(B2:B100, "A") > 50, 1.2, 1))))

This formula works, but for some reason, using data that I have, I know the AVERAGEIFS value for "A" should be multiplied by 1.2, as there are more than 50 instances of "A" in Col. B. The result the above formula gives multiplies by 1.1.

I've also tried to expand the above formula to incorporate additional factors that could affect the ranking:

=AVERAGEIFS(C2:C100, B2:B100, "A", C2:C100, ">0") * IF(COUNTIF(B2:B100, "A") < 3, 0.7, IF(COUNTIF(B2:B100, "A") < 10, 0.9, IF(COUNTIF(B2:B100, "A") > 30, 1.1, IF(COUNTIF(B2:B100, "A") > 50, 1.2, 1)))) * IF((AND(B2:B100, "A"), COUNTIF(S2:S100, "yes")>1), 1.1, 1))

Running this second formula gives me an error message: "The formula you typed contains an error."

Not sure what's going wrong in the second formula, as I've continued to build on the first formula above.

pnuts
  • 58,317
  • 11
  • 87
  • 139
premiumcopypaper
  • 165
  • 1
  • 13
  • I am going to make an assumption, but the problem is with the last part `IF((AND(B2:B100, "A"), COUNTIF(S2:S100, "yes")>1), 1.1, 1)` Here is the assumption: `IF((AND(Countif(B2:B100, "A")>1, COUNTIF(S2:S100, "yes")>1), 1.1, 1)` You are missing one of the countifs for the. You will need to change the >1 I put in to what you want. – Scott Craner Dec 11 '15 at 15:46
  • Not sure why I would need the extra `COUNTIF`. The formula is not counting anything in Column B, just making sure that Organization A is present. If Organization A is present, then the formula should count the number of times "Yes" appears in Column S, if >1 then the `AVERAGEIFS` value should be multiplied by 1.1, otherwise 1 – premiumcopypaper Dec 11 '15 at 18:13

1 Answers1

1

Since 30 is less than 50 your formula (which short-circuits) never gets as far as what is more than 30 as a separate condition. Maybe something like: 

=AVERAGEIFS(C2:C100, B2:B100, "A", C2:C100, ">0") * IF(COUNTIF(B2:B100, "A") < 3, 0.7, IF(COUNTIF(B2:B100, "A") < 10, 0.9, IF(COUNTIF(B2:B100, "A") > 50, 1.2,1))))

This relies in the default factor of 1 for values of 10 to under 30.

Might be easier to see what is happening with: 

LOOKUP(COUNTIF(B2:B100,"A"),{0,3,10,30,50},{0.7,0.9,1,1.1,1.2})

after the asterisk.

pnuts
  • 58,317
  • 11
  • 87
  • 139