Add Two Columns Together And Then Divide The Sum By Another Column

May 11, 2009

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:

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

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?

I'm trying to divide the value in column A by the data in B:AO using VBA, however I need to repeat this for many rows (the values differ for each row).

There could also be a different amount of rows with data in each time, and there is also a single blank row after every 18 rows with data.

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.

I have attached a sheet as an example, as i am trying to Divide a cell by the number of populated cells in that column e.g column (A) has a table of 16 but only has 13 populated cells in that column, the sum would be something like this =sum(A18/13. Sometimes the column will have less and at times more populated.

Column A | Column B | Column C Los Angeles | Fire Dept | 3 Los Angeles | Health Services | 12 New York | Fire Dept | 8 New York | Health Services | 22 New York | Internal Services | 100 New York | Public Works | 7 Chicago | Health Services | 15 Chicago | Public Works | 56 Chicago | Social Services | 4

And I am trying to make it look like this:

Fire Dept Health Services Internal Services Public Works Social Services

I have conditional formatting on a cell based on a formula, when I entered the solution of "if then 0 " but the cell doesn't see the result as a 0, if I manually enter the 0 the conditional format works.

I even tried putting the formula in another cell and then referencing to that cell, still didn't work...

The "divide by zero error won't trigger the conditional either......

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.

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

I have just started to try and learn VBA for excel. the resource I have been using are books and the WEB. Some how, not sure how there was/is some code where all enteries in all worksheets are divided by 100, i.e if I enter 2222 after clicking the enter button the result in the cell is 22.22. I have deleted all macros and code however the problem still exist.

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 ?

I have minutes of meeting and have to divide them into different sheets against completed, and different departments and dept HOD responsible for it. How do I do it without "Copy/Pasting" the code? Also I have to remove the ones that are done. Any macro or something that can be written for these.

format of sheet Sl No. | Date | Remarks | Dept | Dept Head 1 | Dept Head 2 | Status

I have to get data into different sheets against Dept, Dept Head 1, Dept Head 2 and remove the "Done" status to different sheets.

I am working on my thesis empirical part now I have 10 years from 2003 to 2012 and need to divide this into 2 categories pre-cisis and post-crisis. How can I do that?

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

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.

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?

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)

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.

I have three different sets of numbers on a r12m that I have to add and divide by different numbers and not quite sure I have the formula right as when I do it manually on a calculator it comes out different.

I've the following formula but some of the results are returning the #DIV/0! result I know I need to bring some logic into my formula to rectify this but am at a loss as to how to do this.

I have a very long string of characters inside one cell.

Now, I would like to divide this string into multiple rows and I know that next row should start after each 80 character. In other words, 81th character is a start of a new row.

Say value in A2 and A3 is 4.5 and 5 respectively and the value in A1 is and 1 respectively B2 and B3 have zeros.

Case 1 : Now I would like to have value A1 in C2:AZ2 till the value in A2 gets exhausted.

Hence C2 = 1, D2 = 1, E2 = 1, F2 = 1, G2 = 0.5, H2 = 0, I2 = 0 ... and so on

Case 2 : Values in C3:AZ4 should be A1 till A3 gets exhausted. But the constraint is the values in the above range should start from/next cell when we left CASE1

For CASE-1 : C2 is =IF($A$2>0,IF(SUM($B$2:B2)$A$2,$A$1,0),0)

D2 is =IF($A$2>0,IF(SUM($B$2:C2)$A$2,$A$1,0),0)

and so on...

But this works only if the numbers are whole numbers, I tried a lot but I could not get a formula which works with integers. The above formula will fail if the number is say 4.5 -having decimal part.

For CASE-2 : C3 is =IF($A$3>0,IF(COUNTIF(C$2,1)>0,0,IF(SUM($B$3:B3)$A$3,$A$1,0)),0)

D3 is =IF($A$3>0,IF(COUNTIF(D$2,1)>0,0,IF(SUM($B$3:C3)$A$3,$A$1,0)),0)

When the numbers in A2 and A3 are decimals and not the whole numbers.

I have a large numeric spreadsheet of water quality data for a range of metals. On occasions where a particular metal is below the limit of detection, the entry reads ".