Excel: Help with IF function?

Question

I'm a dog walker - I'm trying to create a spreadsheet for clients and their dogs, when they've been walked and if I've been paid. I currently have columns for Date walked, Walk complete, Owed, and Paid.

I'm trying to add an extra column on the end of these to turn red if I've written "YES" in the Walk complete column, but nothing in the Paid column (to indicate that I'm due payment). Preferably, also to turn green once I've written "YES" in both the Walk complete and the Paid column (to indicate that payment has been dealt with).

I've tried adding a screen grab to make it all clearer but not sure if it's accepted it.

TIA

garbo7441 · Answer

As stated, you should use conditional formatting.  You do not specify which columns contain your data, so it is inferred that they are A:D.  Column B for 'walk complete' and column D for 'paid'.  Also, column E will be the indicator column.

Assuming your want to format 50 rows, select (highlight) range E1:E50.

In the Home tab, click the Conditional Formatting drop down in the 'Styles' group to the right.

Select 'New Rule'.

Select 'Use a formula to determine which cells to format' in the 'Select a Rule Type' section.

In the 'Format values where this formula is true' textbox, enter this formula:

=AND(B1="Yes",D1="Yes")

Click 'Format', select the Fill tab, select the shade of green you wish to use to format the cells.

Click 'OK' twice.

Repeat the above process, using this formula to indicate 'complete/not paid':

=AND(B1="Yes",D1="")

Select the particular shade of Red you wish to use.

If your columns are different adjust the formulas accordingly.

Note: Excel will automatically adjust the formula in cells E2:E50, or whatever range of cells you are formatting.

Anonymous · Answer

No - that can't be done (easily) using the IF function.

Use conditional formatting instead - much easier.

Trending News

Excel: Help with IF function?

2 Answers