Ivan
|
| posted on 25/2/14 at 07:24 AM |
|
|
Help with Excel
Hi
Anyone know a shortcut to putting dollar signs in formulas like converting this "=SUMIF(I5:I44,B51:B59,G5:G44)" to this
"=SUMIF(I$5:I$44,B$51:B$59,G$5:G$44)"
|
|
|
|
|
Barkalarr
|
| posted on 25/2/14 at 07:31 AM |
|
|
Highlight the part you want and then press F4
|
|
|
Ivan
|
| posted on 25/2/14 at 07:36 AM |
|
|
Give that man a medal  - why didn't I ask this question years ago 
For the dummies amongst us each time you push F4 it changes where the $ sign appears.
[Edited on 25/2/14 by Ivan]
|
|
|
Barkalarr
|
| posted on 25/2/14 at 08:00 AM |
|
|
Here's another couple of short cuts I use all the time .....
To format cells to currency - highlight the cells or column then press <ctrl> <shift> 4
(4 being the dollar sign)
This also works with %
<ctrl> <shift> 5
|
|
|
Ivan
|
| posted on 25/2/14 at 08:05 AM |
|
|
Thanks - will remember that
|
|
|
Agriv8
|
| posted on 25/2/14 at 10:03 AM |
|
|
Use this amost as much as 'ALT' and 'Print Screen'.
copies current 'Active Window' to clipboard rather than Full page ( whish is sisable on twin 19 inch monitors ).
Reagrds
Agriv8
Taller than your average Guy !
Management is like a tree of monkeys. - Those at the top look down and see a tree full of smiling faces. BUT Those at the bottom look up and see a
tree full of a*seholes .............
|
|
|
omega0684
|
| posted on 25/2/14 at 04:17 PM |
|
|
Just google excel shortcuts and there are thousands!
I love Pinto's, even if i did get mine from P&O!
|
|
|
coozer
|
| posted on 25/2/14 at 05:38 PM |
|
|
Anyone help me with a formula for time = hours?
I'm after a formula that takes the human error out of this..
18.00 to 07.30 -45mins = ?? Answer is obviously 12.75 but is there a way to do it automatic like?
1972 V8 Jago
1980 Z750
|
|
|
nick205
|
| posted on 25/2/14 at 07:30 PM |
|
|
quote:
Originally posted by Barkalarr
Here's another couple of short cuts I use all the time .....
To format cells to currency - highlight the cells or column then press <ctrl> <shift> 4
(4 being the dollar sign)
This also works with %
<ctrl> <shift> 5
Didn't know that one, very handy indeed
|
|
|
coozer
|
| posted on 25/2/14 at 08:01 PM |
|
|
CTRL + SHIFT + ~
Shifts format back to general.
So i have this formula that gives me total hours worked minus a 1 hour break, BUT in the decimal..
=SUM(G21-F21,1)-IF(WEEKDAY(C21,1)>0,1/24) = 0.46875
I need to just times by 24 to get the paid 11.25 hours for the result but it gives me an error if I put *24 on the end or in the middle..
Any advise for multiplying the formula?
1972 V8 Jago
1980 Z750
|
|
|
mcerd1
|
| posted on 26/2/14 at 09:48 AM |
|
|
^^ not sure what the format of your inputs are to this are in so tell me if I've misunderstood anything...
=SUM(G21-F21,1) = will give you 1 + Cell G21 - Cell F21 - that doesn't sound right to me, what is in these cells ?
WEEKDAY(C21,1) = will give you a 1 for sunday, 2 for monday..... and 7 for saturday
-IF(WEEKDAY(C21,1)>0,1/24) = if weekday is greater than 0 (which it always is) then subtract 0.0416777
this means that your taking a difference of two different times and adding 1 to it, then whatever the day of the week it will always subtract 1/24th
off the answer ??? tell me if I've missed something here
you may want to change to the other versions of weekday: like 2 instead of 1 so that sat/sun become 6 & 7 and you can just say if weekday < 6
then subtract 1 hour
otherwise you'll need a if weekday is between 1 and 7 style logic function instead
dates and times are a bit funny in excel... Ive never really got my head around how the stupid serial number thing works
if it helps any you can turn a number of hours, minutes and seconds into the excel decimal format using the =TIME(Hour,Min,Sec) which you can then
just format as time to display it correctly
but it doesn't like fractions of an hour in the hour section so you need to do a bit of pre processing to feed it integers in each
section....
(check the help file for the limitations & syntax of these functions)
I use this for my hours:
=TEXT(TIME((INT(AP5)),(60*(AP5-(INT(AP5)))),0), "hh:mm"
its working on a decimal sum of hours worked for each job in cell AP5 (I use multiple columns to add up across multiple start stop jobs each day)
for whatever reason I decided to use the TEXT function to format the result as 'hh:mm', but its so long ago I can't remember why I
did this rather than just format the cell..... I was probably planing to add something else that never happened
then the next bit make turns the hours and mins into an excel format time
the middle bits give the hours which are just the integer (INT) of the decimal time, and the min's which are the decimal minus the integer and
multiply by 60
but you may be better off avoiding excels decimal time stuff altogether and just working with the hours without telling excel thats what they are -
if you add up in hours rather than fractions of 24 then
that should give you some ideas to play with 
[Edited on 26/2/2014 by mcerd1]
-
|
|
|
coozer
|
| posted on 26/2/14 at 12:58 PM |
|
|
Forget all that, after sleeping on it came up with a much simpler way to determine shift length with breaks taken off...
=MOD(G21-F21,1)-H21
Where F21 is start time, G21 is end time and H21 is break length. These cells all formatted to time so 18:00 0:45 etc..
1972 V8 Jago
1980 Z750
|
|
|
