Forum Discussion
Solved
If/Then Statements with Possible AND/OR or Nesting Stmts
I have two tabs in the same Excel spreadsheet. The first tab is a page that will be completed by the end user, who will answer questions by placing an "x" in the column that they choose. Then, th...
Kim-Kay I'm really not sure if I understand what all you want but here is a first crack at it:
=IF(LEN(CONCAT(B2:F2)),IF(XOR(LEN(B2&C2),LEN(E2&F2)),SUMIF(B2:F2,"<>",Sheet2!B2:F2),Sheet2!D2),"")
BTW you completely excluded mention of the 'standard' rating and if that is universally accepted (as in the attached example) or should be treated as an exclusion in both cases or something else completely.
Not working for what I'm needing, but my explanation probably wasn't great, either. The charts you have are good and the values are pulling over. And yes, the "standard" rating is universally accepted, as in your example. But, the standard maximum isn't being applied.
To make it what I'm needing... only one column can be chosen for each question and each question is to have the default value assigned and shown in the Rating column (which you already have shown). But when figuring the total rating, there is a standard maximum that needs to apply this way: (1) If an inadequate or below standard rating is marked for any question in the section and no above standard or superior ratings are marked, then the sum of the default values that were chosen is to show, (2) Same if only standard ratings are chosen - the sum of the default values that were chosen is to show, (3) If above standard or superior ratings are chosen and no inadequate or below standard ratings are chosen, then the sum of the values that were chosen is to show, (4) If above standard or superior ratings are chosen, and inadequate or below standard ratings are chosen as well, I want the the total to only show as the total maximum sum that can show for the standard ratings.
For example, if I rate Q1 and Q2 as inadequate, Q3 and Q4 are standard, and Q5 is above standard, I want the total rating for the section to be 15 (which is the sum of all standard values possible). I am actually rating the job performance of people using the form and I don't want their insufficiency in one or more areas to pull down their total more than the standard rating if they are excelling in one or more other areas.
I hope this makes more sense that I think it sounds to myself!
To make it what I'm needing... only one column can be chosen for each question and each question is to have the default value assigned and shown in the Rating column (which you already have shown). But when figuring the total rating, there is a standard maximum that needs to apply this way: (1) If an inadequate or below standard rating is marked for any question in the section and no above standard or superior ratings are marked, then the sum of the default values that were chosen is to show, (2) Same if only standard ratings are chosen - the sum of the default values that were chosen is to show, (3) If above standard or superior ratings are chosen and no inadequate or below standard ratings are chosen, then the sum of the values that were chosen is to show, (4) If above standard or superior ratings are chosen, and inadequate or below standard ratings are chosen as well, I want the the total to only show as the total maximum sum that can show for the standard ratings.
For example, if I rate Q1 and Q2 as inadequate, Q3 and Q4 are standard, and Q5 is above standard, I want the total rating for the section to be 15 (which is the sum of all standard values possible). I am actually rating the job performance of people using the form and I don't want their insufficiency in one or more areas to pull down their total more than the standard rating if they are excelling in one or more other areas.
I hope this makes more sense that I think it sounds to myself!