Help please!!! writing excel formula.....

[amber12177]

so I can't seem to figure this out. my problem is this....

so here's an example:

A B C D E F G H
NAMES SCHEDULE DAILY HRS SCHEDULE DAILY HRS SCHEDULE DAILY HRS TOTAL HRS-LUNCH


what i need the formula to do is take each daily hrs cell (c,e,g) if a cell is 6 or over it should subtract .75 but if it is under 6 leave the value as is,...then it needs to add all values into cell H

so far I'm not really having any luck......
any advice would be greatly appreciated!!

 

[isabelle]

hi,

=IF(C2>=6,C2*0.75,C2)+IF(E2>=6,E2*0.75,E2)+IF(G2>=6,G2*0.75,G2)

isabelle

Le 2014-06-04 19:25, (e-mail address removed) a écrit :

 

[isabelle]

it should subtract .75

oops: change 0.75 by 0.25
isabelle

Le 2014-06-04 23:19, isabelle a écrit :

 

[joeu2004]

so I can't seem to figure this out. so here's an example:
A B C D E F G H
NAMES SCHEDULE DAILY HRS SCHEDULE DAILY HRS SCHEDULE DAILY HRS TOTAL
HRS-LUNCH
what i need the formula to do is take each daily hrs cell
(c,e,g) if a cell is 6 or over it should subtract .75
but if it is under 6 leave the value as is,...then it
needs to add all values into cell H

=C2+E2+G2 - 0.75*((C2>=6)+(E2>=6)+(F2>=6))

But you said that is "an example". I am concerned that might not scale well
if the number of columns is significantly more than 3.

Alternatively:

=SUMPRODUCT(--(C$1:G$1="daily hrs"), C2:G2 - 0.75*(C2:G2>=6))

Change G to last column that contains Daily Hrs.

Note that the dollar sign ("$") in C$1:G$1 and the absence of the dollar
sign in C2:G2 is purposeful.

 

[Alex Plantema]

(e-mail address removed) schreef in

A B C D E F G H
NAMES SCHEDULE DAILY HRS SCHEDULE DAILY HRS SCHEDULE DAILY HRS TOTAL
HRS-LUNCH

what i need the formula to do is take each daily hrs cell (c,e,g) if
a cell is 6 or over it should subtract .75 but if it is under 6 leave
the value as is,...then it needs to add all values into cell H

Put this in H2:
=IF(C2<6,C2,C2-0.75)+IF(E2<6,E2,E2-0.75)+IF(G2<6,G2,G2-0.75)

 

[amber12177]

Alex thank you soo much!!! I worked like a charm!

 

[amber12177]

So, I now have just one piece of the puzzle left, but am struggling with the formula once again...

Example:


B4= start time (7:45am)
D4= end time (6:15pm)
F4= total hours elapsed (10.50)

I don't want to use military time, and I need the total hours elapsed expressed quarter hour increments (ex: 6.25 or 4.75....etc.)

any ideas??