formula help

[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

 

[Norman Harker]

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.

 

[Guest]

-----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

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.

 

[Harlan Grove]

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)

....

Follow that logic to its ultimate conclusion.

=MAX(0,SUM(C7:J7)-MAX(0,K5)-40)

 

[Harry Bo]

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

 

[Guest]

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.

 

[Andy B]

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.

 

[Guest]

Thank you that seems to work. Thanks tons!!!

-----Original Message-----
Hi

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

--
Andy.





.