Multiple functions in one cell

[Guest]

I am trying to build a spreadsheet so I can track my times at work. I am
having trouble trying to create a formula for 1 cell that will sum all the
days of the month, and add a 1 for every 8 hours of standby time example:
01/02/05 4 hours of standby, 01/03/05 2 hours of standby, 01/04/05 8 hours of
standby, 01/05/05 2 hours of standby, with the total being 16 I want that
cell to calculate too 2.
Thanks for any help you can provide!!
Kevin.

 

[Dave Peterson]

If you put your data into two columns, you're life will become lots easier.

And if you have your data already entered, maybe you can use Data|Text to
columns to separate it into those two columns.

Choose delimited by space.

Then you can just sum that second column and divide by 8.

But you could use a formula like:

=SUMPRODUCT(--MID(A1:A5,FIND(" ",A1:A5)+1,255))/8
(adjust the range to match your data (in both spots!).)

But you'll find excel lots easier to work with if you separate different fields
into different columns.

 

[Guest]

Use sumif. it is in the help files, too.
Try this, these 3 line formulas are really one long formula each, the
formating here wrapped them. the result of the formula is above each one,
according to the sample input table below.
Note: use 'roundup' if you get a full "1" for less than 8h standby, use
'round' if you can round up 5 or more sby hours to the next "1" and
'rounddown' if they are a bunch of scrooges. lol

Overtime Rate 1.5 cell F5
Standby Conversion 8 cell F6

Time Charged rounded down 46
SUM(SUMIF($F$15:$G$35,"Regular",$G$15:$G$35),($F$5*SUMIF($F$15:$G$35,"Overtime",$G$15:$G$35)),ROUNDDOWN(SUMIF($F$15:$G$35,"Standby",$G$15:$G$35)/$F$6,0))
Time Charged rounded off 46
SUM(SUMIF($F$15:$G$35,"Regular",$G$15:$G$35),($F$5*SUMIF($F$15:$G$35,"Overtime",$G$15:$G$35)),ROUND(SUMIF($F$15:$G$35,"Standby",$G$15:$G$35)/$F$6,0))
Time Charged rounded up 47
SUM(SUMIF($F$15:$G$35,"Regular",$G$15:$G$35),($F$5*SUMIF($F$15:$G$35,"Overtime",$G$15:$G$35)),ROUNDUP(SUMIF($F$15:$G$35,"Standby",$G$15:$G$35)/$F$6,0))
E15:E35 F15:F35 G15:G35
Day 1 Regular 8
Overtime 1
Standby 3
Day 2 Regular 8
Overtime
Standby 4
Day 3 Regular 8
Overtime 1
Standby
Day 4 Regular 8
Overtime
Standby 3
Day 5 Regular
Overtime
Standby 8
Day 6 Regular
Overtime
Standby 8
Day 7 Regular 8
Overtime
Standby 1