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

4+4/636 = 79.5

d2+f2 (divided by) h2 = 79.5

View 3 Replies
ADVERTISEMENT
May 24, 2006

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 2 Replies
View Related
Oct 10, 2009

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 9 Replies
View Related
Feb 27, 2009

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 9 Replies
View Related
Aug 15, 2013

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.

View 1 Replies
View Related
Feb 16, 2007

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 9 Replies
View Related
Jan 18, 2010

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 14 Replies
View Related
Apr 11, 2014

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.

test sum skip blank cells.xlsx

View 2 Replies
View Related
Jan 31, 2013

I have a large spreadsheet converted from pdf whose data still appears in A4 reading format.

I need to move part columns of data from 6 columns to form 1 large column in column A.

For example, move range B8 to B76 beneath range A8 to A76 and range C8 to C76 beneath that etc, page by page working through all 270 pages !

Also need to delete unnecessary 'page headers' throughout as in rows 2-6

View 2 Replies
View Related
Jun 30, 2014

I have a excelsheet that looks like this:

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

Los Angeles

3

12

New York

8

22

100

7

Chicago

15

56

4

View 8 Replies
View Related
Apr 8, 2014

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

[Code] ....

View 3 Replies
View Related
Oct 10, 2008

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 3 Replies
View Related
Aug 20, 2009

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 12 Replies
View Related
Feb 2, 2014

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.

View 3 Replies
View Related
May 21, 2009

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 9 Replies
View Related
Oct 16, 2006

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 6 Replies
View Related
Apr 7, 2014

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.

How do I do it without manual labor?

View 1 Replies
View Related
Aug 1, 2014

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?

View 3 Replies
View Related
Aug 1, 2014

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?

View 10 Replies
View Related
Jun 15, 2009

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 2 Replies
View Related
Jun 20, 2014

I have a data extract that gets generated at the end of each month that I post into the 'Full Extract' tab on my example spreadsheet.

writing a macro that will divide the various items in the 'Extract' based on the Reference, (some items may need to go into multiple sheets)

then draw down the formulas that need to be applied to the item.

View 2 Replies
View Related
Dec 16, 2009

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 9 Replies
View Related
Sep 6, 2005

=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 14 Replies
View Related
Aug 27, 2009

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 3 Replies
View Related
Jan 6, 2012

How to divide two cells if there is no text in the first?

i.e.: if there is text display a 0 and if there is a number to divide it with a second number in another cell.

View 7 Replies
View Related
Sep 21, 2012

I am trying to divide the total of two groups of cells and I used the following formula:

=sum(B3:M3)/(B17:M17)

However, that is not a correct formula.

View 4 Replies
View Related
Apr 4, 2009

I have an array in my code and I need to divide each elements of the array with a constant.

View 9 Replies
View Related
Jul 20, 2006

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 6 Replies
View Related
Nov 3, 2006

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 2 Replies
View Related
Mar 25, 2007

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 6 Replies
View Related
Feb 1, 2008

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 5 Replies
View Related
Dec 16, 2013

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.

The formula I have put together is

=sum(O4:z4)/10)+(sum(o5:z5)/200)+(sum(06:z6)/1000)

This is the three rows of data

221121

2333443201142

402448314034292331351663

Using the formula above the result is 0.81 but if I do it manually on a calculator it is 1.6 (by doing each line individually and adding results)

View 6 Replies
View Related
Jan 21, 2014

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.

=SUM(1/COUNTIF(AB:AB,AB:AB))

View 2 Replies
View Related
Mar 10, 2014

I have a lot of cells containing numbers. Some numbers are small, some are big.

I want to divide all numbers which are greater than 300 by 1000.

IF cell A2 > 300 THEN divide by 1000.

How to do that? Is it possible to convert value in the same cell like using conditional formatting principle?

View 7 Replies
View Related
Oct 2, 2008

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 3 Replies
View Related
Apr 18, 2009

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 9 Replies
View Related
Dec 20, 2011

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.

How can I do that?

View 2 Replies
View Related
Jan 11, 2012

I am trying to do:

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

Hence C3 = 0, D3 = 0, E3 = 0, F3 = 0, G3 = 0.5, H3 = 1, I3 = 1, J3 = 1, K3 = 1, L3 = 0.5 ...

I am using the below formula -

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.

View 9 Replies
View Related
Oct 1, 2013

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

View 4 Replies
View Related
Feb 6, 2014

I am trying to add 2 groups of cells and then divide the number by 60?

I can't get it to divide? It will only total.

=SUM(T807:T832)+SUM(T837:T846)/60

View 2 Replies
View Related