Simplify this formula for summing a row with multiple criterias

I have a need to find an average percentage for a given row of numbers when each cell is divided by three. The kicker is that I have to use multiple criteria per cell to then work out what the percentage should be. A bit hard to explain but I have a formula that works fine however I know that there is an easier way to write this using a SUM formula and ranges and possibly INDEX and MATCH but i just can't get my head around it...

So this is the formula that I have at the moment:

Please Login or Register  to view this content.

Basically this formula looks through the range of C6:AG6 (albeit one at a time which is what I know I can do in one hit with a range) and if the cell contains a 0,1,2 or 3 it then divides that cell by 3. If the cell is greater than 3 AND it doesn't contain an "x" then it makes that value a 1 (anything more than 3 is classed as 100%). If the value of the cell is "x" then it is given a 0. Once it does this for each of the cells in that row it then adds them all together (I know this can be done by a SUM formula and a range) and then divides it by the value in cell AI6 to obtain and average percentage. Outside of all of this if there is an error it just displays a blank cell just to keep things looking nice.

Hopefully that makes sense. Basically I know that the above formula is very long winded and I am sure that there is a quick easy way to do this via an array formula or something but just can't get it to work. If someone could help out it would be great. The above formula works fine but just not knowing how to make it cleaner is just doing my head in....

Sorry if it is hard to read the formula.

Please post a sample file showing expected results.

Originally Posted by JohnTopley

Please post a sample file showing expected results.

Sorry didn't see this before I posted the table. Will the table be OK? That is pretty basic but it is all I am after.

Thanks that seems to actually work OK. Now just have to put it into a formula (if possible) to make that C6 reference be every cell in a range / row and then sum them together.

Originally Posted by XOR LX

Hi.

I think an actual workbook with an example together with expected result(s) would be of benefit.

Regards

How do I make that happen???? Dropbox link or something?

Hi again.
Click "Go Advanced" then paper clip icon to upload file.

There appears be inconsistencies in the logic: 0,1,2,3 - divide by 3 but3/3=1 so should we not do the following

SUM values >=1 and <=2 and divide result by 3 =SUMIF(A1:H1,"<=2")/3

COUNT number of entries >=3 =COUNTIF(A1:I1,">=3")

Add the above together =SUMIF(A1:H1,"<=2")/3 + COUNTIF(A1:I1,">=3")

With your data ... 1+4 =5

Test.xlsx

Did that work?

Just made up a quick spreadsheet to show you what I mean....

Originally Posted by JohnTopley

There appears be inconsistencies in the logic: 0,1,2,3 - divide by 3 but3/3=1 so should we not do the following

SUM values >=1 and <=2 and divide result by 3 =SUMIF(A1:H1,"<=2")/3

COUNT number of entries >=3 =COUNTIF(A1:I1,">=3")

Add the above together =SUMIF(A1:H1,"<=2")/3 + COUNTIF(A1:I1,">=3")

With your data ... 1+4 =5

WOW I think that works!!!

See attached:

Test2.xlsx

Yep looks like it works great. Pretty amazing really.... I was thinking waaaaaay to complicated.....

Thanks so much I can sleep now.

Yes: file received. Does my formula give the correct answers?

Sorry: posts crossed. If the problem is resolved, could you mark thread as SOLVED (See "Thread Tools" at top of the thread).

Thank you.

Originally Posted by JohnTopley

Sorry: posts crossed. If the problem is resolved, could you mark thread as SOLVED (See "Thread Tools" at top of the thread).

Thank you.

Will do mate. Thankyou so much.

Just updated it again now with the error handling to show blanks when no data. Works a treat.

Test3.xlsx