Formula Help

Sarah Butterworth

Hello, I have built the following formula but for some reason the last part of the formula is pulling a blank result, can anyone see what I have done wrong please? If the result is 12 and Internal it pulls blank instead of Creds / prior slides. Everything else works. Thank you.

=IF(AND([Total Score]@row >= 35, [Total Score]@row <= 45), "Must Win", IF(AND([Total Score]@row >= 25, [Total Score]@row <= 34), "Strong Response", IF(AND([Total Score]@row >= 18, [Total Score]@row <= 25), "Informed creds", IF([Total Score]@row < 18, IF([Request type]@row = "RFP", "Reject", IF([Total Score]@row < 10, IF([Request type]@row = "Internal", "Reject", IF(AND([Total Score]@row >= 10, [Total Score]@row <= 17), IF([Request type]@row = "Internal", "Creds / prior slides", "Reject")))))))))

Condition 1

If score is between 35 and 45 then return “Must Win”

 

Condition 2

If score is between 25 and 34 then return “Strong response”

 

Condition 3

If score is between 18-25 then return “informed creds”

 

Condition 4

If score is between 10-17 AND Internal then return “Creds Only”

 

Condition 5

If score is equal to or under 17 AND RFP then return “Reject”

 

Condition 6

If score is  equal to or under 9 AND Internal then return “Reject”

Sing C

Try this @Sarah Butterworth:

=IF(AND([Total Score]@row >= 35, [Total Score]@row <= 45), "Must Win", IF(AND([Total Score]@row >= 25, [Total Score]@row <= 34), "Strong Response", IF(AND([Total Score]@row >= 18, [Total Score]@row <= 25), "Informed creds", IF([Total Score]@row < 18, IF([Request Type]@row = "RFP", "Reject", IF(AND([Request Type]@row = "Internal", [Total Score]@row < 9), "Reject", IF([Request Type]@row = "Internal", "Creds / prior slides", IF(AND([Total Score]@row >= 10, [Total Score]@row <= 17), "Reject", "Reject"))))))))

• A slight adjustment to the logic to capture that scenario.

• Hope that solves it for you!

Sing C

Hi @Sarah Butterworth,

Give this a try:

=IF(AND([total score]@row >= 35, [total score]@row <= 45), "Must Win", IF(AND([total score]@row >= 25, [total score]@row <= 34), "Strong Response", IF(AND([total score]@row >= 18, [total score]@row <= 25), "Informed creds", IF([total score]@row < 18, IF([request type]@row = "RFP", "Reject", IF([request type]@row = "Internal", "Creds / prior slides", IF(AND([total score]@row >= 10, [total score]@row <= 17), "Reject", "Reject")))))))

I think the issue is in this part of your original formula:

IF([Request type]@row = "Internal", "Creds / prior slides",

The final check for "Creds / prior slides" in the original is within the inner IF for "Reject" with a score between 10-17. Since the overall score is less than 18, it triggers the "Reject" chain, and even though the internal check passes, the formula doesn't evaluate that part.

I built a very basic sheet from the columns and values in the formula and it appears to have addressed the problem. Let me know if that sorts it for you.

Hope that helps!

Have a great day!

Sarah Butterworth

Thank you @Sing C every part of the formula works other than now if it is Internal and under 9 - it comes back with Creds / prior slides rather than Reject?

Sing C

Try this @Sarah Butterworth:

=IF(AND([Total Score]@row >= 35, [Total Score]@row <= 45), "Must Win", IF(AND([Total Score]@row >= 25, [Total Score]@row <= 34), "Strong Response", IF(AND([Total Score]@row >= 18, [Total Score]@row <= 25), "Informed creds", IF([Total Score]@row < 18, IF([Request Type]@row = "RFP", "Reject", IF(AND([Request Type]@row = "Internal", [Total Score]@row < 9), "Reject", IF([Request Type]@row = "Internal", "Creds / prior slides", IF(AND([Total Score]@row >= 10, [Total Score]@row <= 17), "Reject", "Reject"))))))))

• A slight adjustment to the logic to capture that scenario.

• Hope that solves it for you!