Add Two Numbers And Divide The Result By Another Number
Oct 5, 2007
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 9 Replies
ADVERTISEMENT
Jun 22, 2012
How could you divide all numbers in a pivot table by a certain number?
View 1 Replies
View Related
Jun 30, 2009
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 14 Replies
View Related
Oct 6, 2006
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 6 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
Dec 21, 2007
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 6 Replies
View Related
Jul 9, 2008
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.
View 14 Replies
View Related
Nov 28, 2008
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.
View 2 Replies
View Related
Jun 19, 2008
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 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
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
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
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
Apr 10, 2014
I have a sumproduct formula, I am trying to divide the output by the number of members used in the calculation:
This is my data set
4/10/2014102.6214/10/201499.5144/8/2014106.144/8/2014105.4
4/9/2014102.3284/9/201499.3864/2/2014105.8734/7/2014103.6
4/8/2014102.0884/8/201498.7953/17/2014105.5864/3/2014103.95
my sumproduct function is adding up all items that fall on that date which is fine, but i would love to calculate an average so divide by the number of items that were used in the calculation, is that possible?
=SUMPRODUCT(--(A1:G9=A11),(B1:H9))
View 3 Replies
View Related
Feb 10, 2014
I need a formula that will divide a number and if the answer is greater than a 100 then blank.
i've been using this =IFERROR(SUM(H4/C4,"",IF(K4>100,""))) but no luck.
View 6 Replies
View Related
Apr 2, 2009
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 2 Replies
View Related
May 31, 2013
I have 4 columns labelled 1st Year, Frequency, Spread and cost. then I have columns spanning upto 30 they represent years but can be labelled as 1 to 30. My challenge is that I want a formula/function/macro that depending on what I type in the first 4 columns it will populate the remaining 30 columns with either cost or 0.
Example: if I had £9,000 at a frequency of 10, starting on the 5th year with a spread of 3.
The cost represents the cost of each frequent. so in the example I would get £3,000 on each of the 5th,6th and 7th years then another £3,000 on the 15th,16th,17th years and finally another £3,000 on the 25th, 26th and 27th years.
what I want to be able to do is have one function/formula that I can paste across all of the year cells. I currently have a formula that can copy the cost at different frequencies but I cant figure the spread out.
=IF($K:$K>0,IF(OR($8:$8=$J:$J,MOD($8:$8-$J:$J,$K:$K)=0),$L:$L,0),0)
where K is frequency
8:8 is year
J is cost
what is the best way to approach this?
View 2 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
May 22, 2014
With the sizes in in B6 AND B7 these result in an error in B68 , the formula is in B68 and the tables are on sheet 3 , the increments have to stay as they are.
View 6 Replies
View Related
Dec 11, 2009
Does anybody know the code or the formula to divide a number generated by a formula. Id like to divide this number by 2 different set numbers. Meaning if my number comes out ot 19, id like to divide this by 5.5 and if it's not a whole number which this isn't (it's 3.45), i'd like to round this number to a whole number and add 2.5. My end answer would be 3 5.5 and 1 2.5.
Windows XP
Excel 2003
View 9 Replies
View Related
Apr 3, 2012
I have few numbers in Column "A". Few are normal figures & few are decimal numbers, like this:
20
5
5.63
4.5
200
53.263
125.5
Now I want to filter only the decimal numbers, so that after filter the result will show like this:
5.63
4.5
53.263
125.5
But I don't want to use any other column for doing so. I can do it using MOD in other column but I want the result in the same column.
View 7 Replies
View Related
May 30, 2009
I have an inherited formula and I am not sure if it is giving me the correct answer. It is:
=0.03*668*0.75*0.10
The result is 1.503
What I am aiming for is to get 3% and 25% of 688, deduct the results from 688 and then get 10% of that answer.
Is the inherited formula correct?
View 3 Replies
View Related
Mar 23, 2008
The list columns 5,7 and 9 has number entries.The userform has 3textboxes.is it possible to link total amount of this numbers result into the textboxes.Like column 5"CZ" entries total will showup in textbox10,column 9"DD" = textbox14
Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim a, i As Long, ii As Long, b(), n As Long
ListBox6.Clear
With TextBox2
If .Text = "" Then Exit Sub
If WorksheetFunction.CountIf(Range("cv:cv"), .Text) = 0 Then
MsgBox "No Entry !"
TextBox2 = ("")
Exit Sub..........
View 9 Replies
View Related
Feb 10, 2009
I have a count for each site for certain cloumn headers.
But i want to collate these so that if there is a 1 in the column the it will output it with the column header. But there are 10 column headers and I would like to get a result that has all the columns with 1 in.
eg:
a b c d e f g h i j k l m n o p q (Organic Suites) (Inorganic Suites)
sitea 1 0 1 0 1 1 0 1 0 1 1 1 0 0 0 0 (O1 O3 O5 O6 O8 OS) (I1 I2)
View 6 Replies
View Related
Apr 21, 2009
I am using the SUBTOTAL function, with reference value 4 to return the max value from a filtered list.
What I'd like to be able to do is return the values of cells on the same row of the returned max value. Effectively if I can extract, somehow, the row number of the row where the max value exists, then I can use that to get the rest, but I'm at a loss as to how to do this,
View 14 Replies
View Related
Jul 26, 2007
I work in a finance dept and we often need to divide our numbers into 12 months but I need to only work with whole numbers, not decimals. Is there a way for example of forcing either in a formula or with VBA code to get 10,000 to divide equally by 12 months? At the moment it comes out at 833.333333. What I want is something that randomly assigns 833 to any one of the 11 months of the year and for the other remaining month put in the difference which is 837 to get the total to add up to 10,000exactly.
View 9 Replies
View Related
Dec 28, 2013
I have been using the following formula to add time (hh:mm) in the last 28 rows (inclusive) of Column B into an adjacent cell in Column C (Column A is the date):
=SUMPRODUCT($B1177:$B1204)*24
It is becoming tedious to copy/paste the formula - Is there a formula that would define all cells in Column C to be the sum of the last 28 rows of Column B?
View 3 Replies
View Related
Jan 31, 2014
Is there a way to tell Excel to sum only up to a certain point? Like if A1 through AF equal 50, but I only want it to sum up to 40, to stop at 40?
Is there also a way to tell Excel using IF that if a statement evaluating a number is true or false, to return the value up until a certain number. For instance, if the value is 8 or less, then it should return that number, but if it exceeds beyond 8, say for instance 12, then if the value is 12, only return 8.
View 1 Replies
View Related