Forum Discussion
Scoring list
Dear reader,
I made a scoring list. Most questions have a"Yes" or "No" option, the code I use is:
=IF(I22="Yes",2,IF(Sheet1!I22="No",-1,""))))+etc.
On top of the scoring sheet, I made a box that will show a percentage based on their scoring. If every question is answered "Yes" the score will be a 100% (based of max. 41 points).
This works perfectly.
But, I have a section with 2 separate questions. #1 If they added an attachment and #2 an attachment evaluation.
Code:
+IF(D45="Select",0,IF(D45="Yes",2,IF(D45="No",0,"")))+IF(D46="N/A",0,IF(D46="Poor",1,IF(D46="Fair",2,IF(D46="Excellent",3,"")))))
This is hard for me to explain, sorry if it's unclear:
If #1 is "No" Which results in #2 automatically being "N/A", I don't want this to affect the total percentage scoring. If everything else is answered "Yes" except for these two questions, the total scoring right now would be 90%, but I want it to be a 100%.
I only want these questions to count in the total if they are answered "Yes" + What scoring evaluation they get.
Is this achievable?
Kind regards,
It is always a lot easier to help people if, in addition to the description of the task--which you've provided--they post a copy of the spreadsheet. Unless there's something confidential in yours, which seems unlikely, could you please place a copy of the actual Excel spreadsheet on OneDrive or GoogleDrive and paste a link here that grants edit access to it.
That said, instead of nesting multiple levels of IF functions, you can profitably make use of the IFS function for testing multiple conditions. That enables a shorter formula, more readable, more understandable, and easier to maintain. Here's a description of it.
- Hi Mathetes,
Thank you for mentioning the IFS function document! I will try it out.
Here is the GoogleDrive link to my document. I have not applied any coding of the AFS document you provided. This is the original document:
https://docs.google.com/spreadsheets/d/11oSRFJnjLVMi5jd-NkgKXuJXga5vZ_0y/edit?usp=sharing&ouid=103858001730657634068&rtpof=true&sd=true
I marked the cells yellow that hold the coding for counting the points. I also made notes describing my wishes for this document in cell A45.
Kind regards,
FarahI'll try to look at this further later on this weekend. But just now, having looked at it, having seen the full formula that you've created, I have these suggestions.
- If you ever find yourself needing to write a formula with multiple nested IFs (not IFS..that's different, and I don't think IFS is what you need here), especially if that formula extends as long as yours does, don't do it. You've managed to make this one work without syntax errors, and that's commendable, but it's not a formula that's easily read and understood. Which means that next year, or even next month, if a change were to be required, you would have a hard time going back and doing the maintenance. And somebody else would not even be able to do it. (Seriously, the books on Excel all recommend against such long IF formulas for exactly those reasons.)
- So that leaves us needing to find a different way to accomplish the objective. And there are several options there. It looks to me as if your first 15 criteria follow the same pattern, so I'd suggest starting with a table that could be accessed via VLOOKUP, or INDEX and MATCH:
- Maybe most important, though, would be to not even try to do the scoring and the totaling all in a single formula as you're doing it. Use what we call "helper columns," maybe hidden but maybe not, depending on who sees it, whether it matters. (a) Score each one on its own in the helper column. (b) Then add the results.
- And some combination of that for the highlighted items in yellow....but the basic idea would be to score them independently and then incorporate the results into a single consolidated result as desired.
It may be very intellectually satisfying to write a single formula that accomplishes it all, and there are times when long formulas are unavoidable; I don't think this is one of those latter occasions.
