Multiple IIF statements for the value

Question

I'm working on a report for attendance and building a report for administration that shows students at Risk based on percentage.Want to change the percentage color based on the value for quicker viewing of issues. This is what I have so far but keep getting errors.

=IIf(Fields!absenteeismPercentage.Value <5, "Green"), IIf(Fields!absenteeismPercentage.Value > 5 and (Fields!absenteeismPercentage.Value < 9,"Yellow"), IIf(Fields!absenteeismPercentage.Value > 10,"Red","Black"))

stan oref · Accepted Answer · 2025-03-13 19:22:39Z

1

=IIf(Fields!absenteeismPercentage.Value < 5, "Green", 
    IIf(Fields!absenteeismPercentage.Value >= 5 And Fields!absenteeismPercentage.Value < 9, "Yellow", 
        IIf(Fields!absenteeismPercentage.Value >= 9 And Fields!absenteeismPercentage.Value <= 10, "Red", "Black")))

answered Mar 13 at 19:22

stan oref

192 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alan Schofield · Accepted Answer · 2025-03-13 20:04:41Z

1

Stan's answer is correct but you might find SWITCH easier to read and understand.

=SWITCH(Fields!absenteeismPercentage.Value < 5, "Green",
Fields!absenteeismPercentage.Value < 9, "Yellow",
Fields!absenteeismPercentage.Value <= 10, "Red",
True, "Black" )

SWITCH stops when it finds an expression that returns true, so it can simply the code as you don't need ranges.

So, "Yellow" will be returned is the value >=5 and <9 because the first test (<5) must have failed.

You might need to edit the values as your original code would have returned "Black" if the value was exactly 5.

The final True acts like and ELSE

answered Mar 13 at 20:04

Alan Schofield

21.9k3 gold badges25 silver badges38 bronze badges

Collectives™ on Stack Overflow

Multiple IIF statements for the value

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related