Excel Question

Page 1 of 1

1 Replies - 730 Views - Last Post: 22 January 2011 - 12:34 AMRate Topic: //<![CDATA[ rating = new ipb.rating( 'topic_rate_', { url: 'https://www.dreamincode.net/forums/index.php?app=forums&module=ajax&section=topics&do=rateTopic&t=211016&amp;s=cd744a1bbe7a196db3767134acd3ee11&md5check=' + ipb.vars['secure_hash'], cur_rating: 0, rated: 0, allow_rate: 0, multi_rate: 1, show_rate_text: true } ); //]]>

#1 Moshambi

• D.I.C Regular

Reputation: 8
• Posts: 280
• Joined: 20-November 07

Excel Question

Posted 20 January 2011 - 02:43 AM

Not sure if this is the right forum or if there is even one for Excel related problems.

Anyway,

I am making an excel sheet to display hours worked for the total pay that it is for and then figure out what the hourly rate would come out to for example:

```     In  Out Hours Pay Rate per hour
[c] [d] [e]   [f]     [g]
[4] 12pm 3pm  3    18      \$6
[5]  ..  ..  ..   ..       ..
[6]  ..  ..  ..   ..       ..

```

This is all working fine and heres where my problem is. I set up a grand totals box for each column: hours, pay, rate.

It will work fine if I use this:

```=SUM((HOUR(E4)+(MINUTE(E4)/60)))

```

So this will end up changing the Grand Total for hours to number format, something like 7, 8.23, 9.5, etc.

When I try to do it for all the cellss in that column though:

```=SUM((HOUR(E4:E8)+(MINUTE(E4:E8)/60)))

```

I get an error. How do I make it so it will add it like it does for the single cell but for each different cell. So after each iteration I want it to change the cell number if that makes sense.

Hope someone out there can help me with this and I appreciate any help! Thanks!

Is This A Good Question/Topic? 0

Replies To: Excel Question

#2 Ionut

• D.I.C Lover

Reputation: 386
• Posts: 1,057
• Joined: 17-July 10

Re: Excel Question

Posted 22 January 2011 - 12:34 AM

You cannot do operation per range(as you try). So sum up all hours then add a sum of minutes and then make that sum. Something like
```="SUM(HOUR(E4:E8)+(Sum(MINUTE(E4:E8))/60)))"

```

Also your first sum can be written like
```="HOUR(E4)+(MINUTE(E4)/60)"

```

Page 1 of 1

 .related ul { list-style-type: circle; font-size: 12px; font-weight: bold; } .related li { margin-bottom: 5px; background-position: left 7px !important; margin-left: -35px; } .related h2 { font-size: 18px; font-weight: bold; } .related a { color: blue; }