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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
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
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
Add And Subtract Numbers With No Carry Or Borrow
I want to input pick 3 (3 numbers) into a cell and with the assigned SUM to that cell to add +123 to the 3 numbers I input, but how do I get the answer not to carry over? For pick 3 games, the numbers are; 0,1,2,3,4,5,6,7,8,9 (ten total). When I add +123 to 987 I get 1110. I don't want that. I would like it to show 010 instead, lotto numbers no carry over.
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
Subtract Negative Numbers Within Pivot Table Data Field
Ive attached a spreadsheet showing what im trying to achieve. basically i have a scheduling objective, in which only a few slots (AA, BB, CC) are available. so the starting availability is fixed. for this example assume the starting values are 6,7, and 8 respectively. as 'bookings' are entered, im using the value of 1 against A, B, or C respectively, to indicate that AA, BB, or CC should be decreased by 1 unit of availability. im trying to incorporate the subtraction within a pivot table. as you can see i am way off. the hypothetical formula logic that im trying to implement within the PivotTable would look something like this: for A,B or C, sum all (1) values for each instance of Date and Stage... this will give total bookings add this total value to the starting values of AA, BB, CC respectively to get the new availability
View Replies!
View Related
Formula: Subtract The Values
Need a formula that will subtract the values in X:Z subtracting the lowest value from the second lowest, then using that value and subtract from the 3 value. The outcome will always be positive and range from 09. Expected results are in Col AA. Then I need an adaption of that formula to read a 3digit value in one cell. ******** ******************** ************************************************************************>Microsoft Excel  CASH 3 MID MOSTLY.xlsx___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutW1=WXYZAA111/30/094774211/29/099636311/28/091632411/27/097687511/26/095775611/25/097801711/24/093003811/23/097700911/22/0952851011/21/0990361111/20/09346512 13 1411/22/095285 1511/21/099036 1611/20/093465 Sheet2 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View Replies!
View Related
Formula To Subtract A Value From A Cell
I need a formula that will subtract a value entered in A3 from a valued entered in A2 to be displayed in A1. Also...the Value displayed in A1 must be the value of A2 minus the running total in A3. Ex...If I type '10' in A2 and type '3' in A3 the value shown in A1 must equal '7'. If I then type '2' in A3 the new value in A1 must equal '5' (this being the value in A2 of '3+2' minus from the original value in A2 of 10) So..A2 = inputted value , A3 running total of inputed value, A1 differnce of A3 from A2. Is this even possible? The whole idea is this....if you type a number into a calculator, and then subtract a number from the first number, the calculator then displays the new total. If you then subtract an number from that NEW total, the calculator then displays that NEW total. Thats what I'm trying to recreate in excel.
View Replies!
View Related
Formula To Subtract Pounds & Ounces
I need to build a spreadsheet that subtracts from a cumulative weight recorded in pounds and ounces. For example, in a fishing tournament, a person has 5 fish to weigh. They place their fish one at a time in a bag hanging from a digital scale that reads in pounds and ounces, and record the cumulative weight as the fish are added. We want to know what the total weight is of all 5 fish, but also what the individual weight is of each of the 5 fish. The easy way to do it is to place the first fish in the bag and record the weight, then place the second fish in the bag with the first fish and record the cumulative weight of the 2 fish together. Continue this until all 5 fish are in the bag, and record the final total weight. Then, to get the weights of each individual fish, the first weight is as it is, the second fish weight would be Cumulative Weight recorded for Fish 2 minus the weight of Fish 1. To get the weight of Fish 3, you would subtract the Cumulative Weight for the first 3 fish from the Cumulative Weight of the first 2 fish, and so on. For example: Weight of the Bag with: 1 Fish: 1lb 2oz 2 Fish: 2lb 14oz 3 Fish: 5lb 6oz 4 Fish: 9lb 7oz 5 Fish: 11lb 9oz. We can subtract the cumulative weights to determine the individual weights of each fish added to the bag and know that: Fish 1 is 1lb 2oz Fish 2 is 1lb 12oz Fish 3 is 2lb 8oz Fish 4 is 4lb 1oz Fish 5 is 2lb 2oz Easy to do in my head, or on paper, but not so easy to do in Excel because it's pounds and ounces, which is how the digital scale reads out. But, when you're doing this for 2030 fishermen, it's not that easy to do it on paper.
View Replies!
View Related
Formula To Subtract Invoices From Total Amount Available
I have 2 tables, one with invoices, the other with purchase orders. I would like to have a "PO Amount Remaining" column on the invoice table that looks up the PO listed on the invoice with the PO $ amount on the PO table. Once it is matched, I would like to subtract the total invoices to give me the amount of the PO that is left. Basically I would like the "PO Amount Remaining" column right now to have $4,200 listed in each row for invoices 1 & 2.....
View Replies!
View Related
Match/Index Formula :: Multiply Last 3 Cells In A Row And Subtract 1
I am having a little trouble with multiplying a few formulas. I am looking for a formula that will multiply the last three cells in a row that contain data and subtract 1. Below is an example of the type of data I am working with and the formula I am trying to use but is not working. The formula is for the cell highlighted in red. Every quarter the last three cells being referenced will change.....
View Replies!
View Related
Formula =AVERAGE(B16:L16) To Give The Average
I'm using the formula =AVERAGE(B16:L16) to give me the average. However I have a couple of problems with this. Firstly I would like to exclude the value zero from the average. Secondly to also ignore the lowest and highest values. Example, if the values in the cells are 0,1,2,3,4,5,6,7,8,9,10 then the current result shows 5, by ignoring the 0 and lowest value 1 and highest value 10 the average should be 4.5.
View Replies!
View Related
Average Row Of Numbers Using Different Row #'s
Field 1 input: Beginning Date (e.g 7/17/09) Field 2 input: End Date (e.g. 7/23/09) Need: Average costs on and between these given dates From table with this information: Date Market two 7/16/2009 80 7/17/2009 80 7/20/2009 82 7/21/2009 84 7/22/2009 82 7/23/2009 82 7/24/2009 82 7/27/2009 82 I need Excel to average Market two 7/17 to 7/23 (80, 82, 84, 82, 82) based on the dates I give it. If I change the date to 7/20 to 7/24, I then need excel to average (82, 84, 82, 82, 82) How can I get Excel to average based on different dates? Or basically, how do I get it to pull rows between 2 inputs?
View Replies!
View Related
Average Of The First N Numbers In A List
Hello, I have a column with 100 numbers in it. I want to be able to choose a number N and then calculate the average, minimum and maximum of the first N numbers in the list. I have played around with countif and sumproduct but can't figure it. I don't know how index works, and wonder if that is the key.
View Replies!
View Related
Average Numbers In Sequence
I have cells O1 to O300 numbers in random sequences of no more than 10 I need to average the 5 highest numbers in each sequence eg: O P 1 2 16 (result) 2 20 3 4 4 6 5 14 6 12 7 8 8 10 9 16 10 18 11 blank cell 12 15 9 (result) 13 3 14 6 15 12 16 9 17 blank cell 18 blank cell 19 4 23 (result) 20 16 21 20 22 40 23 8 24 11 25 28 26 blank cell down to 300 rows, the sequence of numbers can be from 1 to 10 but never more than 10 I got a function : =IF(COUNT(O1:O10>=10,AVERAGE(LARGE(O1:O10,{1,2,3,4,5}))," ") but that works only for 10 numbers and not for less
View Replies!
View Related
Find Average Of Last 4 Non Zero Numbers
I am trying to figure out how to take the average of the last 4 "nonzero" numbers in a series of values. The sheet looks something like this, numbers are all in one row: 7 , 7 , 7 , 7 , Total = 28 , 8 , 8 , 8 , 8 , Total = 32 , 0 , 0 , 0 , 0 , Total = 0 , 8 , 8 , 8 , 8 , Total = 32 , 7 , 7 , 7 , 7 , Total = 28 , 8 , 8 , 8 , 8 , Total = 32 , 0 , 0 , 0 , 0 , Total = 0 , 8 , 8 , 8 , 8 , Total = 32 I'm looking for a formula that will look at all the last few totals (starting from the end) and take an average of the last 4 totals that were > 0. In this case it would average {32, 32, 28, 32} ignoring the 0 total.
View Replies!
View Related
Finding An Average Couple Numbers
I am trying to get an average of a couple numbers, but I have to enter both numbers in one cell. I have to enter the numbers in a cell as a range (ex. "10003000"). I need to convey it as a range in the spreadsheet I am doing, but in a separate cell I need the average of the extremes (1000 & 3000). Is there a formula or anything that would let me get the average of those two numbers(2000) directly from that one cell? If needed, I could make the cell "1000,3000" instead. I just don't want to make two separate cells, one saying 1000 and the other saying 3000.
View Replies!
View Related
Place Average Of The Five Numbers In Column
Column B values are the average of the most recent five values in column A. The formula in Cell C1 is =B7 so that the value in C1 is 1.17. My question is: When I place a value in cell A8, which will result in the average of the five numbers in column A4A8 being placed in cell B8 how do I make the value in C1 change to be the value which is in cell B8 rather than the value which is in cell B7? Note: I just want to be able to pick up the most recent average value the method whereby it is done is of no consequence. However I cannot think of an alternative. Example:.....
View Replies!
View Related
Calculate Average Where All Numbers Equal X
I have done is created an Officer Evaluation Form in Word for my Police Chief and the Scores for the different observations are: N/A, 1, 2, 3, 4 and 5. If for example there are 4 observations and one of the observations is "N/A" for not applicable or not observed and the rest are all 5's I want the formula to ignore the field(s) with the N/A and still come up with an average of 5. The way I have it set up now which is: =AVERAGE(KOW1,KOW2,KOW3,KOW4) it comes up with an average of 3 when I put a N/A in field KOW1 and all 5's in KOW2KOW4.
View Replies!
View Related
AVERAGE Row Of Numbers And Return Corresponding Numeric Label
I have one Row that houses numbers 8090 in seperate cells (11 columns A20: K20)  these are my Numeric Labels. I then have several other Rows that span the same number of columns as the Numeric Labels (A21:K100) and house Numeric Values that relate to the Numeric Labels. I would like to AVERAGE the Numeric Values in each Row and have a Numeric Label (value) that corresponds to the calculated average returned as the result. Example: Sample Data A20:K20 (Numeric Label) 80 81 82 83 84 85 86 87 88 89 90 A21:K21 (Numeric Value) 07 06 05 03 09 09 00 02 04 12 10 Based on the above data  the Expected Result is 81 Average = 6; Return Numeric Label = 81 The leading zeros shown in A21:K21 is for alignment purposes only. I would appreciate two formulas: 1) Includes zeros to be averaged 2) Excludes zeros from being averaged I have tried variations on this =INDEX($A$20:$KJ$20,MATCH(AVERAGE($A21:$K21), $A21:$K21,0)) but receive the #N/A error message.
View Replies!
View Related
Allocate Numbers To Letters And Find Average
formula to do the following: Assign numerical values to the letters G, A, R each having the values of 3, 2 and 1 respectively and then take an average of their values. Please be aware that in some cells there may be no letter. So in a 5 cell range the values could be: blank, G, A, R, blank which equates to a result of 2 for an average, (3+2+1)/3 (the two blank cells are discounted).
View Replies!
View Related
Average Mix Of Positive & Negative Numbers Separately
I have a column which is populated with profits and losses from a trading account. Here is an example: Column I 400 200 100 150 50 What I want to do is average all the profits, and average all the losses so I have two numbers  the average winner and the average loser. Continuing with the example from the 5 trades above, the average winner is 300 and the average loser is 100. How can I do this in excel?
View Replies!
View Related
Average Elapsed Time With Conditions Using Dates & Numbers
Dataset is 220K rows. A small subset follows. Using an array calc with criterions Day = 1 and Hour = 1 summed on Elapsed Time returns 81 with an average of 11.57 for 7 events of which there are 5 distinct dates. The function =SUM(IF(D2:D14<>"",1/COUNTIF(D2:D14,D2:D14))) returns the number of distinct dates in the dataset ie 7. Is it possible to combine these two methods to create an average of elapsed time by day and hour using a denominator of distinct dates for only that combination of dimensions? I have attempted to calculate an item in a pivot table using a different denominator without success. day Hour Date Elapsed Time 1 0 5/03/2009 25 1 0 5/03/2009 24 1 1 5/03/2009 23 1 1 7/03/2009 11 1 0 7/03/2009 5 1 1 8/03/2009 2 1 1 8/03/2009 8 1 1 9/03/2009 4 1 1 9/03/2009 17 1 0 9/03/2009 22 1 1 10/03/2009 16 1 2 13/03/2009 12 2 1 14/03/2009 15
View Replies!
View Related
Good, Average, Fair, Poor: Automatically Use One Of The Numbers That I Have Set And Add It To Total
In creating a chart that calculates home prices I need to be able to just enter "Good", "Fixer", "Remodeled", etc...and have that automatically use one of the numbers that I have set and add it to my total. I have created a box with variables for "Good", "Fixer", "Remodeled", etc...How do I make it so I can put any of those variables into the condition of the house row and make it add the dollar amount specified into the totals box? This is the very simple code that I am using to find the totals so far: =SUM(C5:J5) Here's what I need: Also, the plug in values in the first picture WILL CHANGE and I don't want to have to change the formula. So I need something to point to those cells.
View Replies!
View Related
Formula For Average
Cell: F6 is to represent "average weekly offering". D2:D53: represents the Total Given Year To Date. I need a Weekly Average for Offering, so is it possible to have a formula that can show the average weekly offering in cell F6?
View Replies!
View Related
Daily Average Formula
I need to count the daily average of a task to a week ending number. I need to see the current average after each day during the week. Example Mon = 2, Tues = 4 AVERAGE is 3 Wed = 2 AVERAGE IS NOW 2.6
and so on averaging out after each day is added.
View Replies!
View Related
AVERAGE Formula Getting #VALUE! Error
I am trying to do an average of quartely results (2008 results) and because certain quarters can have no data present then my quarter shows 0%. But if i do a simple average of all Qs then the 0% affects my overall year to date. I need to keep the 0% because it may be a legit value. So i've devised a way to is if we actually have something in the cell (using isblank). So i test with isblank and if something is present then i use the Quartely value. It could be 50% or it can be 0%. I would like to incorporate the snapshot but i can't because i do not have access to uploading a picture. If any of you wish i could email the pictures. When i test out my formula in different cells its works but when i combine them all together i get #VALUE! error. In the example the error comes from the Q3 which test out and should give "" but when its added to the formula i gert #VALUE!. Shouldn't the average function disregard "" cells? My formula is: =average((if(and(isblank(F33),isblank(J33),isblank(N33))," ",R33)),(if(and(isblank(T33),isblank(X33),isblank(AB33)),"",AF33)),(if(and(isblank(AH33),isblank(AL33),isblank(AP33)),"",AT33)),(if(and(isblank(AV33),isblank(AZ33),isblank(BD33)),"",BH33))) Maybe my logic is flawed or there is a better way to do it. Either way i would like your opinion on it.
View Replies!
View Related
Formula AVERAGE The Sheet
There are (i think) 5 tball coaches 25 5 yrs old 44 6 yrs old each were score on RUN, THROW, GROUND BALL, FLY BALL, BATTING, scores were: 5Excellent, 4Very Good, 3Good, 2Average, 1Fair So I was thinking of making 5 sheets (one for each coach, with their scoring for each players) The last sheet will be what the Average would be for each area from each coach, giving me a general idea about the players that I didn't get to watch and score. So how would I formula the Avarage sheet? Again I'm on short notice, and need to have this done on Sunday, when the information starts to come in and I can dataentry the scores, and have it ready for Monday nights draft.
View Replies!
View Related
Average Formula For Different Columns
Looking for an average formula. All found formulas average within a string (ex. A1:F1). My spreadsheet contains a series of three columns. Example: Column A reflects a number stat for a month (January). Column B reflects another number stat for the same month (January) Column C reflects the difference between Columns A & B. Column D reflects a number stat for the next month (February) Column E reflects another number stat for the same month (February) Column F reflects the difference between Columns D & E. and so on. I would like to average the rows stats reflected in Column A, D, G, J, and so on. Also, I would like to average the rows stats reflected in Column B, E, H, K, and so on. Also, I would like to average the rows stats reflected in Column C, F, I, L, and so on.
View Replies!
View Related
Filtering & Average Formula
I'm building a yearly workbook with multiple worksheets (i.e. one for each month, quarterly reports and one final yearly report). My question is that although I can have the data transfer easily to my quarterly and year end report sheets, I can't seem to figure out how to filter my data and then calculate an average based on personnel averages in the various categories I have set up. My example is that I want to be able filter my personnel shifts (i.e. 7am, 3pm, 11pm, etc) and then set up a formula which can for example calculate a column of data and average it only based on the employees working that particular shift. My formula should adjust for the shift time I pick.
View Replies!
View Related
Formula Average The Values
I am trying to do a stock ( goods) inventry for a catering industry, meaning most good swill keep havinf cost prices changing. I am trying to use a LIFO system. To achieve the end result, i am trying an average formula and i am trying something as follows: =SUMIF(I4:I20,">0",I4:I20)/COUNTIF(I4:I20,">0") To try it out, i created a similar page with the same amounts but using a normal multiplication and division formula, the totals dont marry.
View Replies!
View Related
Array/average Formula
Trying to average multiple cells that are not consecutive and contain zeros that i want to disregard. I have tried to do an array formula but the cells must be consecutive so I am at a loss. This is what i tried. =AVERAGE(IF(A2,A8,A12<>0,A2,A8,A12,))
View Replies!
View Related
