Help creating a conditional formatting rule with certain specifications

Hi, my homework is asking that I create a conditional formatting rule with certain specifications. I'm having a really hard time figuring how to do it. Here are the bullet points.

-Applies to the range A2:C47

-Uses a formula to determine which cells to format based on amounts of $100 or more and that are classified as business expenses. Because this conditional formatting applies to several columns (A, B, and C), you must use mixed cell references in the formula.

-Formats data with a Medium Green fill color and a Green line border using the Outline preset border style.

I'm going to upload a pic of what it is I'm working on. I will also attach the actual file of the assignment I'm working on. I really hope someone can help me with this as I've been stuck for an hour or so on this and I'm really not sure even where to begin. Please help

http://i122.photobucket.com/albums/o...g10/excel4.png

Originally Posted by FDibbins

Hi and welcome to the forum

Unfortunately it is not the forum's policy to do your homework for you

However, I will take a look at your file and see if I can give you some pointers and get you headed in the right direction

Okay, I appreciate any help at all. I guess I wasn't really expecting for someone to actually do it. Just stuck and need info on how to do this part of it. Thanks

Originally Posted by FDibbins

OK here goes...

When I need to create anything but the simplest of CF rules, I always create them in the worksheet 1st, that way I can see at once if they are working - and adjust/modify as needed. When I am happy that my formulas - formulated to return either a TRUE or FALSE - are giving me what I want, I can then use that formula and copy it into CF. So try that approach 1st

2nd, I see you have used TABLE, that makes understanding a formula (for me anyway) a bit harder, so I just type in the cell references instead of pointing to them for instance =Table1[[#This Row],[Amount]] is the same as =D2 - lot simpler huh?)

OK on to your problem...
you need to construct a formula that will test for the amount AND check for a "Yes" in the "business" column E. Because you want to have A:C all colored, there is a small twist to the formula - you need to absolute the column references

Once you have constructed that (relatively simple) formula, and tested that it works, you can open your CF, select NEW RULE and then select USE FORMULA and copy your formula into there.
You then need to format the rule as specified, Im sure you can manage that part though, right?

Good luck and give me a shout on how you make out please?

Because this is in my Information Systems class, they expect that I should already know much of how to do formulas in Excel. But I don't. I've made it far enough to adding the data bars in the amount cells. The book does not go deep enough in telling us how to use mixed cell references. I have tried to filter the table to only show the business expenses and from there, I'm stuck. What I'm stuck with is that I do not know how to apply the amounts of $100 or more AND that are classified business expenses. The output on the book shows that I should have 7 expenses grouped together at the top, all in medium green color, and each is $100 or more as well. I don't know how to come up with a formula that will get me there. I can get my way to only showing these 7 expenses, but everything else disappears.

So a few more things, I'm not sure if I'm strictly working on selected cells A2:C47 or if I need to filter anything else out first. I also do not know which Rule Type to work under.

Originally Posted by FDibbins

Filtering will only show what you want shown, and hide the rest, it wont change the color of the cells that match the specified criteria. So, for instance, as time goes on, and 1 row now exceeds the $100 mark (or another 1 drops below that, or a row is now classified as "Yes", you would have to apply the filter again...whereas CF would automatically change the color for you

To test for the 2 criteria, you need to use the AND() statement. The syntax for that is =AND(1st-test,2nd-test). You need to write a formula for the 1st test that will check to see if the amount is >=100, and then for the 2nd test, you need to write another formula that will see if the cell in the business column = "Yes"

Okay...I'll see what I come up with. So i'm going to be working on this =AND formula while highlighting A2:C47?

I tried =AND(A2:C47=>100) but that didn't work

Anyone?????