Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










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


View Complete Thread with Replies

Sponsored Links:

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

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
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
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?

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
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 (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)?

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

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
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
news:B257DADD-0042-4CF8-88DF-1D17AEAFF627@microsoft.com...

View Replies!   View Related
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.

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%, 11-20%, 21-30%, . . . 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
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.

View Replies!   View Related
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.

View Replies!   View Related
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?

View Replies!   View Related
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.


View Replies!   View Related
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

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

View Replies!   View Related
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?

View Replies!   View Related
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?

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

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

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


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
Column 9 To Be The Sum Of Columns 3, 6, 7, And 8
I need column 6 to show 15% of column 5, then I need column 9 to be the sum of columns 3, 6, 7, and 8. I just can't figure out how to do this. What I'm trying to do is take a wholesale price, get 15% of it to figure for shipping, then add the wholesale price, shipping charge, a drop ship charge, then my Paypal charge together so I can see what I am paying to ship a specific item...then I can figure out what to charge.

View Replies!   View Related
Splitting One Column Into Many Other Columns
I need a macro which place chosen parts from one column into another column.

For example I have following text in column E:

Column E
Flugschneise {f}
ringsum
(total) im Arsch [vulg.]
mutterseelenallein
Geld und Gut
Pensum {n}

*chosen content not always at the end of a cell!

Now i need a macro which puts chosen content from Column E to Column F or Column G. For example I want that all {f}s should be deleted from column E and put into column E. Or I want all {n} to be deleted and placed into column F. Or I want all [vulg.] to be deleted from column E and put into column G. If column F or G already consist of text then the new text( {f} or {n} or [vulg.] ...) should be added to the existing text but separated through a separator like comma or semicolon

It means that the macro is each time fed with information about what to delete from which column and where to put it. It would be cool if a simple msg box would ask for the needed information.

View Replies!   View Related
Insert New Column After Every 2 Columns
I have many excel sheets with many columns. I want to insert new columns but after every 2 columns, starting from column M.

View Replies!   View Related
Combining Two Columns To 1 Column
see the picture below to get an idea of what i want to do.

http://aycu15.webshots.com/image/362...0389296_rs.jpg

View Replies!   View Related
Sum Column If Criteria In Other Columns Are Met
I am trying to create a formula that will sum up a value in one column if certain critieria residing in other columns are met. I have attached a sample and the criteria.

View Replies!   View Related
Insert A Column Every X Columns...
I am looking for a VBA code (or alternatively a way of doing this in normal Excel) that can let me insert a column every X columns (for example every six columns).

Ideally I would want a code that can let me insert a specific column (the same) every X columns, but a formula that inserts a blank column every X columns will hopefully do.

View Replies!   View Related
Inset 2 New Columns In Next Available Column.
I have had a search around but cannot find exactly what I'm looking for. I have a worksheet with data from B5-E10 and G5-J10. I need a macro which will insert two columns after column E. So then it would push what was in G5-J10 to I5-L10.

The macro would need to look for the blank column (column F) and then insert two columns before it. The columns will be constantly changing so that why it needs to look for that blank column. After they have been inserted would it be possible to give the columns headings such as 'New column 1' and 'New column 2' in row 4.

View Replies!   View Related
Combine Two Dynamic Columns Into One Column
i need to take all cells in column A with values and all cells in column B with values and combine them into column C so i can sort and subtotal. Columns A and B may have 2 values or 2000 values.

View Replies!   View Related
Summe Column Depending Of Other Columns
I want to create a macro that make the somme of 2 other columns when the nummer of the first column ist double(more than one ) and put the somme in a new Rows. like this :

A B
23 5
23 12
23 2
45 4
45 1
46 3 in the news the resultat in the same columns its like this
A B
23 19
45 5
46 3


View Replies!   View Related
Split A Column Into Multiple Columns
The spreadsheet contains over 21,000 rows of data, and one of the columns (D I think) contains data as in the two examples below.

