chparkervatech
New Member
- Joined
- Feb 13, 2013
- Messages
- 6
I am attempting to average cells from an assessment model which uses an ordinal scoring system. 0-15 = N, 16-50=P, etc.
I average the results from different focus areas. The focus areas contain a range of cells that include rows and columns. There are seven focus areas being assessed. I am using the following to determine the letter score and the later to determine the number score for each focus area:
=IF(AND(G8>-1,G8<16),"N",IF(AND(G8>15,G8<51),"P",IF(AND(G8>50,G8<86),"L",IF(AND(G8>85,G8<101),"F",""))))
=AVERAGE(C4:F4)
There is a summary sheet that displays all of the focus area socres and updates them based on the cell reference from the prior sheet. The summary sheet includes the focus area scores and also an overall score which is an average of the 7 focus area. What I am trying to do is create an IF statement for the overall score where IF one or two or three, etc. of the focus areas has not been assessed or is blank (which results in a #DIV/0!), I want to disregard those blank/errors and average the remaining others.
The problem I am running into is 1) When the focus areas scores are blank I receive a #DIV/0! error. Which is fine, however, I need to create a formula that is simple and does spell out all of numerous possiblilites of different focus areas being #DIV/0! and some not. I started with something like this which works for two focus areas, but then I obviously found out the possiblilites are nearly endless. Please help.
=IF(ISERROR(G8),G10,IF(ISERROR(G10),G8,IF(AND(G8>-1,G10>-1),AVERAGE(G10,G8),"")))
I average the results from different focus areas. The focus areas contain a range of cells that include rows and columns. There are seven focus areas being assessed. I am using the following to determine the letter score and the later to determine the number score for each focus area:
=IF(AND(G8>-1,G8<16),"N",IF(AND(G8>15,G8<51),"P",IF(AND(G8>50,G8<86),"L",IF(AND(G8>85,G8<101),"F",""))))
=AVERAGE(C4:F4)
There is a summary sheet that displays all of the focus area socres and updates them based on the cell reference from the prior sheet. The summary sheet includes the focus area scores and also an overall score which is an average of the 7 focus area. What I am trying to do is create an IF statement for the overall score where IF one or two or three, etc. of the focus areas has not been assessed or is blank (which results in a #DIV/0!), I want to disregard those blank/errors and average the remaining others.
The problem I am running into is 1) When the focus areas scores are blank I receive a #DIV/0! error. Which is fine, however, I need to create a formula that is simple and does spell out all of numerous possiblilites of different focus areas being #DIV/0! and some not. I started with something like this which works for two focus areas, but then I obviously found out the possiblilites are nearly endless. Please help.
=IF(ISERROR(G8),G10,IF(ISERROR(G10),G8,IF(AND(G8>-1,G10>-1),AVERAGE(G10,G8),"")))
