formula help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i need help =if(K7>40,K7-40,0)
now k7 is going to equal =sum(C7:J7)
but if K5 > .00 then K5 needs to be subtracted from K7

i need help righting this formula
 
Hi!

If I'm interpreting you correctly:

K7 formula becomes:
=IF(K5>0,SUM(C7:J7)-K5,SUM(C7:J7))

Leaving your original formula at:
=if(K7>40,K7-40,0)

Or merging the two formulas:
=IF((IF(K5>0,SUM(C7:J7)-K5,SUM(C7:J7)))>40,(IF(K5>0,SUM(C7:J7)-K5,SUM(C7:J7)
))-40,0)

But rather than:
=if(K7>40,K7-40,0)
Use:
=MAX(K7-40,0)

And merging you get:
=MAX(IF(K5>0,SUM(C7:J7)-K5,SUM(C7:J7))-40,0)
--
--
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.
 
-----Original Message-----
i need help =if(K7>40,K7-40,0)
now k7 is going to equal =sum(C7:J7)
but if K5 > .00 then K5 needs to be subtracted from K7

i need help righting this formula

.
that didn't seem to work. i messed up i gave you the
Click to expand...
wrong info sorry.

k5 is =SUM(C5:J5)
K7 is =sum(C7:J7)
K8 is =if(K7>40,K7-40,0)


if any number greater than .00 is in K5 that number needs
to be subtracted from K8 if no number is in k5 than k8 can
equal any number above 40 from k7.

example: if K5 has a value of 6, and then k7 has a value
47 after adding C7:J7. i would then need to be able to
subtract k5 the value of 6 from K7 value of 47.
 
Norman Harker said:
But rather than:
=if(K7>40,K7-40,0)
Use:
=MAX(K7-40,0)

And merging you get:
=MAX(IF(K5>0,SUM(C7:J7)-K5,SUM(C7:J7))-40,0)
Click to expand...
....

Follow that logic to its ultimate conclusion.

=MAX(0,SUM(C7:J7)-MAX(0,K5)-40)
 
Not sure about this but try:

=IF(AND(K7-K5>=40,K5>=0),K7-K5,0)

if I understand you right, or tell me otherwise

Harry
 
that isn't working. this might help if i explain better
what i am trying to complete.

k5 is =SUM(C5:J5) this cell is for sicktime or vacation

K7 is =sum(C7:J7) total hours worked with k5 included

K8 is =if(K7>40,K7-40,0) is overtime but if the employee
has any vacation time used in k5 that needs to be
subtracted before any overtime will appear. If no hours
are used for vacation in k5 then i want k8 to equal
anything over 40 from my total in k7.

Thank you for your Help.
 
Hi

Try:
=MAX(K7-K5-40,0)

--
Andy.


that isn't working. this might help if i explain better
what i am trying to complete.

k5 is =SUM(C5:J5) this cell is for sicktime or vacation

K7 is =sum(C7:J7) total hours worked with k5 included

K8 is =if(K7>40,K7-40,0) is overtime but if the employee
has any vacation time used in k5 that needs to be
subtracted before any overtime will appear. If no hours
are used for vacation in k5 then i want k8 to equal
anything over 40 from my total in k7.

Thank you for your Help.
Click to expand...
 
Back
Top