Need help with buried IF statements.

M. David

I need help with a formula that I think will have multiple IF statements. Please see below.

I am trying to determine the Maint Tech YTD Score. For the formula, we will only be using the "Community Average Score", "Company Wide Satisfaction Avg.", and the "Score Delta". We are comparing the Score Delta to the Company Wide Satisfaction Avg. If the Score Delta is plus or minus 2 points, we multiply the Community Average Score by 1. If the Score Delta is 3 or more points above the Company Wide Satisfaction Avg., they get a 1.1 multiplier. If the Score Delta is 3 or more points below the Company Wide Satisfaction Avg., they get a .9 multiplier.

I just realized I need to get clarification from my colleague on what to do with a score of 2.6. I am guessing the actual cutoff point will be 2.9. If a formula is offered up prior to getting clarification, I can adjust the formula suggested. Thanks for your help with this. As always, it is greatly appreciated.

Paul Newcome

Ah. Ok. Let's try this then...

=[Community Average Score]@row * IF([Score Delta]@row >= 0.03, 1.1, IF([Score Delta]@row <= -0.03, 0.9, 1))

Paul Newcome

Try this one...

=[Community Average Score]@row * IF([Score Delta]@row - [Company Wide Satisfaction Avg.]@row >= 0.03, 1.1, IF([Score Delta]@row - [Company Wide Satisfaction Avg.]@row <= -0.03, 0.9, 1))

Basically it says that if [Score Delta] is 0.03 (or 3%) greater than the [Company Wide] then multiply by 1.1. If it is less than 0.03 then multiply by 0.9. Everything in between gets multiplied by 1.

M. David

For some reason, I'm not getting that to work. I've played with changing the percentage on the 2020 Resident Satisfaction Score, and it is not altering the final Maint Tech YTD Score at all. I did get clarification that the cutoff point is 2, so I did make that change in your formula. Do you have any ideas what is not working in the formula? I think it might be something to do with the "Company Wide Satisfaction Avg." part. I don't think you actually need this in the formula. Wouldn't the formula just look at the Score Delta, and use the number there to determine what to multiply the "Community Average Score" by?

Paul Newcome

In your post you said that you were comparing the Score Delta to the Company Wide Satisfaction Avg. for the variation. The variation drives the multiplier. Right?

How exactly are your percentages being entered? Are you just entering a number into a column that is formatted for percentages so that the % symbol is automatic?

M. David

My apologies, but I think I explained part of this incorrectly. The Score Delta already compares the 2020 Resident Satisfaction Score with the Company Wide Satisfaction Avg. The Score Delta is the difference between the two. I think the formula will just be looking at the Score Delta, and then evaluate if its +/- 2, etc.

Yes on the percentages question. A number is entered and the columns are already formatted as a percentage.

Paul Newcome

Ah. Ok. Let's try this then...

=[Community Average Score]@row * IF([Score Delta]@row >= 0.03, 1.1, IF([Score Delta]@row <= -0.03, 0.9, 1))

M. David

Thanks for the help. This solution does work for all of the score combinations I tested it with. I appreciate your time on this. Nested IF statements are not my strong suit.

Paul Newcome

Happy to help. 👍️