A puzzle for you all

[featured_spectre]

So, I have been set a task, which I am not sure I can complete without help.

 

Basically I have a spreadsheet. If 1 cell falls below 80%, the score cannot go above 79.9%

if 2 fail, the score cannot go above 70%

if 3 fail, the score cannot go above 60%

if 4 fail, the score cannot go above 50%

if 5 fail, the score cannot go above 40%

 

average.xlsx

 

I have attached my base file

 

Cell B9 has a capped average IF statement in it, but I cannot work out how to make it so that if additional cells fall below, the threshold, the capped average score reduces by the appropriate amount.

 

Anyone able to help?

 

 

For anyone that wants to see what the code is so far:

 

=IF(
   AND(
       OR(B2<0.8,B3<0.8,B4<0.8,B5<0.8,B6<0.8),
       B8>0.8
   ),
   0.799,
   B8
)

[bald_pig]

If it's stupid, but it works, it ain't stupid, right?

 

=IF(COUNTIF(B2:B6,"<80%")=1,IF(AVERAGE(B2:B6)>=80%,79.9%,AVERAGE(B2:B6)),IF(COUNTIF(B2:B6,"<80%")=2,IF(AVERAGE(B2:B6)>70%,70%,AVERAGE(B2:B6)),IF(COUNTIF(B2:B6,"<80%")=3,IF(AVERAGE(B2:B6)>60%,60%,AVERAGE(B2:B6)),IF(COUNTIF(B2:B6,"<80%")=4,IF(AVERAGE(B2:B6)>50%,50%,AVERAGE(B2:B6)),IF(COUNTIF(B2:B6,"<80%")=5,IF(AVERAGE(B2:B6)>40%,40%,AVERAGE(B2:B6)))))))

[featured_spectre]

That has cracked it. You're a gem!!!

[bald_pig]

I feel like this needs explanation, the countif returns the number of cells that are below 80%, and it's a series of if statements, so if 1 cell is below 80%, 2 below 80% etc.

 

Within that, it's more if statements! if the average of the cells is above the threshold, return the threshold, else return the average.

 

I'm sure there's a neater way of doing it, but it's the only way I could think of doing it where I could cram it all in to one cell!

[featured_spectre]

It works for what I need, so I am happy

[TomHD]

We can make a small improvement to the solution offered the provided solution is not referencing a cell for the percentage value, if your percentage criteria ever changes you will have change the formula and any instances of it in use whereas if you were referencing you'd only need to change the value in that one cell

[bald_pig]

We can make a small improvement to the solution offered the provided solution is not referencing a cell for the percentage value, if your percentage criteria ever changes you will have change the formula and any instances of it in use whereas if you were referencing you'd only need to change the value in that one cell

 

I know, but he wanted it all in one cell, otherwise I'd break most of it out into separate cells.

[featured_spectre]

Well lets add this in....if it is 100%, then reference another cell value?

 

- - - Updated - - -

 

And I would happily look at your cluster options, just need to think how I can work it in

[bald_pig]

Well lets add this in....if it is 100%, then reference another cell value?

 

- - - Updated - - -

 

And I would happily look at your cluster options, just need to think how I can work it in

 

Basically anything that gets repeated goes in it's own cell, and the main formula references that instead!

[featured_spectre]

Well...lets say this....

 

There are 50 questions

 

5 are worth 3 points

45 are worth 1 point

 

How can we get excel to assign that as a percentage value? and still use the scoring value if the 5 questions are failed?

[featured_spectre]

I have this so far, based on your code...but doesn't work well

 

=IF(COUNTIF(D15:D19,"=100%"),IF(AVERAGE(D15:D19)=100%,D8-0%),IF(COUNTIF(D15:D19,"<80%")=2,IF(AVERAGE(D15:D19)>=80%,D8-20.1%,AVERAGE(D15:D19)),IF(COUNTIF(D15:D19,"<80%")=3,IF(AVERAGE(D15:D19)>70%,D8-30%,AVERAGE(D15:D19)),IF(COUNTIF(D15:D19,"<80%")=4,IF(AVERAGE(D15:D19)>60%,D8-40%,AVERAGE(D15:D19)),IF(COUNTIF(D15:D19,"<80%")=5,IF(AVERAGE(D15:D19)>50%,D8-50%,AVERAGE(D15:D19)),IF(COUNTIF(D15:D19,"<80%")=6,IF(AVERAGE(D15:D19)>40%,D8-60%,AVERAGE(D15:D19))))))))

[featured_spectre]

@bald_pig and @TomHD any ideas? sorry to bug you guys

[featured_spectre]

i got the 100% calculation thing sorted FYI, I am just interested in the questions having scores if you know how I can do that

[bald_pig]

Send us the spreadsheet example so I can see how you have laid out the questions and whatnot

[TomHD]

@bald_pig and @TomHD any ideas? sorry to bug you guys

 

So my solution piggy backs off the solution offered by Bald Pig, all I have done is added cell references to the formula so that any future changes to the score limits or thresholds only need changing in 1 place

Im not sure if I'm getting your problem correct but have a look at this... for featured_spectre.xlsx @featured_spectre

 

[/u]=IF(COUNTIF(B2:B6,"<"&D2)=1,IF(AVERAGE(B2:B6)>=D2,E2,AVERAGE(B2:B6)),IF(COUNTIF(B2:B6,"<"&D2)=2,IF(AVERAGE(B2:B6)>D3,D3,AVERAGE(B2:B6)),IF(COUNTIF(B2:B6,"<"&D2)=3,IF(AVERAGE(B2:B6)>D4,D4,AVERAGE(B2:B6)),IF(COUNTIF(B2:B6,"<"&D2)=4,IF(AVERAGE(B2:B6)>D5,D5,AVERAGE(B2:B6)),IF(COUNTIF(B2:B6,"<"&D2)=5,IF(AVERAGE(B2:B6)>D6,D6,AVERAGE(B2:B6)))))))[u]

 

Edited October 2, 2019 by TomHD

[featured_spectre]

Attached the file for you ...sample2.xlsx Edited October 2, 2019 by featured_spectre

[bald_pig]

I guess you want the result in the sheet 2 BD column?

[bald_pig]

Ok, this should be what you need in the BD column:

 

=(SUM((SUM(F2,J2,AK2,AM2,AP2)*3),G2:I2,K2:AJ2,AL2,AN2:AO2,AQ2:BC2)/60)

 

 

I've added extra brackets to section it out

(SUM(F2,J2,AK2,AM2,AP2)*3)

Tallies the three point answers and multiplies by three to give the correct points, this is within:

SUM(([i]3 point answers[/i]),G2:I2,K2:AJ2,AL2,AN2:AO2,AQ2:BC2)

To give the overall score, this is then divided by 60 (max number of points) to give your percentage.

[featured_spectre]

I owe you a beer