Aesthetic Question for Excel

[Jason Betts]

Hi guys! I know I keep bothering you about Excel, and this is not an Excel forum, but I have one more question. Can I set in a formula to automatically change the color of the output text in the cell under a certain condition? 

 

For example: 

I want to take the hourly rate from one cell and add the total tips received (net) from another cell, then divide by the number of hours worked to get the effective hourly rate when including net tips. The formula is =L17+J17/K17. One problem, this unnamed job has certain costs the worker must pay for, and the employer expects the employee to make up those losses from his or her tips. This usually skims some of their tips away, but on rare occasions if the tips are too low, the worker could actually lose money. If the net tips after expenses are a negative number, the new hourly rate would be less than the original hourly rate.

 

So how could I tell Excel to preform the function =L17+J17/K17, and if the output is less than L17 change the output text color to red?

 

Thanks! 

-Jason

[Ringthane]

Yes you can - it's called conditional formatting.  There should be something about it in the help, or you can see more here: https://support.office.com/en-us/article/Use-a-formula-to-apply-conditional-formatting-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f

[Jason Betts]

5 minutes ago, Ringthane said:

Yes you can - it's called conditional formatting.  There should be something about it in the help, or you can see more here: https://support.office.com/en-us/article/Use-a-formula-to-apply-conditional-formatting-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f

Thank you! The example they show is using dates and I want to use dollar amounts. Can this be adapted to work with less than or greater than values?

[Aleksbgbg]

9 minutes ago, Jason Betts said:

Hi guys! I know I keep bothering you about Excel, and this is not an Excel forum, but I have one more question. Can I set in a formula to automatically change the color of the output text in the cell under a certain condition? 

 

For example: 

I want to take the hourly rate from one cell and add the total tips received (net) from another cell, then divide by the number of hours worked to get the effective hourly rate when including net tips. The formula is =L17+J17/K17. One problem, this unnamed job has certain costs the worker must pay for, and the employer expects the employee to make up those losses from his or her tips. This usually skims some of their tips away, but on rare occasions if the tips are too low, the worker could actually lose money. If the net tips after expenses are a negative number, the new hourly rate would be less than the original hourly rate.

 

So how could I tell Excel to preform the function =L17+J17/K17, and if the output is less than L17 change the output text color to red?

 

Thanks! 

-Jason

How about you search these on microsoft website?

 

Go to:

Home --> Conditional Formatting --> New Rule --> Use a formula to determine which cells to format

 

Type:

=$[insertCellLetter]$[insertCellNumber] < $L$17

 

Go to:

Format --> Font --> Color --> Red --> OK --> OK

 

Applies to:

=$[insertCellLetter]$[insertCellNumber]

 

OK

 

Done.

 

[vinx]

2 minutes ago, Jason Betts said:

Thank you! The example they show is using dates and I want to use dollar amounts. Can this be adapted to work with less than or greater than values?

Yes, just use the option from the conditional formating > Highlight cell rules > choose what kind of rules do you need

greater than or less than. 

 

trial and error will do great

[Jason Betts]

On 9/17/2016 at 5:05 PM, Aleksbgbg said:

How about you search these on microsoft website?

 

Go to:

Home --> Conditional Formatting --> New Rule --> Use a formula to determine which cells to format

 

Type:

=$[insertCellLetter]$[insertCellNumber] < $L$17

 

Go to:

Format --> Font --> Color --> Red --> OK --> OK

 

Applies to:

=$[insertCellLetter]$[insertCellNumber]

 

OK

 

Done.

 

Thank you. I tried and I got this error...

 

 

It's probably a stupid mistake, maybe you indented for me to change the "insertCellLetter" and "insertCellNumber" part, but I don't understand what I'm doing well enough to understand what I'm changing those to. If I change them to the values of a given line, wouldn't that just create a rule exclusively for that line? 

 

I do not understand this!