Need help with formula to reallocate points when N/A is present

[kariisa33]

Grading template for upload.xlsx
I have uploaded the grading template we are trying to use. I am hoping to get some help with a formula that will calculate the correct points when reallocating points when an N/A is present. The problem we are running into is the critical fail column is making everything weighted wrong. I hope I have explained enough but if not please feel free to ask away. I have been searching for an answer for this for months. Thanks in advance for any help you can give!

[alansidman]

Without some sample data in the spreadsheet to show what you mean is making this difficult to comprehend what you want.

[kariisa33]

Grading template for upload 2.xlsx
I have uploaded a new attachment with the way we have it calculated now. I need it to exclude the n/a's and reallocate points to the yes and no's. the light pink section is worth 75 and the purple is 75 as well for a total of 150. However I need the total of 150 to reallocated when an N/A is present in either section.

[alansidman]

I'm not getting it. I don't understand what you want. Where would the reallocation formulas go? What is the logic of the reallocation? This is just not clear to me. Please try and expand on your explanation. Tell us what the solution should be if your were to do it manually so that we can give you an excel solution.

[kariisa33]

I'm not getting it. I don't understand what you want. Where would the reallocation formulas go? What is the logic of the reallocation? This is just not clear to me. Please try and expand on your explanation. Tell us what the solution should be if your were to do it manually so that we can give you an excel solution.

I am beggining to think what I am asking to happen can't be done as I have searched for a long time before posting here.
So step by step.
If you open the spreadsheet and look at the individual loan review tab, I will be using that as the example.
We have 15 lines that are individually rated into 8 different categories. The first 7 of each of the 15 are rated at 0.714 the way it is set up now. The last category is a critical fail and is rated at 5.

What I need is something that will reallocate the points in each (pink) row/column when there is an N/A.
If there is an N/A i need it to be worth zero and only yes or no's to have a value.
I also need to reallocate the (purple) column when there are N/A's present as well.
The pink rows equal a total of 75 points possible and the purple column total 75 points possible.

There has to be an easier way to do this. I just can't figure it out. I will take any suggestions.