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

Divide All Numbers In Pivot Table By Certain Number?

Jun 22, 2012

How could you divide all numbers in a pivot table by a certain number?

View 1 Replies View Related

Count Number Of Rows That Have Numbers And Divide Sum Of Cells

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

Divide 2 Cells & Express Result As Percentage

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

Sum And Divide Three Different Sets Of Numbers To Get One Answer

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

Divide Cells With Numbers & Signs

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

Divide Number Cell By A Set Or Fixed Number

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

Number Formatting: Enter A Number It Seems To Divide It By 100

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

Formula That I Can Add, Subtract, Divide, Average, Etc, Etc These Numbers

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

Function 2 Divide A Number (ie $1.00) By % (ie 25%) To = ($4.00)?

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

Divide Only With Number Of Cells With Value>0

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

How To Divide An Array With A Number In VBA

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

Divide Cell By X If Number Is Greater?

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

Divide A Sum By Number If Items In List

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

How To Add 2 Groups Of Cells And Divide Number By 60

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

Divide The Output By The Number Of Members Used In The Calculation?

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

Formula To Divide Number And Format If Greater Than 100?

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

Divide An Number Of Jobs Without Ending Up With Decimals

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

Divide Cost Over A Number Of Years At Different Frequencies?

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

Divide Cell By The Number Of Populated Cells In A Column

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

Numbers In Boxes Result In N/A Error

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

Dividing To Get A Result Of Whole Numbers Preset

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

Filter Only Decimal Numbers - Result In Same Column

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

Multiply Numbers & Return X Percent Of Result

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

List Column Numbers Result Show In Textbox

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

If Number =1 Display The Result

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

Row Number For Result Of SUBTOTAL Using Max

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

Round Division Result To Whole Number?

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

Add Number Of Rows With Result In Next Column

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

SUM To Certain Number - IF Statement Result Using Less Or Greater Than

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







Copyrights 2005-15 www.BigResource.com, All rights reserved