Nested "IF" with multiple criteria. Please help!

[Crystal Rose]

I am stuck.

I am working on a scorecard with several different metrics. of those metrics I have one called BA. The people that are within the scorecard are categorized as either "A" or "B"

"A" point values are as follows: Greater than or equal to 95% is worth 60 points, Greater than or equal to 93% to 94.99% is worth 45 points, Greater than or equal to 90% to 92.99% is worth 30 points, anything less than 90% is worth 0 points.

"B" point values are as follows: Greater than or equal to 93% is worth 60 points, Greater than or equal to 90% to 92.99% is worth 45 points, Greater than or equal to 87% to 87.99% is worth 30 points, anything less than 87% is worth 0 points.


the A/B categorization column is called LOB. I have only halfway figured out the formula without even adding the "B" value options yet. The formula I have so far is only yielding 60 for those 95% or greater, everything else is yielding "FALSE". I keep getting erred when posting the Formula, so apparently I cant even figure out how to use a forum. I could use a hug.

[Sam Capricci]

put spaces around any greater than or lesser than symbols, that will avoid the sucri error.
it would be helpful to know how the A and B people are linked. Are both formulas supposed to apply to the same data?
possibly a sample sheet with expected results would help.

[Crystal Rose]

I've attempted to attach the worksheets associated

[Crystal Rose]

The formula in question is on the F-BA tab in the BA Weight Column

[Sam Capricci]

Basically there are two different options for you...
=IFERROR(LOOKUP(C2,{0,0.9,0.93,0.95},{0,30,45,60}),"")
or
=IF([@BA]="","",IF([@BA]<0.9,0,IF([@BA]<0.93,30,IF([@BA]<0.95,45,60))))
or without the table references...
=IF(C2="","",IF(C2<0.9,0,IF(C2<0.93,30,IF(C2<0.95,45,60))))
or without accounting for blanks...
=IF(C2<0.9,0,IF(C2<0.93,30,IF(C2<0.95,45,60)))
didn't know what you wanted done with blanks so I added an if="","" then the rest of the formula (last formula given).

[Sam Capricci]

but based on what you posted I can't tell what you want done with the "B" section.
depending on how you want it treated I'd consider lookup tables.

[Crystal Rose]

I've just tried all those formula options in and they've all yielded the same result: 60 in every single E cell. :-/
At this point I'm not even concerned about the B option, its only used for those agents that have only been on the floor for a short period.
Did you try them out and get results showing other than 60?

[Crystal Rose]

OH WAIT!! I got it to work!! I just changed one thing. I used the second formula you provided but exchanged BA with LOB within the logical test. YOU ARE A LIFE SAVER!!!!!!

[Sam Capricci]

never mind since you got one or both to work.
good.
now, if you want to attack the "B" issue let me know.

[Crystal Rose]

Ill be sure to let you know. Thank you so much!

[Sam Capricci]

You're welcome AND thank you for the rep!