Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Pls Help - blank cell/formula issue
I am creating a Wages, Hour and Payroll summary for my fiancé because he's trying to keep track how the new company is handling wages/time schedules, etc.
However, I'm struggling with a few calculations that I just cannot find 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 his 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 the G column a negative value :/ G18 just sums the figures in the G column and gives him a weekly total of overtime that can be applied to personal and/or holidays. The problem is, if cell F16 and/or F17 are blank they are still 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 much better, but I am not an expert nor every day user of Excel. I can normally make calculations do what I want them to do just by googling information but in this case.. I cant, so if someone could please help me I would greatly appreciate it. Thank you Sho |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pls Help - blank cell/formula issue
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 -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
|
|||
|
|||
Thank you Claus for your reply, I will definate try those after a few hours of sleep. However, most of the night I have recreated the formulas and the following is now what I am working with. It would probably give much better details, etc.
Should have provided a bit more information before I tried to explain the formula calculations problems I am having. I thought I could attach the actual spreadsheet but I can't - I will try to explain each column and what I am "trying" to accomplish, maybe someone would be kind enough to assist me with my horrible mistakes. (Attached a PDF of the actually sheet). As you can see I am creating a salary/payroll w/ hours, breaks and over hour calculation (only). Reason being is that overtime is not paid via time and half, but those accumolated hours become avialable to use for 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 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 However occassional, he works Saturday for a few hours and doesn't get a 30 min (unpaid) break - How can I calculate unpaid breaks to specific days only? Example: Sum((C10:C16)-(#ofcells*0.5)) I'm sure there is a formula out there that can do this unfortunately, I have not been able to fine one (they all do things I don't want them to do). My current formula does work, but I think it may be what is causing the error I receive when I reopen the document (see final error at the end of thread). ____________________________ D10 thru D16: calculates if the hours are more then reg scheduled hrs it shows the overtime hours. D10:D16's formulas a Mon - Thurs is: =IF(C108.5,(C10-8.5),0) Fridays is: =IF(C148.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 manual changed the hours), it shows an error. Error: The formula in this cell differs from the formulas in this area 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(D10:D16) However, its not calculating and states: The formula in this cell differs from the formulas in this area of the spreadsheet. What am I doing wrong? Everything else on the spreadsheet regarding his payroll is functioning 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 calculate correctly. Circular references are any references within a formula that depend upon the results of that same formula. For example, a cell that refers to its own value or a cell that refers to another cell which depends on the original cell's value both contain circular references. For more information about understanding, finding, and removing circular references, click ok. If you wnat to create a circular reference, click cancel to continue. I have spent quite a while trying to figure out which formula is creating this error with no success. I really appreciate the time anyone takes to read my issues and assists with the problems. Thank you Sho Quote:
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pls Help - blank cell/formula issue
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 -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#5
|
|||
|
|||
Hi
Probably simpler and save on all that typing if you uploaded a sample spreadsheet (Dummy data) Quote:
|
#6
|
|||
|
|||
Ive tried it wont allow me to upload it, not sure why.. I saved the speadsheet as pdf and its only 1 page but states it too large - Every time I attempt its stating it is too large.. pdf file, even a zipped file.. I'll try to upload it to the server and add a link in a whiles
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pls Help - blank cell/formula issue
"Shokra" wrote:
Kevin@Radstock;1607266 Wrote: Probably simpler and save on all that typing if you uploaded a sample spreadsheet (Dummy data) 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 MediaFi http://www.mediafire.com FileFactory: http://www.filefactory.com FileSavr: http://www.filesavr.com RapidSha http://www.rapidshare.com |
#8
|
|||
|
|||
Claus - your formula is absolutely brilliant! I applied =SUM(C10:C16)-COUNTIF(C10:C16,""&6)*0.5 to cells C17 to K17.. I did attach the excel sheet zipped but I can't remember now if it was before I made those changes or not.
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. So when it calculates his gross salary, its adding in those 4 extra 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 the above, is it correctly done? I have two questions: 1 - Overtime column(Friday).. formula is: =IF(C148.16,(C14-8.16),"") next weeks Friday is: =IF(E147.5,(E14-7.5),"") These days alternate each week, but always the same. I get the same error on both: Error: The formula in this cell differs from the formulas in this area of the spreadsheet. Is this actually an error of its it just a notification? 2 - When I sum the overtime columns to total in D17, etc, I get the Error: The formula in this cell differs from the formulas in this area of the spreadsheet. Is this actually an error of its it just a notification? well I'm going to try the zipped file again hope it works this time. :) Last edited by Shokra : November 11th 12 at 04:38 AM Reason: Question to Claus' formula |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pls Help - blank cell/formula issue
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 -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting blank cell issue | Excel Programming | |||
How to return a blank formula cell if the reference is blank? | Excel Worksheet Functions | |||
Average Formula to display blank cell if named range is blank | Excel Worksheet Functions | |||
Start Cell B1 then find first blank cell, insert subtotal, next non blank, then next blank, sutotal cells in between......... | Excel Programming | |||
Formula that will leave cell blank if cell is blank | Excel Programming |