cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MarkFay
Frequent Visitor

Conditional fill color from calculated field in SP

Hello,

 

I am trying to create a status bar of sorts indicating the amount of a form a user has completed after submitted. What I would like to do is display the percentage of complete in four different stages or colors. 0-25% complete (Red), 25-50% (Orange), 50-75%(Yellow), and 75-100% (Green).

In my Sharepoint list I have a field that counts the number of blank fields on my list and I have it calculating a percentage. In my gallery I have a rectangle I would like to fill with the appropriate color. I think I am close because I can have it display 1 of 2 colors  with this statement If(Round(Value(ThisItem.ColCount),0) >=50 Green,Red) Which obviously displays all records over 50 green and less than 50 red. So the question is how can I make this more granular?  

I was trying something like 

If(Round(Value(ThisItem.ColCount),0) >75 And <100, Green || If(Round(Value(ThisItem.ColCount),0) >50 And <75, Yellow......and so on. Clearly "And" is not right ..

 

Any thoughts would be appreciated.

 

Mark

1 ACCEPTED SOLUTION

Accepted Solutions
Jagadeeshk
Responsive Resident
Responsive Resident

it should written in sequence manner ex: if 0-25 red, 25-50, orange, 50-75 blue,  75-100 green then

option 1

code will be, first condition for 75-100, then 50-75, 25-50, 0-25 like that,

 if(value>75, green, if(value>50, blue, if(value>25,orange, if(value>0, red))))

 

option 2

with min max condition 

if( and(value>=0 , value<=25), red, if(and(value>25, value<=50),orange,if(and(value>50, value<=70),blue,if(and(value>75, value<=100),green))))

 

View solution in original post

4 REPLIES 4
Jagadeeshk
Responsive Resident
Responsive Resident

You have to use nested ifs, like if(condition1, color1,if(condition2, color2, if(condition3, color3,color4)))

Thanks,

 

any ideas  how do I write the condition of “in between” 0-25, 25-50 etc?

 

mark

Jagadeeshk
Responsive Resident
Responsive Resident

it should written in sequence manner ex: if 0-25 red, 25-50, orange, 50-75 blue,  75-100 green then

option 1

code will be, first condition for 75-100, then 50-75, 25-50, 0-25 like that,

 if(value>75, green, if(value>50, blue, if(value>25,orange, if(value>0, red))))

 

option 2

with min max condition 

if( and(value>=0 , value<=25), red, if(and(value>25, value<=50),orange,if(and(value>50, value<=70),blue,if(and(value>75, value<=100),green))))

 

View solution in original post

Perfect!! That worked..

 

Thank you!

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Top Kudoed Authors
Users online (68,682)