What she wants is to split this column at the semi-colons ( and have the column header as the "field" name.

Unfortunately not all the cells have the same number of "fields" as you can see. Some don't have an "addressLineTwo" while others also have "stateprovince".

Is it possible to split the column so each "field" goes into it's own column?

Please note that if a "field" is missing there is not two semi-colons to indicate an empty field. I'm also fairly certain that, between them the two examples below show all possible fields.

Data Examples.

addressLineOne:Road Belen Staana;addressLineTwo:Costado Oeste;city:SAN ANTONIO DE BELEN;highRate:194;latitude:9.97631;longitude:-84.20038;postal4005

addressLineOne:1766 Homestead Drive;airportCode:ROA;city:HOT SPRINGS;highRate:500;latitude:37.99662;longitude:-79.83079;postal24445;Rating:52;stateprovince:US

Didn't there used to be a "Split" function that split text over two cells? I'm sure I used it years ago, but can't find any mention of it in Excel 2003.

View Replies!   View Related
Hide Columns On Column Labels
I was wondering how I might be able to hide columns based on their 1st row labels with vba - I never know where these columns might be on different file I get but I want to hide the same columns every time. right now I am just specifying column numbers or letters but it's not working out:

Columns("A:C").EntireColumn.Hidden = True
Columns("G:I").EntireColumn.Hidden = True
Columns("K:R").EntireColumn.Hidden = True
Columns("T:V").EntireColumn.Hidden = True
ActiveSheet.PrintOut

Columns("A:C").EntireColumn.Hidden = False
Columns("G:I").EntireColumn.Hidden = False
Columns("K:R").EntireColumn.Hidden = False
Columns("T:V").EntireColumn.Hidden = False

View Replies!   View Related
Sum The Totals On One Column, If Two Other Columns Are True
(Excel 2003 on windows XP.)

I need to SUM the cells in column J when column E has the word "yes" and the range N:R has a letter "w" in one of the columns. Only one of the N:R columns will have an entry on any one row and it may not necessarily be a "W".

I have Googled, and being a beginner I don't comprehend the result! Some people were talking about using an add in, but I won't be allowed to add software to our PC's.

View Replies!   View Related
Average The Difference Of 2 Columns W/out A 3rd Column
I have two columns:

A B

1 10
2 9
3 8
4 7
5 6
6 5
7 4
8 3
9 2
10 1

I need a formula that takes the difference of the two columns (1-10, 2-9, etc.) and then finds the average for all of the differences. I don't want to have a third column with the differences and then take the average of those.

View Replies!   View Related
Unlock Columns If Column Is Not Blank In Any Row
Unlock columns if Column is not blank in any row

Using VBA
On Sheet("Draft Final") , search every row and

If Col A is not blank, then unlock Columns G: M and unlock Columns O:U

View Replies!   View Related
Create Separate Columns From One Column
I have a spreadsheet with these columns:Group_Name
Contact Name
Company Name
Contact ID

Situation:
The Group Name Column has the name of the group that the Contact Name is a member of. The Contact Name could be a member of many Group Names and therefore there may be many rows of the same Contact Name one with each Group Name that they are a member of. I have manually created separate columns for each Group Name in the spreadsheet that is attached.

Goal:
I would like to have a macro that will look at the Group Name column and create a new column for each distinct group. I then would like the macro to go down the Contact Name column (I believe that I will have to have it sorted)and look at the Group Name that is listed in that row and put "True" in the Group Column that was created in the above step. Then, go to the next row, if the Contact Name is the same, look that the Group Name column and put "True" in the corresponding Group Column in the FIRST ROW OF THAT CONTACT NAME. Then Delete that row.


View Replies!   View Related
Unhiding Columns (column A That You Can't Click On!)
I have columns A to N hidden and need to reveal them. I know you can manually drag each one to reveal it. But I thought there was a way through highlighting/menu options to reveal them all instantly.

View Replies!   View Related
Copy All Columns Data In One Column
I want a macro that copies all columns data in coulmn a which starts from row 2 and delete rest of columns data.

see mentioned below example....

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved