Formula HELP!

%3CLINGO-SUB%20id%3D%22lingo-sub-1650218%22%20slang%3D%22en-US%22%3EFormula%20HELP!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1650218%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20%22Total%20Hours%22%20column%20(G)%20and%20a%20%22Price%22%20column%20(H).%20I%20want%20a%20column%20that%20takes%20the%20Price%20and%20divides%20it%20by%20the%20Hours%20to%20get%20an%20average%20of%20what%20we%20are%20billing%20per%20hour.%20I%20know%20that%20formula%20to%20be%20%3Dsum(H%2FG).%20However%2C%20in%20addition%20to%20that%2C%20I%20want%20it%20to%20turn%20RED%20if%20it's%20below%20%2455%20and%20GREEN%20if%20it's%20above%20%2455.%20Can%20anyone%20assist%20in%20this%20formula%20that%20LINKS%20with%20the%20sum%20formula%20already%20being%20used%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1650218%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1650320%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20HELP!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1650320%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F787639%22%20target%3D%22_blank%22%3E%40KristenHanson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESet%20the%20fill%20color%20(or%20font%20color)%20of%20the%20cell%20with%20the%20formula%20to%20red.%20This%20will%20be%20the%20default.%3C%2FP%3E%0A%3CP%3EWith%20the%20cell%20selected%2C%20click%20Conditional%20Formatting%20%26gt%3B%20New%20Rule...%20on%20the%20Home%20tab%20of%20the%20ribbon.%3C%2FP%3E%0A%3CP%3ESelect%20'Format%20only%20cells%20that%20contain'.%3C%2FP%3E%0A%3CP%3ELeave%20the%20first%20dropdown%20set%20to%20'Cell%20Value'.%3C%2FP%3E%0A%3CP%3ESelect%20'greater%20than'%20from%20the%20second%20dropdown.%3C%2FP%3E%0A%3CP%3EEnter%2055%20in%20the%20box%20next%20to%20it.%3C%2FP%3E%0A%3CP%3EClick%20Format...%3C%2FP%3E%0A%3CP%3EActivate%20the%20Fill%20tab%20(or%20the%20Font%20tab%20if%20you%20want%20to%20specify%20the%20font%20color).%3C%2FP%3E%0A%3CP%3ESelect%20green.%3C%2FP%3E%0A%3CP%3EClick%20OK%2C%20then%20click%20OK%20again.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I have a "Total Hours" column (G) and a "Price" column (H). I want a column that takes the Price and divides it by the Hours to get an average of what we are billing per hour. I know that formula to be =sum(H/G). However, in addition to that, I want it to turn RED if it's below $55 and GREEN if it's above $55. Can anyone assist in this formula that LINKS with the sum formula already being used?

3 Replies
Highlighted

@KristenHanson 

Set the fill color (or font color) of the cell with the formula to red. This will be the default.

With the cell selected, click Conditional Formatting > New Rule... on the Home tab of the ribbon.

Select 'Format only cells that contain'.

Leave the first dropdown set to 'Cell Value'.

Select 'greater than' from the second dropdown.

Enter 55 in the box next to it.

Click Format...

Activate the Fill tab (or the Font tab if you want to specify the font color).

Select green.

Click OK, then click OK again.

 

Highlighted

@Hans Vogelaar Worked perfect! You're the best, thanks so much!

Highlighted

@KristenHanson 

 

You wrote: I have a "Total Hours" column (G) and a "Price" column (H). I want a column that takes the Price and divides it by the Hours to get an average of what we are billing per hour. I know that formula to be =sum(H/G)

 

And I just wanted to let you know that there is a more direct formula:

=Hx/Gx    (where x is the number designating the row involved)

Which is to say, you don't need the SUM function in there at all.