Divide Without Using Temp Column
In A1 I have the value 60 I want it in 1:00:00 format
What I do is, I divide that by A1/24*60 and the copy that and pastespecialvalue on A1
Is there any way to convert the number 60 into 1:00:00 format without having to do calculation in temporary columns?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
 If Column A = 0 Then Adjacent Cell In Column B Divide By .3
 Add Two Columns Together And Then Divide The Sum By Another Column
 Nested Loop To Divide Adjacent Row By Column
 Changing Temp Dir
 Temp File Not Saving Macros
 Delete Temp Files Located On FileSever Via VBA
 Activechart.export Not Renaming Temp File
 Can I Divide A Combobox Value By 2
 Divide By Zero Error
 Divide A Given Value Into Several Cells
 Divide By Zero Error. #DIV/0
 Divide Selected Cell By 100
 Function 2 Divide A Number (ie $1.00) By % (ie 25%) To = ($4.00)?
 How To Divide An Array With A Number In VBA
 Divide Only With Number Of Cells With Value>0
 Divide Population Into Deciles
 Divide Text Across Cells
 Formula To Avoid Divide By Zero
 Divide 2 Cells From Different Worksheets
 Divide Two Columns But Show My Zero
 Replace 'Divide By Zero Error' With Just A Zero
 Temp Macro To Rename Macro
 Vlookup: Divide It By 1000 Then Multipy By 24
 Math Functions (divide And Mulitply)
 Group Items And Divide By Coloured Row
 How To Redivide The Data At The Same Time Interval
 Add Two Numbers And Divide The Result By Another Number
 Divide Uneven Data Into Columns
 Divide A Sum By Number If Items In List
 Divide A Budget Of 100k For Each Of My Employees
 Divide By X Automatically In Same Cell Upon Entering A Value
 Divide Cells With Numbers & Signs
 Function/Formula To Divide 2 Different Ranges & Add Them
 Formula That I Can Add, Subtract, Divide, Average, Etc, Etc These Numbers
 Divide An Number Of Jobs Without Ending Up With Decimals
 Divide 2 Cells & Express Result As Percentage
 Count Number Of Rows That Have Numbers And Divide Sum Of Cells
 Search Folder For Files Containing "Temp", Delete Files
 Conditional Divide (probs Occur When I Manually Enters The Exchange Rate)
 Macro For A Formula To Divide One Cell By Another And Past It In A Third Cell.
 Divide Number Cell By A Set Or Fixed Number
 Number Formatting: Enter A Number It Seems To Divide It By 100
 Lookup/Match: Compare A1 For The Values In Column B, Then Return The Corresponding Cell (column C) In Column D
 If Column Contains Data Then Insert A Blank Column And Shift Specific Column To The Right
 Find Rows W/same Value In Column A And B And Offset Column C To Next Available Column
 Subtracting Column B From A, And Leave Third Column Blank If No Value In Column B?
 Check One Column, Get Value From A Second Column And Display Sum In 3rd Column
 Multiply Every Nth Cell By Another Column Whith Merged Cells, THEN Find Column Total
 Pull Column Data (Sheet3) From Master.xls And Paste To Column 4, Sheet4 Of WorkingSS.xls
 Merge Two Workbooks. Copy Column Data Based On Numerical ID Match Of Another Column.
