Conditional Formatting after a sum calculation problem

[MagnumOpus]

Hi all,

I'm trying to learn how conditional formatting works, and I kind of have the gist of it but I can't get it to work after calculations. Here's what I'm trying to do:

I want an icon to appear after a sum calculation formula so that I can spot outliers easily. It needs to calculate the sum of 3 numbers (found in L2,M2,N2) and if that sum is between 5 and 22 then let a particular icon appear, but if it's outside that range, then let another icon appear. Icons should appear in cell O2. I'm going to copy the formula down the column so it doesn't need to use absolute references.

I'm not sure how to set it up because I'm confused on my syntax of commands. I know I probably need to use "countif" or "if", but where do I place the "sum" function?


Any help is greatly appreciated.

 

[barry houdini]

Try putting the sum in O2, i.e. in that cell use the formula

=SUM(L2:N2)

Now you can use conditional formatting with icon sets to display an icon based on the value in O2 (if you don't want to display the sum in O2 you can just tick the "Show icon only" box)

 

[MagnumOpus]

okay, that put me on the right track and thanks for the feedback (I guess I was making it more complicated than necessary)...but it caused me to run into a different problem.

In essence this is a statistical problem that I'm working on, so I'm looking at a bell curve of possible sums, with hopes of staying closer to the middle, and disregarding outliers (sums lower than 5 or higher than 22). What I'd like to do is somehow rate (with icons) the sums that fall closer to the middle of the curve (between 10-17) rating higher - perhaps with green flags or something, but sums falling more outside the middle of that curve (5-9, and 18-22) with possibly a yellow flag. A red flag would obviously show up for all sums outside 5 and 22.

If that's not possible, it's ok, but that would make this spreadsheet do what I need it to do perfectly and efficiently.