Tracker
HOME    TRACKER    Excel

# 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 paste-special-value on A1 Is there any way to convert the number 60 into 1:00:00 format without having to do calculation in temporary columns?

Related Forum Messages:
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?

Add Two Columns Together And Then Divide The Sum By Another Column
I may not even be calling it the right thing. but let me explain what i'm looking to do. i've got three columns on a spreadsheet and i need to be able to add two columns together and then divide the sum by another column. so something like this:

4+4/636 = 79.5

d2+f2 (divided by) h2 = 79.5

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.

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:...Temp|Temp_PandL" exists. If not, then it will be created:

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 "FY06-10 PROJECTS 19-Jan-10 08-32'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.........

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?

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.

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

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 (M-F). 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)?

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,F5-21,F5)
Then in Cell Z5 =IF(P5>=25,P5-21,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.

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 ?

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.

Function 2 Divide A Number (ie \$1.00) By % (ie 25%) To = (\$4.00)?
=1/25%

is it that you want a UDF

Function MyFun(a,b)
MyFun = a / b
End Function

Usage:
=MyFun(1, 25%)

Mangesh

"Gonecoastal1" <Gonecoastal1@discussions.microsoft.com> wrote in message

How To Divide An Array With A Number In VBA
I have an array in my code and I need to divide each elements of the array with a constant.

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?

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%, 11-20%, 21-30%, . . . bottom 10%)?

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.

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)

Divide 2 Cells From Different Worksheets
i want to divide two cells from different sheets using a macro

i have this at the moment

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

Divide Two Columns But Show My Zero
I have to divide two columns. Sometimes there is a zero in one of the columns. Now, I realize that nothing can be divided by zero, but isn't there a way to show a zero instead of the #ref symbol???

Replace 'Divide By Zero Error' With Just A Zero
Is it possible to replace 'Divide by Zero Error' with just a Zero? See a small section of what I'm trying to do. I can't add the full spreadsheet it huge over 4 MB. way too big for this forum.

Temp Macro To Rename Macro
Is it possible for a macro to rename a macro? I have a Temp macro that I want to rename to Auto_Open (or maybe auto_close).

Vlookup: Divide It By 1000 Then Multipy By 24
I am having a problem with vlookup. I have the formula working but would like to divide it by 1000 then multipy by 24. When I enter /1000*24 after the colon in the vlookup formula my answer is 0(zero). If I don't try to divide and multiply the answer is correct.

Math Functions (divide And Mulitply)
I need to sum column L then divide that by 36 then multiply that by the sum of column N. How can this be accomplished with a formula?

Group Items And Divide By Coloured Row
I need to group items by date and time and for each group to be separated by a coloured row. So for example september 3rd at 2am may contain rows of info, after that would be a coloured row.

How To Re-divide The Data At The Same Time Interval
I have 2 columns of data (value and time):

for example:

15 4/2/08 13:00
4 4/2/08 19:00
7 4/5/08 12:00
13 4/9/08 3:00

They are continuous data. so I want to divid the value into hourly data as follows.

15 4/2/08 13:00
? 4/2/08 14:00
? 4/2/08 15:00
. .
. .
? 4/9/08 2:00
13 4/9/09 3:00

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.

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?

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.

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?

Divide By X Automatically In Same Cell Upon Entering A Value
How do I enter a value into a cell and divide that number automatically by another value all within the same cell.

For example..

I want to enter 20 into a cell and have it change into 17.24 (20/1.16) automatically all within that same cell.

Divide Cells With Numbers & Signs
i want to divide eg. >53/>12 but i get #value! error. i have huge columns full of is so i dont wanna so 53/12 kinda thing 1000 times. whats the formula i need to use?

Function/Formula To Divide 2 Different Ranges & Add Them
I am familiar with the SUMPRODUCT function, but is there a similar function that will divide the cells of two columns and then add them together?

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 = 50-55
A2 = 45-67
A3 = 56-90
A4 = 49-80
A5 = 51-55

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).

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.

Divide 2 Cells & Express Result As Percentage
I need to divide Cell "A" with Cell "B" and put have the final sum in Cell C. e.g. Cell A===10 Cell B=2 Therefore, I want Cell C to say 5

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 30-52 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)

Search Folder For Files Containing &quot;Temp&quot;, Delete Files
Is there a code that will search a pre-determined 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.

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

Macro For A Formula To Divide One Cell By Another And Past It In A Third Cell.
I am trying to write a macro where I divide the value of one cell by that of a second cell, multiply it by 500 and then to paste the outcome of the formula to a third cell. This is what I wrote

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.

Number Formatting: Enter A Number It Seems To Divide It By 100
I having a bit of trouble entering numbers in Excel, every time I try to enter a number it seems to divide it by 100. For example : - 1 entered in to a cell becomes 0.01, 100 becomes 1....etc. I've tried formatting the cells and had no luck, tried Tool>Options but not sure where to look.

Lookup/Match: Compare A1 For The Values In Column B, Then Return The Corresponding Cell (column C) In Column D
I've been searching the forums for this problem but I can't seem to find any answers. Anyway, this is the problem. See screenshot.

I want to compare A1 for the values in column B, then return the corresponding cell (column C) in column D.
e.g. D1 = 2, D2 = 1, D3 = 4, D4 = 5 and D5 = 3.

If Column Contains Data Then Insert A Blank Column And Shift Specific Column To The Right
What I'd like to do is; If column C contains data then insert a blank column and shift column C to the right.

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 root-restrictive feature:<p> More than 60 inches
0014B27<@33><i>Depth to root-restrictive feature:<p> More than 60 inches..............

Subtracting Column B From A, And Leave Third Column Blank If No Value In Column B?
I have a spreadsheet with three columns (A,B,C). I want the third column C to be column A - B (A minus B) for each row, but only if there is a value in column B.

If there is no value in column B, then I want that row in Column C to just stay blank.

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

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.

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.

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.