If Column A = 0 Then Adjacent Cell In Column B Divide By .3
Im wondering if this can be done in excel.. 2 columns if the value in column O=$0.00 then I need to divide the adjacent cell in column L by 0.3 or multiply by 30% this is the way I was trying to do this... =IF((O2:O6722)=0,DIVIDE(L2:L6722).3) I have tried it several different ways and cannot get it to work  is it my formula or can this even be done because I can't reference just one cell?
View Replies!
View Related
Nested Loop To Divide Adjacent Row By Column
I am trying to loop through Column Q2:Q2000, and divide the adjacent Row R2:HS2 by the value in the corresponding Q column. ie. cells R2: HS2 should all be divided by Q2 cells R3:HS3 should all be divided by Q3 etc until R2000:HS2000 are divided by Q2000 I presume that i would require an initial loop to scroll through the column and a seperate loop to scroll through the row values, but i cannot get my head around how this is done.
View Replies!
View Related
Changing Temp Dir
When opening 1 of my files (made to be used by others) a lot of .tmp and .emf files are made. When I close the file most, but not all, are deleted and new ones are added. Simply deleting all .tmp/.emf files doesn't work because some are still in use (by other files). There for I have made a bit of code that checks if direcory "C:...TempTemp_PandL" exists. If not, then it will be created:
View Replies!
View Related
Temp File Not Saving Macros
The form is completed by the project leader, then they hit a Command Button and it's sent to the Senior Manager. After the Senior Manager looks at it and approves it, he then sends it on to Admin (me!) so the project can get set up on all of our systems. We recently upgraded to Office 2007 (yes, we are a bit late!) and now the Macro that sends the form to Admin no longer works. An example of the error is as follows: Cannot run the Macro "FY0610 PROJECTS 19Jan10 0832'EmailtoAdmin.EmailtoAdmin'. The macro may not be available in this workbook or all Macros may be disabled. Here is the code that I use to send it to the Senior Manager: Sub MailtoSM() Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim TempFilePath As String Dim TempFileName As String With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook ActiveSheet.Copy Set Destwb = ActiveWorkbook With Destwb If Val(Application.Version) < 12 Then FileExtStr = ".xls": FileFormatNum = 4143 Else.........
View Replies!
View Related
Delete Temp Files Located On FileSever Via VBA
Right now the security settings on my server are set so that new files can be added by anyone but only a few people can delete them. Everytime Excel opens a file on the server it creates a temp file that it cannot delete due to the security settings. This means that I have to go in to each subdirectory and manually select then delete each of these temp files. way to identify an excel tempfile, or a method for deleting these files with VBA?
View Replies!
View Related
Activechart.export Not Renaming Temp File
I am trying to export a chart using the code below, the loops change the data that is used to produce the chart: Dim strFilename As String Dim strExt As String Dim lngProfile As Long Dim lngOption As Long For lngOption = 1 To 3 Range("nmOption") = Choose(lngOption, "Existing", "Option 4", "Option 5") For lngProfile = 1 To 4 Range("nmProfile") = "Profile " & lngProfile ActiveSheet.ChartObjects("Chart 13").Activate ActiveChart.ChartArea.Select strExt = "gif" strFilename = ThisWorkbook.Path & "Images" & _ Range("nmOption") & "_" & Range("nmProfile") & "." & strExt ActiveChart.Export strFilename, strExt Next lngProfile Next lngOption Each export command creates a file with the correct name but no data (0kb in size) and a temporary file that is the correct file type (I have tried both jpg and gif). I could try and copy the temporary file, but why does Excel not finish the job? I have tried to put a loop in and also added DoEvents after the export but no joy.
View Replies!
View Related
Can I Divide A Combobox Value By 2
If CheckBox1.Value = True Then Range("e25") = ComboBox6.Value ElseIf CheckBox2.Value = True Then Range("e25") = ComboBox6.Value / 2 End If Im getting an error on the second to last row, Ive never tried to divide using VBA before and I have no idea how to! Also the amount im trying to divide will always be in the format hh:mm
View Replies!
View Related
Divide By Zero Error
I am creating a 'weekly average spreadsheet' (Excel 2007) for a teacher. It has a column for each student's name and his test grade for each day (MF). The last column gives his weekly average. She also needs an average for all the students' for the week (vertically). My formula is correct, i.e., =AVERAGE(H2:H11). However, I'm getting a divide by zero error because I have some blank rows above my formula (so she can add new students' names as they come. How can I get rid of the divide by zero error so she can see the running averages as she inputs grades (but still leaving empty rows for new names)?
View Replies!
View Related
Divide A Given Value Into Several Cells
If I have a value of lets say 53, I need it broken down into equal and whole numbers across the spread sheet with the last one being the remaining value. Currently for example in cell F5 I have a value of 41 that I need to break down so I have done the following: In Cell P5 =IF(F5>=25,F521,F5) Then in Cell Z5 =IF(P5>=25,P521,P5) and this goes on until I have zero value from the original value in the last cell can i not do this all in one formula and have the results populate in other cells.
View Replies!
View Related
Divide By Zero Error. #DIV/0
I have a database that requires me to find the average of some data over several columns, however if one of the values I have to divide by is a zero I get a 'Can't Divide By Zero Error' is there any way to override this ? So that if a Divide by Zero Occurs, instead of returning a 'Can't Divide By Zero Error' it just replaces it with a Zero instead ?
View Replies!
View Related
Divide Selected Cell By 100
I'm lost on VBA, but I have been successful at recording a few extremely simple macros. I have a number of pdf statements that I have run through OCR, and the OCR often misses the decimal point so that 43.85 comes out as 4385. (this is in dollars, so everything has 2 decimals). These cells are usually pretty easy to spot, so I want to select that cell, press a keystroke or 2, and divide the value in that cell by 100. I assumed that by selecting "relative cell references", and recording a macro with the keystrokes "<F2>/100<HOME>=" (without the quotes, of course), I would accomplish that. But instead, it places a constant in that cell, with the value obtained in the cell where I recorded the macro.
View Replies!
View Related
Divide Only With Number Of Cells With Value>0
I really struggle with the following scenario: A1=5 B1=4 C1=0 D1=0 E1=5 What I want to do is to divide the sum of those cells with the number of cells with a value > 0 (but all cells must be controlled to check if the value > 0). In real life it's easy to see that I want to do 14/3 (A1>0, B1>0 and E1>0 equals three cells), but how to write this formula in Excel?
View Replies!
View Related
Divide Population Into Deciles
Excel has a QUARTILE function to divide a population into quartiles. I'm trying to do something similar but divide a population into deciles (i.e., every 10%). Is there any easy way to divide a large population into deciles and to assign a decile to each value in the population (i.e., top 10%, 1120%, 2130%, . . . bottom 10%)?
View Replies!
View Related
Divide Text Across Cells
Fallowed EXCEL instruction, for >DIVIDE TEXT ACROSS CELS< but result is not what it should be. 1, I select . >FIXED WIDTH< Than I create ONE !! >BREAK LINE< 2, click NEXT / FINISH. 3, MY text is split but not to two column but to MANY COLUMNS? Repeat many times, always careful that ONLY ONE break line selected. Also when I first select. >FIXED WIDTH< and click next. My text is already market with many >BREAK LINES< so I have to first delete all, seems to me strange.
View Replies!
View Related
Formula To Avoid Divide By Zero
in column E i need to add a formula: =($C2  $B2)/$C2 And i would be extending it to the rows below. Now, obviously, if C2 contains a 0, it gives a divide by 0 error. How to modify the formula which makes it valid only if C2!=0(not equal to 0)
View Replies!
View Related
Divide 2 Cells From Different Worksheets
i want to divide two cells from different sheets using a macro i have this at the moment ='Cost Sheet A'!$L138/'Cost Sheet A'!$AD155 and have gathered that i can divide 2 cells that are in the same sheet using macros but i want to link to two different sheets using a macro
View Replies!
View Related
Add Two Numbers And Divide The Result By Another Number
I have a simple formula that adds two numbers and divide the result by another number K1: SUM(E1,F1) / H1 K2: SUM(E2,F2) / H2 K3: SUM(E3,F3) / H3 K4: SUM(E4,F4) / H4 K5: SUM(E5,F5) / H5 etc, etc and then I average the results AVERAGE(K1:K5) etc, etc When H1, H2, H3, etc is 0, it generates a #DIV/0! (since you cannot divide by 0) I want a formula that I everytime a H1, H2, H3, etc is 0, I want to set it to a specific number H1, H2, H3, etc to 15.00 For example: IF (H1 = 0, 15.00, sum(E1,F1) / H1) If the number provided (H1) is 0, set it to 15.00, otherwise add the two numbers (E1, F1), and divide this result by the number provided (H1) But this is where the problem is: In every single cell that I have NOT yet provided information, it treats the BLANK cells as if it was 0, and has set every single cell to 15.00.
View Replies!
View Related
Divide Uneven Data Into Columns
I would normally sort out using Text to Columns, but I can't in this case as the remaining elements can vary. I have a list of names with Forename and Surname and then the elements after that vary. Names are written in one cell under a single column like so: John Smith 13/04/2000 James Smith 123 123 1233 Jane Smith 07122 12345 Another Smith AB99 9CD All I want to do is end up with 3 columns of data, Forname  Surname  Other Is it possible to use a formula to make the breaks into each column at the first 2 spaces only, therefore the contents after the second space would just end up in one cell?
View Replies!
View Related
Divide A Sum By Number If Items In List
I basically have lots of lists of 12 cells. how ever some of those lists contain errors (e.g instead of having 12 numbers it will only have 10) so instead of doing =sum(a1:a12)/12 i want to divide by the amount of numbers in that area in this case 10 so i can get an average.
View Replies!
View Related
Divide A Budget Of 100k For Each Of My Employees
how I would be able to divide a budget of 100k for each of my employees. If I have employees making anywhere from 20,446.40  69,659.20 How can I give each employee a portion on these funds? For example if: Lisa makes 20,446.40 Tom  24,448.20 Liz  34,854.60 Mike  44,564.80 Jan  62,061.48 Dan  54,324.20 What would be the precentage that I could give each employee?
View Replies!
View Related
Formula That I Can Add, Subtract, Divide, Average, Etc, Etc These Numbers
Imagine I have cells A1 to A5 with a sequence of "numbers" like this: A1 = 5055 A2 = 4567 A3 = 5690 A4 = 4980 A5 = 5155 The left hand side numbers represent a score a minus sign () to separate and the right hand side represent another score. I want a formula that I can add, subtract, divide, average, etc, etc these numbers. If I want to add the left hand scores, for example, cells A1 to A3, sum would be 151 (50 + 45 + 56).
View Replies!
View Related
Divide An Number Of Jobs Without Ending Up With Decimals
I'm trying to create a formula which will allow me to do the following 1. Enter the number of jobs received 2. Divide it by the staff available without ending up with decimals. As an example. Say I have 60 jobs to be allocated and there are 13 peeps to distribute them to I'm currently getting 4.62. What I need to do is make sure that the jobs are only issued as whole numbers. E.g 5 peeps gets 4 and 8 get 5. I also need to factor in jobs on hand etc but I have already cracked this.
View Replies!
View Related
Count Number Of Rows That Have Numbers And Divide Sum Of Cells
I have a 52 (one for each week) page workbook. I am trying to average cell J9 for the entire 52 pages. However the information is not added to the cell until the end of the week so week 3052 all have 0s and should not be counted in the dividing number. Is there a way to have Excel count the number of cells that have a number (not 0) and divide the sum of the cells but that number. =(WK1!J9+.....WK52!J9) / (counted number of cells not = 0)
View Replies!
View Related
Search Folder For Files Containing "Temp", Delete Files
Is there a code that will search a predetermined folder for all .xls files containing the word "Temp", and deleting those files? Example: In the root of drive M, I have several Excel files. Anytime one of the original tracking logs is opened, a temp file of that log is automatically created. I would like to automatically search the drive and delete all files containin the word "Temp". I've attached a picture of the directory tree, in case that will help. The file will always be named "PO Response Tracking  Temp#######.xls", with ###### representing a series of either 5 or 6 numbers.
View Replies!
View Related
Conditional Divide (probs Occur When I Manually Enters The Exchange Rate)
The file is a simple sheet which upon entering the actual/Invoice cost (C5), calculates the estimated landing/final cost (C8). In between the process involves changing the currency from US$ to PKR, make some calculations, and changing back the currency again to US$. The default rate of currency exchange is set to ave 60 (C12). However if the user knows the current rate he can put it manually in C6 and sheet will make all the calcs on this instead of using the default rate. Problem: Everything is working just too perfect. But probs occur when i manually enters the exchange rate. It does successfully changes the US$ to PKR and calculates everything perfectly but doesnt reverts the final cost back to US$ successfully instead it keeps using the default value instead of user's value
View Replies!
View Related
Divide Number Cell By A Set Or Fixed Number
I am wondering if it is possible to create a formula in a cell that will divide whatever number is entered into that cell by a set or fixed number. ie. I enter the number 9 in the cell and when I press enter, the 9 is instantly divided by 15 and the answer is displayed in the cell where I just typed the 9.
View Replies!
View Related
Find Rows W/same Value In Column A And B And Offset Column C To Next Available Column
I need to combine rows that have the same value in column a and column b to the same row by offsetting column c to the next available column. For example, I would like the first 6 rows of the provided sample to appear like this. 0014B22<@44>Soil Properties and Qualities<@44>Soil Properties and Qualities<@44>Soil Properties and Qualities 0014B23<@28>Coursey<@28>Ogles<@28>Shelocta Sometimes the values are the same in column c, sometimes they are different. I do not want to delete duplicate rows where they are the same. Sometimes there are 2 rows that have the same values in column a and column b, other times there may be 3 or even 4 rows with the same values in column a and column b. Regardless, I would like the values in column c combined on the same row in the next available column. It would be nice if the duplicate column a and column b rows (with a null column c cell) were then removed, but I could do that in the next step. 0014B22<@44>Soil Properties and Qualities 0014B22<@44>Soil Properties and Qualities 0014B22<@44>Soil Properties and Qualities 0014B23<@28>Coursey 0014B23<@28>Ogles 0014B23<@28>Shelocta 0014B24<@33><i>Available water capacity:<p> High (about 11.5 inches) 0014B24<@33><i>Available water capacity:<p> Very low (about 2.9 inches) 0014B24<@33><i>Available water capacity:<p> High (about 9.0 inches) 0014B25<@33><i>Slowest saturated hydraulic conductivity:<p> Moderately high (about 0.57 in/hr) 0014B25<@33><i>Slowest saturated hydraulic conductivity:<p> High (about 1.98 in/hr) 0014B25<@33><i>Slowest saturated hydraulic conductivity:<p> Moderately high (about 0.57 in/hr) 0014B26<@33><i>Depth class:<p> Very deep (more than 60 inches) 0014B26<@33><i>Depth class:<p> Very deep (more than 60 inches) 0014B26<@33><i>Depth class:<p> Very deep (more than 60 inches) 0014B27<@33><i>Depth to rootrestrictive feature:<p> More than 60 inches 0014B27<@33><i>Depth to rootrestrictive feature:<p> More than 60 inches..............
View Replies!
View Related
Check One Column, Get Value From A Second Column And Display Sum In 3rd Column
I am tying to total all the sales for a given customer. This is what I have at present Customer Sales CustA 1000 CustB 500 CustA 1250 CustC 750 Elsewhere in the spreadsheet I have the following Customer Total Sales CustA CustB CustC As you can probably see I need to get the sum of each customers sales and display this under Total Sales. I thought about using VBA but am not really sure where to start. If anyone has any ideas how I could progress this I would be most grateful. I am using excel 2007
View Replies!
View Related
Multiply Every Nth Cell By Another Column Whith Merged Cells, THEN Find Column Total
Please see the attached sheet. I have columns B through a lot (B through O in my oversimplified example). In every 7th row in each of these columns there is either a 1 or a blank/zero. I need to multiply that 7th number by the Quantity in column A, to achieve a total (ie the sum of each result of 7th cell*quantity) for each column in the bottom row, labeled "Totals". In the actual version of my sheet, there are far too many rows to select everything manually. I've been fiddling with combinations of COUNTIF/COUNTA and OFFSET, but I haven't come up with a way to check for the 1 in every 7th row, THEN multiply that 1 by the quantity in column A, THEN add up the results for each column. As you can see, there are 1's elsewhere in the columns that are irrelevant to this particular calculation, so something like LOOKUP would also have to look in every 7th cell and couldn't just look at the column as a whole. If you can't provide an immediate solution, but can at least point me to a resource that would allow me to devise a way to isolate every 7th row (THAT part is the sticking point), I'll surely post the solution to my own thread with updated keywords if I need it.
View Replies!
View Related
Pull Column Data (Sheet3) From Master.xls And Paste To Column 4, Sheet4 Of WorkingSS.xls
Pull Column Data (Sheet3) from Master.xls and past to Column 4, Sheet4 of WorkingSS.xls I'm assuming this would be done with VBA or a really exotic macro. The Funky Part would be that the WorkingSS.xls file column data is being copied/pasted too (WorkingSS1.xls or WorkingSS2.xls ect) the file may be different every time so I would need an insert in macro or VBA to "Choose File Please..." then continue. The Master.xls workbook has spreadsheet lets say "Sheet1" in which I need all the data in Column A (except the header or cell A:1) copied TO WorkingSS1.xls on Sheet4, Column B, but Column B already has about 6000 rows of info, so I need it copied to the very end of (A:6001 although it will be different everytime) or the first empty cell at the bottom of that column. next another Column from Master.xls workbook lets say "Sheet1" again in which I need all the data in lets say "Column B" copied to the WorkingSS1.xls on Sheet4, Column F. Caveat this time is that the data needs to copied to the same row as the first copy/past. So it would be pasted into F:6001. Double caveat is that the Column F contains no other data except for what we are about to paste in. I have several more steps of automation to be done here but this is the beginning and a big hump I need to get past. The rest I think I can do.
View Replies!
View Related
Merge Two Workbooks. Copy Column Data Based On Numerical ID Match Of Another Column.
I am trying to get excel to search a workbook/(or worksheet if easier) for a matching unique value and fill in its associated data. My first workbook has the SKU (A) filled in but not the UPC (B). My second workbook has both the SKU (A) and the matching UPC (C) filled in. I need to take both workbooks/(worksheets), compare the SKUs, and if a matching SKU is found, extract the UPC from Workbook 2 and fill in the UPC field in Workbook 1, and if no UPC is present in Workbook 2, then it leaves the cell in Workbook 1 blank.
View Replies!
View Related
