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 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 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 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 (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 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
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 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 B5E10 and G5J10. I need a macro which will insert two columns after column E. So then it would push what was in G5J10 to I5L10. 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
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 it´s 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 semicolons ( 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 semicolons 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
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
