Pls Help - blank cell/formula issue

[Shokra]

I am creating a Wages, Hour and Payroll summary for my fiancé becaus
he's trying to keep track how the new company is handling wages/tim
schedules, etc.

However, I'm struggling with a few calculations that I just cannot fin
solutions for online.

Basically my sheet is set to:
F G
10 Hours Overtime
11 Monday 8.5
12 Tuesday 8.5
13 Wednesday 9.5
14 Thursday 12.5
15 Friday 9.5
16 Saturday
17 Sunday
18 Total Wkly Hours:____ ____

The formula that I have in F-18 is as follows:
=((F12-0.5)+(F13-0.5)+(F14-0.5)+(F15-0.5)+(F16-0.5)+(F17-0.5)+(F18-0.5))
It totals the hours he works then minus' his non-paid 30 min break
The problem that I am running into is:
1 - Saturday & Sunday - I want the formula to bypass these cells (F16
F17) if they are blank

Now for the Overtime column (G11):
Basically I have this formulated to calculate his hours worked minus hi
non-paid break, then determine anything over the figure in F11
(formula: =(F12-8.5)
but if the F column has no figures, it is still calculated and gives th
G column a negative value :/

G18 just sums the figures in the G column and gives him a weekly tota
of overtime that can be applied to personal and/or holidays.

The problem is, if cell F16 and/or F17 are blank they are stil
computing the formula, I want it to ignore what is NULL/Blank

I know my formulas aren't very complex and could probably be done muc
better, but I am not an expert nor every day user of Excel. I ca
normally make calculations do what I want them to do just by googlin
information but in this case.. I cant, so if someone could please hel
me I would greatly appreciate it.

Thank you
Sh

 

[Claus Busch]

Hi,

Am Fri, 9 Nov 2012 18:45:39 +0000 schrieb Shokra:

Basically my sheet is set to:
F G
10 Hours Overtime
11 Monday 8.5
12 Tuesday 8.5
13 Wednesday 9.5
14 Thursday 12.5
15 Friday 9.5
16 Saturday
17 Sunday
18 Total Wkly Hours:____ ____

in F18 try:
=SUM(F11:F17)-COUNT(F11:F17)*0.5

in G11:
=IF(F11="",0,F11-8.5)
and copy down


Regards
Claus Busch

 

[Shokra]

Thank you Claus for your reply, I will definate try those after a fe
hours of sleep. However, most of the night I have recreated th
formulas and the following is now what I am working with. It woul
probably give much better details, etc.

Should have provided a bit more information before I tried to explai
the formula calculations problems I am having. I thought I could attac
the actual spreadsheet but I can't - I will try to explain each colum
and what I am "trying" to accomplish, maybe someone would be kind enoug
to assist me with my horrible mistakes. (Attached a PDF of the actuall
sheet).

As you can see I am creating a salary/payroll w/ hours, breaks and ove
hour calculation (only). Reason being is that overtime is not paid vi
time and half, but those accumolated hours become avialable to use fo
personal/holiday time off w/ pay (similar to "floating hours").

Description of Sheet & Calculations:

C9 thru L9
Each week of the A2 year/month & A9 beginging & ending wk day(s) of th
months pay period
A10-16 Days of Wk

Week 1 = work days from 29.10 - 04.11
C10 = 8.5 hrs wk
C11 = 8.5 hrs wk
C12 = 8.5 hrs wk
C13 = 8.5 hrs wk
C14 = 8.16 hrs wk (8 hrs 10 mins)
C15 = off
C16 = off

C17's formula is
=((C10-0.5)+(C11-0.5)+(C12-0.5)+(C13-0.5)+(C14-0.5)+(C15-0.5)+(C16-0.5))
It works - sort of! Basically I want it to add up add C10-C16, if th
work day is blank, it ignore that cell, then based on how many cell
have hrs entered it #ofcells *0.5 to obtain the hours of break tim
(unpaid), then breakhrs - Wk hrs = Total wkly hrs

However occassional, he works Saturday for a few hours and doesn't get
30 min (unpaid) break - How can I calculate unpaid breaks to specifi
days only?
Example: Sum((C10:C16)-(#ofcells*0.5))

I'm sure there is a formula out there that can do this unfortunately,
have not been able to fine one (they all do things I don't want them t
do). My current formula does work, but I think it may be what i
causing the error I receive when I reopen the document (see final erro
at the end of thread).
____________________________
D10 thru D16: calculates if the hours are more then reg scheduled hrs i
shows the overtime hours.

D1016's formulas are:
Mon - Thurs is: =IF(C10>8.5,(C10-8.5),0)
Fridays is: =IF(C14>8.16,(C14-8.16),0)

This took some time but I did get it working but only for Monday
Thursday. Once I apply the same concept to Friday (you see I manua
changed the hours), it shows an error.

Error: The formula in this cell differs from the formulas in this are
of the spreadsheet.
I can't figure out why

E-L #'s10-16 are just same as what is described above.
____________________________

Overtime Column:

D17's formula is: =SUM(D1016)
However, its not calculating and states: The formula in this cel
differs from the formulas in this area of the spreadsheet.
What am I doing wrong?

Everything else on the spreadsheet regarding his payroll is functionin
properly, to my knowledge.

Reopen File Error:
As soon as I open the document I get the following:
Circular Reference Warning:
One or more formulas contain a circular reference and may not calculat

correctly. Circular references are any references within a formula tha

depend upon the results of that same formula. For example, a cell tha
refers
to its own value or a cell that refers to another cell which depends o
the
original cell's value both contain circular references.
For more information about understanding, finding, and removing circula

references, click ok. If you wnat to create a circular reference, clic

cancel to continue.

I have spent quite a while trying to figure out which formula i
creating this error with no success. I really appreciate the tim
anyone takes to read my issues and assists with the problems.

Thank you
Sho

 

[Claus Busch]

Hi Shokra,

Am Sat, 10 Nov 2012 03:57:16 +0000 schrieb Shokra:

Description of Sheet & Calculations:

C9 thru L9
Each week of the A2 year/month & A9 beginging & ending wk day(s) of the
months pay period
A10-16 Days of Wk

Week 1 = work days from 29.10 - 04.11
C10 = 8.5 hrs wk
C11 = 8.5 hrs wk
C12 = 8.5 hrs wk
C13 = 8.5 hrs wk
C14 = 8.16 hrs wk (8 hrs 10 mins)
C15 = off
C16 = off

C17's formula is:
=((C10-0.5)+(C11-0.5)+(C12-0.5)+(C13-0.5)+(C14-0.5)+(C15-0.5)+(C16-0.5))
It works - sort of! Basically I want it to add up add C10-C16, if the
work day is blank, it ignore that cell, then based on how many cells
have hrs entered it #ofcells *0.5 to obtain the hours of break time
(unpaid), then breakhrs - Wk hrs = Total wkly hrs

you can't substract unpaid breaks if there is no worktime. If you only
get unpaid breaks with 6 or more hours of work then try in C17:
=SUM(C10:C16)-COUNTIF(C10:C16,">"&6)*0.5
In other case modify to suit.
Every time over 8.5 hours is overtime. Then in D10:
=IF(C10="",0,C10-8.5) and copy down. D10 to D13 = 0
D14 = -0.34 because she worked less than 8.5 hours. D15 and D16 = 0
because she didn't work.


Regards
Claus Busch

 

[Shokra]

Hi

Probably simpler and save on all that typing if you uploaded a sampl
spreadsheet (Dummy data)

Ive tried it wont allow me to upload it, not sure why.. I saved th
speadsheet as pdf and its only 1 page but states it too large - Ever
time I attempt its stating it is too large.. pdf file, even a zippe
file.. I'll try to upload it to the server and add a link in a while

 

[joeu2004]

Ive tried it wont allow me to upload it, not sure why.
I saved the speadsheet as pdf

No need to save as PDF. Probably better if you don't.

I have seen Excel files archived in zip files "attached" to postings
submitted through excelbanter.com. But I am not an ExcelBanter user, so I
cannot help you with that.

However, in general, you can upload an example Excel file (devoid of any
private data) that demonstrates the problem to any file-sharing website.

Then post the "shared", "public" or "view-only" link (aka URL; http://...)
in a response here.

The following is a list of some free file-sharing websites; or use your own.

Box.Net: http://www.box.net/files
Windows Live Skydrive: http://skydrive.live.com
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
RapidShare: http://www.rapidshare.com

 

[Shokra]

Claus - your formula is absolutely brilliant! I applie
=SUM(C10:C16)-COUNTIF(C10:C16,">"&6)*0.5 to cells C17 to K17.. I di
attach the excel sheet zipped but I can't remember now if it was befor
I made those changes or not

There is one problem with the formula though That is, it is no
removing the overtime hours. There is no overtime, it becomes like hm
"floating hours". So when I applied the formula to E17, it showed hi
paid hours as 43, when technically if you minus the break & th
overtime, he only has 39

So when it calculates his gross salary, its adding in those 4 extr
hours
I changed your formula to: =SUM(E10:E16)-COUNTIF(E10:E16,">"&6)*0.5
(F17) and it seems to function properly. What do you think of th
above, is it correctly done

I have two questions

1 - Overtime column(Friday).. formula is: =IF(C14>8.16,(C14-8.16),""
next weeks Friday is: =IF(E14>7.5,(E14-7.5),""
These days alternate each week, but always the same. I get the sam
error on both: Error: The formula in this cell differs from the formula
in this area of the spreadsheet. Is this actually an error of its i
just a notification

2 - When I sum the overtime columns to total in D17, etc, I get th
Error: The formula in this cell differs from the formulas in this are
of the spreadsheet. Is this actually an error of its it just
notification

well I'm going to try the zipped file again hope it works this time. :

+-------------------------------------------------------------------
|Filename: payroll.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=675
+-------------------------------------------------------------------

 

[Claus Busch]

Hi Shokra,

Am Sun, 11 Nov 2012 01:54:16 +0000 schrieb Shokra:

There is one problem with the formula though That is, it is not
removing the overtime hours. There is no overtime, it becomes like hmm
"floating hours". So when I applied the formula to E17, it showed his
paid hours as 43, when technically if you minus the break & the
overtime, he only has 39.

what are the regular working hours for each day? Is working on weekend
completely overtime?


Regards
Claus Busch