Please look at the attachment. Firstly, i'd like cell G5 to show the amount of time worked i.e diff between E6 & F6. Secondly I'd like help with the formula for cells AF6 & AG6 which would require AB6-AC6 divided by the frames, every time I try it I get a messed up answer. Thanks for reading.
Last edited by omletto; 11-12-2009 at 07:28 AM.
Formula in G5 is =MOD(F5-E5,1)
And about second question I'm not quite sure what do you look for
Never use Merged Cells in Excel
Thank you very much for first answer, that worked perfectly. Sorry, I didn't explain very well. I need to work out the peices per hour. That would mean the total peices (3811) divided by total hours worked less the total justified downtime. hope thats clearer.
Try this:
In AF column you can write =D5/G5.
But you'll get error due to dividing by 0
Therefore you can use this:
=IF(ISERR(D5/G5),"",D5/G5)
Wich will give you blank cell instead of error.
Same for AG column you can write:
=IF(AF5="","",AF5/3600)
That comes up as a blank, in AF6 it should read 27hrs minus 1 hr 57 minutes. then i need it to show how many peices were made per hour. so that should equal 3811 divided 25hrs and 3 minutes.
I see where I made mistake...
Try look here is this OK for you now:
SAWS.xls
However, I had to change those ugly merged cells
(only monday adjusted)
Also, I get different values.
Pieces per hour can't be hour. It's a number (I got 3387)
Same is for Pieces per second (i just forgot to remove formatting, but format it as number)
Last edited by zbor; 11-12-2009 at 05:44 AM.
Firstly I agree with the merged cells, that wasn't meBut the frames per person was correct, D6+J6+P6+V6= 3811 Now what I need is for the 3811 to be divided by the total hrs worked less the total justified down time 25hrs 3mins which should come to approx 152.
Look here...
However, I still didn't get 152 but I hope we are close...
SAWS.xls
Take a look on SUMIF function wich is basically I use
Thanks for the help, but the peices per hr still reads 3651, if 3811 were made in 25 hrs then 3651 cannot be the output per hr.
How about this:
=IF(AB5=0,"",AE5/24*(AB5-AC5))
That comes up as a blank
Yes. And extend down and you'll get numbers in AF6 and AF7.
If you don't want blank you'll get DIV/0 error but you can't get numbers because you dividing with 0.
Last edited by zbor; 11-12-2009 at 07:10 AM.
i need a formula that will divide 3811units divided by 25:03 and give the answer in units.
And formula do that.
Just enter in AF6 : =IF(AB6=0,"",AE6/24*(AB6-AC6))
Hi mate, your formula works with a couple more brackets - =IF(AB6=0,"",AF6/(24*(AB6-AC6))). :-)
Thank you very much for your help, much appreciated.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks