How To Incorporate An If Then Else Function To An Average Formula
Is there a way to include an if then else function to an average formula that will skip cells with zero?
I went into better detail in the attached example.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Incorporate A Vlookup Into The Formula
Last Name First NameSales CodeDepartmentSalesHours WorkedBase PayCommission RateAllbaughJoshuaAMMen's$ 4,000.00 8.00188.0072 I created this formula to show commission but it doesn't incorporate the array below. How can I incorporate a vlookup into the formula above showing this array of info? Sales CodeSales TitleHourly Wage QuotaCommision RateAMAssistant Manager$23.50 $200.00 0.03PT1Sales Assistant$10.50 $100.00 0.01PT2Sales Partner$11.75 $125.00 0.015S1Sales Associate$13.50 $150.00 0.02S2Sales Consultant$15.00 $175.00 0.025
View Replies!
View Related
Running Average Formula/Function
I wonder if it is possible to formulate a formula in which average values are recalculated after every added record. For example: Record 1 shows an amount of 200 so the average value after 1 record is (200/1) 200. Record 2 shows an amount of 250 so the average value after 2 records is ((200+250)/2) 225. Record 3 shows an amount of 210 so the average value after 3 records is ((200+250+210)/3) 220. I don't want to calculate all averages manually and most probably there must be a formula that is able to recalculate the average value after another record is added to the file.
View Replies!
View Related
Incorporate Adjacent Cell In Formula W/o Naming Cell Address?
I searched and I keep finding something that's not quite what I'm looking to do so I think I'm missing something really simple. Let's say that I want each cell in col b to be the sum of 5 plus whatever value is in the cell adjacent to it on the left (col). I know that you can just write a formula in each cell like... b1 will be =sum(a1+5) b2 will be =sum(a2+5) ... but is there a shortcut so that you don't have to write out the actual cell address for each one? Meaning  is there a predefined name or something that represents the cell to the left or right so you can just use that instead, allowing you to just copy paste the same formula all the way down the column? Something like... b1 will be =sum(left+5) b2 will be =sum(left+5) ... where "left" represents whatever value is in the cell to the left of that particular cell?
View Replies!
View Related
Incorporate Calendar
I have the following worksheet ,named "Calendar", to make an annual calendar and I'm wondering if there's a way to use it to make a calendar in worksheet named "NovemberDaily". On the worksheet named "NovemberDaily" I want to put in cells C14, G14, K14, O14, S14, W14, and AA14 "11/1/2007" if the weekday of the week matches the date"11/1/2007". Is this possible or is there an easier way of accomplishing this. I'm needing the date (e.g. 11/1/2007) in the cells mentioned because it ties into another formula that I'm using later in the worksheet.
View Replies!
View Related
AVERAGE Function Embedded Within An IF Function
TPR, DISPLAY and FEATURE columns generate a rating based off of an IF function. In the Executed column, I need TPR, FEATURE, DISPLAY to be averaged together...BUT....In I want the average only include columns where there are numbers. For example in row one the eqn would be (1+3+2)/3, but in row 2 the eqn would be (1+1)/2...can I state an average function within an if function? Or what would be the best way to create an eqn for this?? I have thousands of rows to complete and doing it manually is not an option. 0 Did not meet expectations 1 Below expectations 2 Met expectations 3 Exceeded expectationsTPRDISPLAYFEATUREExecuted?Effective?Comments132Coming off of a Dec promotion113111111221
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
Incorporate A Series Of If Statements
I would like to incorporate a series of if statements in column D of my macro that looks at the labels in column C. If the label is 10 characters long, it returns the full 10 characters. If the label is 11 characters long, it returns the leftmost 10 characters. What syntax would work for this?
View Replies!
View Related
Incorporate Concatenate Funtion: Return Text Rather Than A '0'
I have the following formula: ................... and excel is saying I used too many characters and to use the concatenate function to do so, but I have no idea how. I wanted to return text rather than a '0' in the case that all the original IF statements were false, I threw another IF statement in there and said if the original is equal to '0', then return "". If not, then run through the original again (I pasted the original again after the "").
View Replies!
View Related
Filldown For The AVERAGE Function
I am trying to create a table showing the yearly average occupancy rates per quarter for every year between may 1998 and december 2008 inclusive (for licensed hotels, motels and guest houses, and serviced apartments in Australia; if that interests you). I have a spreadsheet with a row of data showing the appropriate quarterly results for those dates. Let's say the data for this is displayed A1 through to A44. For the yearly per quarter averages, let's say they are to go from B1 through to B11, I know I could put, for each year, an average formula with the relevant data range. Finding this to be tedious, I tried to filldown. I learnt the hard way however (not being too aware of how excel's grammar ticks) that the fill down function will not work for me. i.e., if B1 contained the average for the data range A1:A4 (the four quarters of 1998), the filledin B2 would have the data range A2:A5 (i.e. the 2nd quarter of 1998 through to the 3rd quarter of 1999).
View Replies!
View Related
Stop #DIV/0! In Average Function
I have a data range  C4:C54 and I want an AVERAGE calculated in C56, but I only want a value displayed in C56 when a data entry is put in. Up to 50 data points could be put in anywhere e.g. only 14 values in C16:C30, or 44 values in C6:C50, or the full 50 in C4:C54. The formula I've been trying is =IF(ISERROR(C4:C54<>""),AVERAGE(C41:C54),"") However, when no values are in the range it displays DIV/0! whereas I'd like the cell blank.
View Replies!
View Related
Conditional Average Function
How do I perform average for a range and be able to exclude outliers. For example I am taking 100 measurements, some are not measureable and recorded as 9999.9. How do I average 100 values, but exclude all the 9999.9?
View Replies!
View Related
Average & Sum Function With Empty Cells
I have data in Column A as follows: A1 15 A2 A3 20 A4 56 A5 45 A6 A7 71 A8 A9 23 where cells A2, A6 and A8 are empty. I want to be able to AVERAGE or SUM the first four nonblank cells. I know I could manually select the cells, but I have a spreadsheet with 30 columns and 40 rows, and the data (including empty cells) in each column is different. Is there a single formula that will find the first four nonblank cells and then perform the AVERAGE or SUM function?
View Replies!
View Related
Average Function: Multiple Arguments
I need some assistance in using an array formula which averages based on multiple arguments. The relevent range names in my input data are: Col A  Manager Col B  Report Type Col C  Days taken to Complete and I have used Validation Lists to include several different Managers and 5 Report Types which I'll call A  E, with Days taken to Complete an input. Each Manager may have 0 to numerous instances of each report during each period. I'm trying to build a table which includes each manager and the average number of days taken to complete each report. The complication is that, while Report A & B are separate reports, for this purpose I need to get an average for A & B together. For C, D & E, I have used the following: {=AVERAGE(IF((Type="C")*(Manager="Smith"),Days_taken_to_Complete))} For A&B, I have tried: {=AVERAGE(IF((Type="A")*(Type="B")*(Manager="Smith"),Days_taken_to_Complete))} but this doesn't work. Could someone please advise me the correct formula if either of two states in a named range are to be used to average a second named range?
View Replies!
View Related
Average Function Based On Criteria
I'm trying to find the best Macro or Formula to get this done. Sheet 1 has a list of salesperson in column "A" and the total numbers they made on Monday (in the same row, column "E"), Tuesday (In the same row, column "F"), Wednesday (In the same row, column "G"), Thurday (In the same row, column "H"), and Friday (In the same row, column "I"). I also have a sheet for each individual salesperson. I need to find a Macro or a Formula that I can use to give me the average of the numbers if "A5" = the salesperson in Sheet 3 then avarage the numbers from "E5" to "I5" and I want the results to show up in cell "D35".
View Replies!
View Related
AVERAGE IF Function With Date And Characteristic
I will attempt to make this as easy as possible in explaining something I am looking to do. I am looking to Calculate the average lifespan of an individual based upon 1) They are "dead" (A status,either "Alive" or "Dead" found in Data!F2:F653) 2) They were born between 1/1/2007 and 12/31/2007 (Delivery Date provided in Data!C2:C653) The lifespans were provided on Data!E2:E653
View Replies!
View Related
Average Function That Excludes Zeros
I am working with averages of ACT scores and Class rank percentages. I cannot figure out how to exclude all the zeros and/or DNR (do not rank) values that are in those columns. For example if I have 10 items, and 2 are DNR, the I want to exclude the 2 DNR's in my average. I tried putting it in the Row or Column area and unchecking what I wanted to exclude and then drag it into the data area, but that didn't work either.
View Replies!
View Related
Average Function And Not Count The Blank Cells
I've got a spreadsheet that I do every month with columns of numbers that I average. This sheet has to match about 10 others similar. The columns are divided by Weekdays, Saturdays, Sundays. But some months there are no entries for certain cells on Saturday or Sunday. I thought that if I just used the Average function, it would dismiss and not count the blank cells. Alas, apparently not. I've highlighted in yellow the one column that I'm really having trouble with.
View Replies!
View Related
INDEX/MATCH Function To Retrieve Vlaues From A Table And AVERAGE Them (arrays)
I have 2 sheets recording feedback and summarising the overall percentages for each trainer. My boss wants me to do this by month!?!?! I have created a third sheet but I am not sure how to do the following: Look up the trainer name Look up the month identify the percentages for each category create an overall average of these percentages So for example if Jon smith trained twice in JAN getting 100% and 50% in cat1, it would display 75% in the cat 1 cell and so on.
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
Label Making (take The Information And Incorporate It Into A Label Format)
I have a spreadsheet with a customers information and various parts we make for them. I need to be able to take this information and incorporate it into a label format. I need something quick and easy as there can be 150 parts per customer Customer: ABCD Customer PO: 12345 Part Number Quantity AB 1 ** 15 EF 22 GH 14 and I need it to output: CUST: ABCD PART: AB PO: 12345 QTY: 1 CUST: ABCD PART: ** PO: 12345 QTY: 15 CUST: ABCD PART: EF PO: 12345 QTY: 22 CUST: ABCD PART: GH PO: 12345 QTY: 14
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
Formula To Return An Average
I am working on formula to return an average of data. Currently it is matching a text criteria. Thus if (the text in) column a = (the text in) column b, (return the average of) column c. The formula that I am using is =IF(A:A,B:B,AVERAGE(P:P)) This is returning  #value! Now is this a formatting problem in column P? Or is the formula I am using incorrect? I know that the text criteria (col A & B) matches.
View Replies!
View Related
Complex Average Formula.
The number of columns vary, but the number of rows is constant. Lets assume, max_col is the number of columns and max_row is the number of rows per report. Then, I need the following logic as VB macro. for row = 6 F6 = (G6*G4) + (H6*H4) + (I6*I4)+...+ ("max_col:6" * "max_col:4") / (G4+H4+I4... "max_col:4")
View Replies!
View Related
Database Formula For Average Of Top 20%
I have a database of information for which I need to be able to calculate the average of the top 20% for each column for all those rows that meet a certain criteria. In other words, I need a formula that's equivalent to the DAVERAGE function but I only want it to work on the top 20% of records that meet the criteria range that I've specified (as opposed to DAVERAGE which works on 100% of records that meet the criteria range). The database continues to grow so I'm unable to predetermine what quantity makes up this top 20%. I've attached a small sample file to show you what I'm talking about.
View Replies!
View Related
Formula To Count Multiple Ifs And Take An Average
Im having a problem finding a formula that will count and take an average of multipul ifs. On sheet 1 of the attached file is multipul queries raised for us to answer. On sheet 2 is an empty table which i would like help formulating. In the Submission Lag Time Column i would like a forumla that looks down Column L on Sheet 1 and gives me an average for the amount of days a query has been with us from each company (column B) per month (going off the dates in column D). I the same formula will be used for the other 3 columns, put looking down a different column on sheet 1.
View Replies!
View Related
Specialized Average Formula Needed
In order to avoid embedding 12 if statements, I want to take an average of a selection of cells based on what month it is. So I have a "now" function, and then a month function referencing the "now" cell so that I get the current month as a number 1 through 12. I have a row of expenses (C14:N14) and I just want the average from January up to the current month. The reason I can't take an average of the entire selection is because the users don't want to have to enter a 0 everytime there's nothing, they'd like to leave it blank, so the sheet needs to recognize what month we're up to. And the average function in Excel only takes an average of the data that is present, not the entire range. For example, if the data in the first cell was 15, second cell was 15, and third cell was blank, the average function returns 15 and not 10. So what I need is, for example, if it's March, the average will calculate =average(C14:E14) and so on. Any ideas? Maybe it's a simple change of formula instead.
View Replies!
View Related
Weighted Average Formula Population
See attached picture. This shows Profit and Growth for the stores in each region. The percentage total is weighted based on the profit of each store in the region. What I have is an excel sheet with hundreds of regions (going down), all with a different amount of stores. I need a way to populate each of its Region % Total with the weighted average formula (in my example, cells C8, C12, and C20) while also varying the formula to include only the stores in the region. For example, C8 calculates the weighted average for %s in rows 2 through 7 (6 stores), but C12 calculates it for rows 9 through 11 (3 stores). I need the formula to vary based on the number of stores.
View Replies!
View Related
Need Assistance With Average Calculation Formula
I get a "#value" error message when I utilize the formula noted below. I seperated it into distinct sections so that it is easier to view. Basically, what the formula is doing is determining whether if two values are the same, then take the absolute difference of the average of other values ,excluding one of the previously noted values, and compare that difference to a different parameter. If the difference does not exceed the parameter, then calculate the difference; otherwise the ending result is zero. Pls. note that the error seems to occur when I input the fifth section into the formula. =IF(K23="","",IF(AND(K23=E23,ABS(B23AVERAGE(F23:J23))>B20),B23ABS(AVERAGE(F23:J23)B20), IF(AND(K23=F23,ABS(B23AVERAGE(E23,G23,H23,I23,J23))>B20),B23ABS(AVERAGE(E23,G23,H23,I23,J23)B20), IF(AND(K23=G23,ABS(B23AVERAGE(E23,F23,H23,I23,J23))>B20),B23ABS(AVERAGE(E23,F23,H23,I23,J23)B20), IF(AND(K23=H23,ABS(B23AVERAGE(E23,F23,G23,I23,J23))>B20),B23ABS(AVERAGE(E23,F23,G23,I23,J23)B20), IF(AND(K23=I23,ABS(B23AVERAGE(E23,F23,G23,H23,J23))>B20),B23ABS(AVERAGE(E23,F23,G23,H23,J23)B20), IF(AND(K23=J23,ABS(B23AVERAGE(E23:I23))>B20),B23ABS(AVERAGE(E23:I23)B20),0))))))))
View Replies!
View Related
Average Formula On Daily Basis
How to go about fixing my spreadsheet so I am not having to manually update it each day..here is my forumla I am currently using...=(AVERAGE($D$2:$AH$2)C5)*AI5...basically i need the cell below in D2 to change as every day a new day rolls off..for example the following day I need this formula to be =(AVERAGE($E$2:$AH$2)C5)*AI5 ....so just that day changes.....do I need to use an If/then statement? if so how?
View Replies!
View Related
Average Formula When Sample Size Changes
I am computing the average of anywhere from 510 numbers. When the numbers are not inserted there is nothing left in the cell. The problem is I get a value that is close to the average but not the real average. (Also the cells I am using are N37:Q41 for the first 5 samples which is constant the next 5 are not always constant W37:Z41) Here is the formula I am using: =IF((W37="")+(W38="")+(W39="")+(W40="")+(W41=""),AVERAGE(N37:Q41),IF((W38="")+(W39="")+(W40="")+(W41=""),AVERAGE(N37:Q41,W37),IF((W39="")+(W40="")+(W41=""),AVERAGE(N37:Q41,W37:Z38),IF((W40="")+(W41=""),AVERAGE(N37:Q41,W37:Z39),IF(W41="",AVERAGE(N37:Q41,W37:Z40),AVERAGE(N37:Q41,W37:Z41)))))) I have the same problem with a standard deviation formula and the same group of numbers (I get something close but a few points off). =IF((W37="")+(W38="")+(W39="")+(W40="")+(W41=""),STDEV(N37:Q41),IF((W38="")+(W39="")+(W40="")+(W41=""),STDEV(N37:Q41,W37),IF((W39="")+(W40="")+(W41=""),STDEV(N37:Q41,W37:Z38),IF((W40="")+(W41=""),STDEV(N37:Q41,W37:Z39),IF(W41="",STDEV(N37:Q41,W37:Z40),STDEV(N37:Q41,W37:Z41))))))
View Replies!
View Related
Formula  Trying To Average A Column That Has Percentages
I have been working on this for days and cannot come up with a answer to this. I am trying to average a column that has percentages. some of the cells have o's in them and the numbers that I am trying to average is a weekly number. So I am trying to average every 7 days percentage. Is there a way to do this? I have looked thru the forum and all over the web and can't come up with an answer. I have excel 2003. Do I have to write a macro for this or can I just do a formula? If so how do I do it?
View Replies!
View Related
If,average,sum Formula, For Rating Chart
I'm trying to get a formula working that is basically for a rating chart. The chart is somewhat simple, but the formula is a bit complex (for me anyway) So the details are the rating is on three axis with three levels, and here is what I need to do. At the moment the formula below works, but it isn't 100% accurate because I actually need to take an average of F5+G5 then take that value and add it to H5 then divide by two to get the proper average of the two charts. (if that makes sense. =IF(AVERAGE(F5:H5)>2.33,"High",IF(AVERAGE(F5:H5)>1.67,"Medium",IF(AVERAGE(F5:H5)<1.67,"Low"))) Here is the formula that I thought would have worked, but Excel begs to differ =IF(SUM((AVERAGE(F5:G5))+H5)/2>2.33,"High",IF(SUM((AVERAGE(F5:G5))+H5)/2>1.67,"Medium",IF(SUM((AVERAGE(F5:G5))+H5)/2<1.67,"Low"))) I played with the brackets quite a bit thinking I had something in the wrong order, but nothing seems to work. I always get FALSE in the field and an error as well when I enter the formula.
View Replies!
View Related
Average Formula With Empty Cells
I have a grade system where I need to obtain an "Average" of grade data within four cells. At time there may onlt be two cells with a value or three. Example: The cell cell cantain a value of 100 points with the totral of 400 point. AS we know the "Average" would be 100 points. But is only two cells cantained 100 point and the other two where empty then the "Average" come back as 50 points. Because I have 4 cells that requires a value input. So my question is how do I create a formula that will give the correct value for the "Average" of data that is placed in the cells. The values are calculated as a total from the grades entered. That total is in F14, F31, F48, F65  Now if F14 has a value of 100, and F31 has a value of 100 then the "Average" should be 100, but it is not for the is calculating F48, and F65 as 4 values, so the return is 50. The situtation is that I need all the cells for in some cases data will be necessary, but I need the "average" to be calcalated for only the values entered. Where is the Formula. =IF(SUM(F14,F31,F48,F65)=0,0,AVERAGE(F14,F31,F48,F65))  My brain says this is simple but no matter what I do it returns 50.
View Replies!
View Related
