I've found how to present time exceeding a minute in seconds ([m]:ss.00), but I need to formulate items per second. Doing simply CellItem / CellTime won't work. I've tried Minute(CellTime)*60+Second(CellTime), but this leaves out the hundreds of a second.
View 3 RepliesI'm trying to work out the productivity of something per hour but I'm having a bit of trouble with dividing time in Excel.
Basically I've got
A1 = # of Sales (e.g. 5)
A2 = Total time worked (e.g 07:36:00)
I want C1 to show me how many Sales are made per hour.
Need to divide a cell containing a time value (hrs:mins:secs). My timesheets calculates the number of hours on a job and I want to divide this total by the number of components made.
View 7 Replies View RelatedI have a total at the bottom of my spreadsheet and I want to distribute that total equaly to the missing columns for each month and each product highlighted in green in the spreadsheet. What formula do I need to use to be able to do that?
At the end I want the numbers in each month to total the total at the bottom.
I am attempting to calculate % utilization. I have number of minutes used formatted in [mm]:ss and number of minutes available formatted as a number. When I divide available by minutes used I get a percentage that is off.
So I have Available 33600 minutes available 4901:20 minutes used.
A given string of some letters (from three to five, six) is followed by a single number (1-4). Then it ends, or is followed by another such combination, up to a maximum of, let's say, four.
Example:
xxxx1
yyy3yyyy1
xyzxx1yxz4xzx1
xxx2xxx2xxx2xxx2
What I would like to do is to divide a given chain of characters into the smallest chains which contain only letters and one number. To the examples given above, I'd like to receive the following sets:
xxxx1
yyy3 and yyyy1
xyzxx1 and yxz4 and xzx1
... and so on
Is it somehow possible to do it with simple functions? Or is VB necessary (which sadly I don't know)?
Does anybody know the code or the formula to divide a number generated by a formula. Id like to divide this number by 2 different set numbers. Meaning if my number comes out ot 19, id like to divide this by 5.5 and if it's not a whole number which this isn't (it's 3.45), i'd like to round this number to a whole number and add 2.5. My end answer would be 3 5.5 and 1 2.5.
Windows XP
Excel 2003
I have Excel 97. I'm working on a spreadsheet and I just can't figure out how to create a conditional formula that will look through Column A & B, then add up the related time value (hh:mm) in Column C.
A B C D
Gender Age Start time End Time
Male Adult 23:10 01:45
Female Adult 21:30 03:30
Male Adult 00:15 01:05
Female Juvenile 23:50 00:10
I tried the SUM(IF((A:A="Male")*(B:B="Adult),C:C)) or something like that.
The times seem to have a problem adding up or averaging if the time value exceeds 24:00 hrs. Or if it's computing numbers that some are before and after 24:00 hrs.
Is there some way to create a formula that will result in something like: Of 5 Adult males, the average start time was 23:50 hrs and the average end time was 01:30?
I am developing a report that tracks the efficiency of industrial sorters on
an automated assembly line. The line runs around the clock in three shifts
(1: 7:30am to 3:29pm, 2: 3:30pm to 11:29pm, and 3: 11:30pm to 7:29am), and
collects certain performance indicators, such as units processed. I am
trying to calculate the units processed during each shift on each day. For
example, if a run ran from 2:30 pm to 4:30 pm, and processed 100 units, I
would want 50 units credited to shift 2 and 50 units to shift 3 (we are
assuming a steady rate of processing).
My data arrives from the machine with the following columns (each run is a
separate row):
B|D|E|G|H
Run #| Start Date| Start Time| Units Processed | End date | End Time
I have successfully used the start time to determine the starting shift with
IF statements:
=IF(--E3<0.3125,3,(IF(--E3<0.6458,1,(IF(--E3<0.9792,2,3))))) and a similar
formula to calculate the end shift. This works fine if the run only spans
two shifts within the same day.
My problem is that some of the runs, run across more than one shift and even
across days. For example, a run that ran from 10:30 pm on one day to 4:30 pm
on the next, would overlap 5 shifts and two days. I need to be able to
figure out the proportion of time spent on each shift, and use that to get
the proportion of units processed during each shift on each day.
I am currently thinking of a convoluted series of nested IF statements (and
generating lots of columns to the right of my data for each potential shift
within a run), but this seems inelegant and cumbersome. Is there a more
logical way to approach this? Even more specifically, is there a function
that can pair my start/end dates and times with a set of shift start/end
times and calculate the elapsed time for each shift within a run? Something
with MATCH or VLOOKUP maybe?
I have a txt file imported into excel that's slightly strange.
But once into excel, I need to edit some numbers.
The values in cell B 23 is 1.
So is the value in B 28, so is the value in B 33, B38, B 43 and so forth ad infinitum... Or so it feels.
I need to edit all those values to 0.
I am using excel 2007 or 2010.
I need to divide the numbers on sheet 1 column D. This column contains numbers and blank cells so I need to divide by the actual number of cells that contain only numbers. I will be referencing the divided numbers on sheet 2 in a monthly summary format.
I have dollar amounts that I need to distribute amongst varying numbers of columns and not have have the total distributed be over or under the original amount by any number of cents. When I simply divide the dollar amount by the number of columns, the total of those columns can sometimes be more or less than the original dollar amount by a few cents.
e.g.
Dollar # of A B C All Columns
Amount Columns Total
--------------------------------------------------------------------
$25.05 2 $12.53 $12.53 $25.06
$11.47 3 $ 3.82 $ 3.82 $ 3.82 $11.46
$25.05 divided into 2 columns gives $12.53 in Column A and $12.53 in Column B. Total of Columns A and B is $25.06. Over by a penny.
$11.47 divided into 3 columns gives $3.82 in Columns A through C. Total of Columns A through C is $41.46. Under by a penny.
I know I could simply always add or take away the pennies from one column, but I would prefer the process to be random or formulated in such a way that the Column to which the extra pennies are added to or taken away from differs in order to be "fair to each column".
I'm busy working on a schedule for calculating irregular hours.
A thing I need to do is the following:
name: Ed
From: 6:30
Till: 15:00
+15% :
Ed is one of the employees working on a busline. If he works between 6:00 and 7:30 he gets +15% on his salary for his worked hours. So what I need to know is how many hours(or minutes) he's worked in that 15% box. (In this example 1 hour)
A client buys 500 minutes of my time. In one week I spend 340 minutes on the account. I'd like a column to show Time purchased (say 500 Minutes) Time spent and a final section showing time left (but showing negative values in red)
I hope I've explained this ok but here is an illustration of what I'm trying to achieve in Excel 2011.
[URL] ...........
How to do the following?
When dividing two cells and the answer is zero then I want to return a different value. I've tried the following with no luck:
=IF(ISERR($D20),"*",$G20/$G$22*100)
or
=IF($H19,$G19/$G$22*100,"-")
=if a1/b1 = 0 then "*", else return actual value
(N)(%)
64 13
82 16
82 16
2 0 --> change this to * or -
264 52
13 3
507(total)
Dividing column in equal parts divide a column in groups with the same rows and the same total sum? For example, I have the column A as following:
8,942
2,807
2,568
5,818
5,818
596
8,942
4,390
1,607
890
8,772
1,103
The total number of rows is 12 and total amount is 52,254
I want to divide the column in three equal parts with the same total. So each group must contain 4 rows with the total sum 17,418
Have a list of information and would like to divide that list by a number that the user enters. Then from the total count of nonblank rows divide by the numeber entered by the user didive the list into equal parts and print out each group with a page break per groups.
Have attached an example.
V = 100 x [E /(IY + YP)] x Sqr. [((R + G)/2 / (IY + YP + F)]
V = 100 x [6.10 /(5.60)] x Sqr. [((10.2+2)/2 / (5.60 + 2.1]
V = 108.93 x Sqr (.79)
V = $96.89 / share as of 7/18/2014
E = Earnings in $ / share 6.1 (b9)
IY = Interest rate in % 5.6 (b10)
R = Profitability Factor in % (internal growth rate)10.2 (b11)
G = Earnings Growth in % / yr. 2 (b12)
F = Inflation Rate in % / yr. 2.1 (b13)
Here's what I have, result should be $96.89, I need to tell excel to take square root of everything to the right not just (B11+ B12)
=100*(+B9/B10)*SQRT(B11+B12)/2/(B10+B13)
I'm not sure if it's just been a long week but I can't get get the following to work in a sum formula.
In the example below I want to deduct B1 from A1 and then Divide by C1. The result should be 0.57 but I can't get to it within 1 formula.
9:49:12 - 6:18:46 = 3:30:26 / 2 = 0.57!!! Not sure if it's because I'm using unfriendly hh:mm:ss format but I need to.
A1 - 9:49:12
B1 - 6:18:46
C1 - 2
Cell A1 contains 2, B1=220077:23
In C1 I created a function = (2*1000)/B1, I get error. I want the answer 2000/220077h23m = 0.009087713
how could divide one single cell in diagonal direction ?? without using line from drawing tool.
View 12 Replies View RelatedI am trying to divide a range that has blanks in. The range is the result of a webquery and taking the blanks out would be time consuming at best.
Conveniently (potentially) the blank row is every other row in the range.
Suppose I have the following plan number (24) and I wan't to spread it across 12 columns, as evenly as possible but with respect to rounding.
Easy enough.. Each column receives 2...
Or say the figure was 24.6 and i wanted to round to 2 places...
Easy enough... Each column receives 2.05...
Or say the figure was 24.7 and i wanted to round to 2 places...
Easy enough... Each column receives 2.05... But one would be 2.06!
How about the number is 1 and say, the rounding is 0.1?
Ten of the columns would be .1 while two others (arbitrarily) would be 0.
In trying to solve my dilema I searched some older threads and found some code written by Peter SSs. I altered it to fit my situation ( or so I thought) because all I get is a compile error. I am not sure why. The data is lengnty but the sorting item is the provider name. The information for each provider should then post to the sheet with his name.
Here is the
Sub test()
Dim lr As Long
Dim ws As Worksheet
Application.EnableEvents = False
Application.ScreenUpdating = False
lr = Sheets("Audit Results").Range("F" & Rows.Count).End(xlUp).Row
For Each ws In ThisWorkbook.Worksheets
Have a list of information and would like to divide that list by a number that the user enters. Then from the total count of nonblank rows divide by the numeber entered by the user didive the list into equal parts and print out each group with a page break per groups. Have attached an example.
View 4 Replies View RelatedI have a large worksheet that I need to make into a quarter of what it is now. Each of the numbers is just inputted, so I was thinking there is a way to divide it all by four.
View 6 Replies View RelatedI have this formula
= COUNTIF(AT6:AY6,"F")+COUNTIF(AT6:AY6,"P")+COUNTIF(AT6:AY6,"M")+COUNTIF(AT6:AY6,"D")
that returns the number 2 (which is correct). However, if I precede it with
AZ6/COUNTIF(AT6:AY6,"F")+COUNTIF(AT6:AY6,"P")+COUNTIF(AT6:AY6,"M")+COUNTIF(AT6:AY6,"D")
it returns a DIV/0 error, even though AZ6 has a value of 24.
Surely 24/2 would return a value of 12? NB AZ6 cell value is derived from the result of a formula.
I have this formula:
=IF( COUNTIF($H$42:$H$76,"A~*")/COUNT($J$42:$J$76)="","",COUNTIF($H$42:$H$76,"A~*")/COUNT($J$42:$J$76))
which seems to work fine if there's data in the range to compute. If there isn't it throws a DIV/0 error at me. How can I modify the above to
Be blank if there's no data to display in the range, therefore avoiding a div/0 error.
I am working an Excell sheet. It have many cells with formula like sum of a range of cells etc.
I wanted to divide a range of cells with the given cell (which is inturn having a formula for sum of a range of cells.) want i wanted is to divide a range of cell values with a given cell whose value (number) is obtained through a formula.
when i do
=100*(v65)/v20 for T65 cell and copy the formula for T66-T106 cell range
it is calculating for T66=100*(v66)/v21 and T67=100*(v67)/v22 ........T106=100*(v106)/v61
what I want is all the cells T66=100*(v66)/v20 ... T106=100*(v106)/v20.
I'm trying to craft a gradesheet for my son's teacher and I am runnning into a problem obtaining a students average. In one row I've listed the possible questions for all the tests whether the test has been taken or not. In the row below I've listed "questions answered correctly" for only the tests they've taken. I need a way to calculate the overall avergae of the student while ingoring columns with null values in the "questions answered correctly" row.
I know there is a way to ignore zeroes by excluding them with an IF statement but I didn't want to do that in case one of the students actually did get a zero. As the attached example shows the simple sum function renders a 25% average while the average should actually be 50%.