Formula - Trying To Average A Column That Has Percentages
Jan 16, 2009
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 9 Replies
ADVERTISEMENT
Jun 24, 2006
I have a row of formulas that calculates information into a percentage. As you input info, a percentage is created in the cell. Those cells that do not yet have information entered still show " DIV/0" or something like that.
I need a formula that will average all the percentages, but the problem is, it's trying to also average the formulas with the percentages, so I currently get an error.
View 9 Replies
View Related
Mar 11, 2013
I'm currently using this formula to calculate the average values in column B where the value in column A ='s E1 eg:
=AVERAGEIF($A$2:$A$21,E1,B2:B21)
Instead of doing this however, I need the formula to calulate the average from column B where the "Date" in column A ='s the year and month I specify in other cells.
Year value specified in: F1
Month value specified in: G1
View 9 Replies
View Related
Jul 2, 2007
Am trying to do a formula to work out a percentage of one figure from another, cannot for the life of me think it through properly!
So in
Cell J73 I have the net income and in cells J75 +J77 I have the total outgoings.
I want a formula that works out the percentage outgoings, so if someone is earning £100 and they have £20 outgoings, that is 20% of their earnings.
View 9 Replies
View Related
Jun 27, 2014
I need a formula to create the following:
In Cell F5 is 26, so the percentage should be 101% (100+1). How can I modify the below to add 1% when the number goes over 100%?
Do I have to create two columns? one for the below and one if the F6 is over 100%?
IF(F6="N/A","N/A",IF(F6>=25,1,IF(F6>=19,0.8,IF(F6>=13,0.6,IF(F6>=7,0.4,IF(F6>=1,0.2,IF(F6
View 5 Replies
View Related
Dec 23, 2013
I've got a list of 20 percentages which can range from 100% to -100%, the total of the 20 percentages should always equals zero but I need excel to give me a total so I can find any calculation errors.
How can I get a total at the bottom of the column?
I've tried SUMIF/SUMIFS, the only way I can get that to work is if I have one cell for >0 and another for
View 5 Replies
View Related
Aug 25, 2014
I use a workbook that records data centered around course referrals.
The workbook also has a stats page. It records Instructor stats but needs to be in the form mostly of percentages.
In Column D within the page Stats I need the cells to show the total number of "YES" that each Instructor has achieved from their number of pupils in the Sheet Data.
Column E and F in Stats need to show the percentage of Yes and No of each Instructor from Column D in the Sheet Data.
Column G In Stats needs to show the number in total of Referrals made by Indivisual Instructors taken from Sheet Data.
Columns H,I and J in Stats need to show the percentage of referrals to OBT,Kaya and HWal made by the Individual Instructors taken from Sheet Data.
Finally in Stats, Columns K,L and M need to show the percentages for each Instructor of their Leave Reason again Taken from Sheet Data.
Usually there are several hundred entries and at the moment I do this manually with a calculator.
View 2 Replies
View Related
Sep 24, 2006
I need a cell formula that will (a) identify the highest N values in an above specified column range, (b) color the interior of those N cells (I suspect that this is not possible), and most importantly (c) return the average value of N corresponding cells, where the corresponding cells are located on the same rows as the identified N high value cells but in a specified column to the left (not necessarily adjacent)
Does anybody know what this formula would look like?
Example:
-----------------------
...| A | B | C | D | E | F |
-------------------------
1 |....| * |.........| 7 |...
-------------------------
2 |....................| 2 |...
-------------------------
3 |....| * |.........| 6 |...
-------------------------
4 |....................| 1 |...
-------------------------
5 |....| * |.........| 5 |...
-------------------------
6 |....................| ? |...
-------------------------
? = average of B1,B3,B5 where (N = 3) and (specified column to the left = B)
View 4 Replies
View Related
Jan 25, 2010
The entries are made in Columns A thru C. In Columns F and G I want any value over 100% to have a red background, any value less than 100% to have regular formatting, and I'd want percentages to have white font if an employee is deleted from Column A.
Here's an example of how I have the formatting set up:
Condition 1: Formula is =$E$2="" --->white font
Condition 2: Cell value is >1 ---->red background
Condition 3 Cell value is </= 1 ---->normal format
View 3 Replies
View Related
Jun 14, 2013
E11 through E24 contains numbers and a few errors (#N/A) that need to persist (the errors need to show).
E10 needs to show the average of the numbers that are in E11 through E24, and just ignore the errors.
I have many columns like that - where the errors need to show and I need to show an average of the number/values that do appear, ignoring the errors.
View 14 Replies
View Related
Jan 7, 2008
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 9 Replies
View Related
Oct 2, 2008
I'm trying to figure out if there is a formula I could use that will calculate the average of a group cells in one column based on the condition of another column. It's hard to explain, so I will show an example. All the data is on a one worksheet and I'm trying to show totals and averages on another worksheet. Location, Days
17, 4
17, 3
17, 5
26, 4
26, 8
26, 10
26, 7
On a different worksheet I would want to know what the average days are for each location. So is there a formula that I could use that will look at column A for a specified location number and then average all the days in column B for that location? I'm using Excel 2003 and have tried using the Average(if) but with no success.
View 2 Replies
View Related
May 3, 2013
I have a pivot that includes customer data and I need to create an average for each row based on the first populated column for each customer. After one month of units appears for the customer, I need to calculate the average going forward, and if there are blanks after that they should be treated as 0 in the average. Below is an example of my description of the pivot.
Jan Feb Mar Apr
Customer #1 1 1 3
Customer #2 1 3
Customer #3 2 2
So, the average for customer #1 would be calculated from Column B to Column E, and factor column D as a 0 in that calculation. Customer 2's average would start in Column C and go to Column E, and factor column E as a 0 in that calculation. Customer 3's average would start in column D to column E.
View 5 Replies
View Related
Jan 13, 2008
I am trying to get an average from one column based upon criteria from an adjacent column. The number of days to close a case for race columns Black and White are listed in B5:C16 and E5:F16 and H5:I16. I need a formula to calculate the average days taken to close cases for Males and then the same for Females. Sample below: ...
View 12 Replies
View Related
May 17, 2009
I have tried a few other examples in previous threads that use COUNTIF and ISERROR but I just can't get it right.
Basically I want to average the cells in column J (J2:J6,J8:J12,J14:J18, etc).
My aim here is to determine the average value for each day (Averaging 0 values in this instance is OK).
Now, the date in column A (A2:A6,A8:A12,A14:A18, etc) sometimes returns a blank value, depending on the day of the week (see attached example). This is because, in the full version of the workbook, I have Column A date linked to a perpetual calendar and therefore the dates can change cells depending on the year.
For the Date Cells that return a blank value, I do not want the AVERAGE equation to include the 0 value on these days.
For an example I have attached a month of February where there are blank date cells at either end of the column.
View 13 Replies
View Related
Dec 14, 2006
I am trying to calculate averages for a ratio I have calculated. Is there a way I can use VBA or Excel functions to average the G column depending on the value of the D column? In other words, find the average value of column G for all Industry Group = 103. Then, find the average value of column G for all Industry Group = 106. Then put industry group # in column H and avg. value in column I.
View 2 Replies
View Related
Oct 21, 2008
Is there a way to average every other row in excel?
View 7 Replies
View Related
Nov 7, 2007
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 10 Replies
View Related
May 22, 2014
I have a column with values from P2 to P3077. I need a formula that would average values from P2:P6, P7:P11, P12:P16 and so on till the end (that is 5 data points each time till the end).
View 2 Replies
View Related
Nov 25, 2008
I do not use excel very often but know there is a simple formula to take the average of numbers inputted into a column. For example, I would like to sum all the values entered into column “D” and then divide that by the number of entries
4
5
3
1
4
11
6
2
0
----
36/9 = 4
If I were to add another entry it would automatically change the divisor to 10
View 7 Replies
View Related
Dec 24, 2008
I want to get the average of various non-contigous columns, ex:
Column B G X Z
10 0 6 8
However, I need to exclude any zero values. In this case the correct average is 8. I have used various sumif's and Average(IF) functions without success.
View 5 Replies
View Related
Jul 27, 2009
Attached Dummy file. I would like to be able to get the average time spent on a process for the day recorded on the "Results" tab. I have posted the answers it should be on the Results tab, which was done manually. I would like the file to do it on it's own. On the individual's tab, the time and instances will be recorded for each job. I believe I am close, and the only thing I need is the Results page to be able to return the desired results. Ranges varies day-to-day, and I think (maybe), I can figure out the dynamic range settings.
I would like to avoid creating a helper column for each set, because then I would be dependent on the person expanding on this. I am also avoiding Pivot Tables, I understand it, but not the ones that needs it. This will have about 15 tabs for employees, and could be possible that more or less will happen, depending on workload. This will continue to be manual for now.
View 5 Replies
View Related
May 10, 2006
I need to get the average of every 6th cell in a column, excluding errors --- three times.
The first average should be every 6th cell beginning with z4, the second every 6th cell beginning with z6, and the third every 6th cell beginning with z8.
Is there an easy way to achieve these 3 averages?
View 9 Replies
View Related
Oct 10, 2006
I have information I'm trying to evaluate. Average Close time. but I need to evaluate it for each month. Forinstance if my average close time was B. and the close date was c. I would need the average(b:b) but only from fields that had a close date of July in column C. is this possible? I hate to have to break up the data set 12 times. (i'm reporting individually for each month of the year so we can see trends.)
View 12 Replies
View Related
Jul 19, 2012
I want to be able to find the last non empty row in column C and then average the last three inputs. Thus utilzing this as a means for a projection of the remaining periods left in the year. All for projecting figures for the remainder of the year.
Bill
Period
Date
Amount
1
1/6/2012
3982.76
[Code] ..........
View 2 Replies
View Related
Nov 7, 2006
In sheet 2 b2, I want to calculate the average of the values in the last column of sheet 1. So if the last column is D then all the figures in D are added up and an average given. But if the last column is M then it gives me the average of that column.
View 9 Replies
View Related
Jan 11, 2008
How to sum or average the data in the even column ( I mean column 2, 4, 6, etc) in a range (like range A1:DD1)? I tried to use Mod and Iseven to get it but failed.
View 9 Replies
View Related
Jul 7, 2009
I'm using Excel 2003, so no AverageIf.
I have 15,000 rows of data.
In Worksheet_1, I have:
Column A (Date) contains daily dates ranging from January 1968 to today, though not every day is listed (weekends and holidays are excluded).
Column E (Price) contains daily prices.
On a separate worksheet (Worksheet_2), I need to calculate average monthly prices, listed by end of month dates (Monthly_Date and Monthly_Price, respectively).
I've been trying to use an array formula like this:
=AVERAGE(IF(AND(MONTH('Worksheet_1'!A2)=MONTH('Worksheet_2'!A2),YEAR('Worksheet_1'!A2)=YEAR('Worksheet_2'!A2)),'Workshee t_1'!E:E,"0"))
I can get an average of the whole column, but I cannot seem to make Excel "grab" only the corresponding data in Column E that matches the right date criteria (in this case, when the month=January and the year=1968).
View 9 Replies
View Related
Jul 8, 2006
I am trying to average times from one column that is the difference of two other columns (generally just minutes). It is formatted in a military time format.
However, I do have criteria it must meet from another column.
Ex:
Col A Col B Col C Col D
1 23:57 00:07 00:10 "=C2-B2+IF(B2>C2,1) fx I am using"
2 03:15 03:20 00:05
1 10:30 10:42 00:12
That's the basics, now I want to average Col D but only if it is a "1" in Col A. There are some empty cells, being its formatted for 100 entries. I can get an average of Col D and a count of Col A, but I can't combine them.
View 7 Replies
View Related
Aug 21, 2006
I have a colomn with numers and text. I would like to get an average of the numbers in the column, but using average (A1:A10) gives me #N/A as some of the cells contain #N/A.
So wondering if there is an AverageIF function in excel that I can use to write something which only averages IF it is a number.
Sounds very basic but I could not find anything through search.
View 9 Replies
View Related
