A simple sum the difference formula

  • Thread starter Thread starter office
  • Start date Start date
O

office

I have an hours worked threshold of 32.

I want to set a limit on cell H6 to 32, the contents of this cell changes
dynamically.
If the hours worked is 40 that means is 8 over threshold.
I want cell H6 to remain at the threshold limit of 32 and cell I6 to have
the difference between the threshold limit and the hours worked.

e.g.
a) Hours worked = 46.25 cell H6 = 32 cell I6 = 14.25

b) Hours worked = 29.75 cell H6 = 29.75 cell I6 = 0 or ""

Any help would be greatly appreciated.

Thanx in advance

Rick
 
Hi Rick!

If your total is in G6:

H6:
=MIN(G6,32)
I6:
=G6-H6

H6 returns the lowest of the entry in G6 or 32
I6 returns the difference which will be 0 if G6 < 32

If G6 could be blank, you'll get an annoying -32 in H6. You can avoid
this using:

H6:
=IF(G6="","",MIN(G6,32))
I6:
=IF(G6="","",G6-H6)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Try this

Hours in G6 46.25
Formula in H6 =IF(G6>32,32,G6)
Formula in I6 =IF(G6>32,G6-32,0)
 
Back
Top