Function To Calculate Averages Of Percentages
Jan 29, 2010
I am wondering if there is a function in excel to calculate the average of multiple percentages. Currently I rely on the following formula to achieve this result and I am curious if there is a quicker way:
Try this formula. .....
Just today I learned there is a function to calculate the compounded return (FVSCHEDULE). It does part of what I am looking to do by taking away the PRODUCT function and the array, but does not really simplify things. Using FVSCHEDULE my function would look like ....
View 9 Replies
ADVERTISEMENT
Nov 5, 2013
=IF(A2-B2,(B2/A2))
The simple formula above calculates percentage which basically in this example, 159-90 divded 159% which equals 56.60%. What i need the formula to recognize is if both the entries are 159 which should show 100%. Its to show error rates by the way. Percentage wise.
View 3 Replies
View Related
Apr 12, 2012
I have two issues:
1, How do I calculate the original value of something if it has dropped say 9.3%
Ex Stock value 5.56 down 7.4%. How do I calculate its original value.
2, I have copied some numerical data from a website, on one computer when I drop that data into an excel sheet it falls into separate cells allowing me to manipulate it, on another computer I drop it into an excel spreadsheet and all the data goes into one cell not allowing me to manipulate it.
I was wondering is there a setting on one excel spreadsheet that I am inadvertantly not using on the other.
View 4 Replies
View Related
Sep 8, 2007
I wish to write an "IF" formular to express a row of numbers in the way I wish to display them in accumulative fashion. 1 upto and including 9 = 1% ...
View 9 Replies
View Related
Oct 6, 2011
I have 3 columns (B,D,F) that have 15 minute data intervals and I need to calculate the hourly averages for each column but here is the catch, I need to do this from July-1-2011 00:00:00 to Sept-31-2011, 24:00:00 (that's 26,217 data points and 6,554 =average() calculations......
IS there syntax I could use that would calculate the average every 4-cells so I could get the hourly average without having to type =average(XX:XX) 6,554 times?
Example of set-up:
29-Sep-11
23:00:00
0.820412164= AVERAGE(B8735:B8738)
0.762574274 =average(D8735:D8738)
84.98389991 =average(F8735:F8738)
29-Sep-11 23:15:00 0.819168591
0.762781167
[Code]...
View 6 Replies
View Related
Feb 20, 2008
Situation: I need to calculate a lot of averages of non-connected values. That is, they are separated by several rows (which also contain values). So far, I use the code-sample below (just to illustrate), which is not really elegant. Unfortunately, it seems that this technique comes to its limits when a high number of different non-connected values are combined into one string and get assigned to a formula- range (last line of code), which is where the program stops.
My question is: Is there a nice way to solve this? A way, which is not limited by the amount of data processed? The only way I could think of, would include a UDF, where a sum, sums all values up and then devides by the number of added sums ... but this would be relatively slow, and I have an awful lot of values to be averaged (100 sets * 6 rows * 200 values = 120.000 calculation- steps), as well as STDEV and a Standard Error of Mean (SEM) to calculate. Is there a better alternative?
Const intCells=9 'actually, this is no constant but a variable
Const i=4 'actually, this is no constant, but a nested for- loop-value
Const intLinesInBetween = 12 ' amount of lines, which separate my values
Dim j As Integer 'simple counter, runs through all cells
Dim rngUpperRow As Range 'Range of the upper row = normalized time vector
Dim rngCurrentRowAvg As Range 'current row into which avg formulas are inserted
Dim intCells As Integer ' amount of experiments in range............
View 4 Replies
View Related
Dec 31, 2008
On another thread I found a solution to creating a weighted average given a pair of columns of numbers but I have column B consisting of values given as High, Medium and Low and the weighted column C of 1, 2 or 3.
How can I use the formula below to accomplish this, or is this maybe not the right way to created a weighted average?
=SUMPRODUCT(B2:B11,C2:C11)
View 7 Replies
View Related
Aug 21, 2009
Code:.....
The names are not static and can be moved, meaning that John can be 1, 2, 3, or 4 (all depending on how the name is chosen). I want to find the average on where John ranks. In this instance, he would be 2 [(1+3+2)/3].
My question is - How can I create a formula that will look for where John appears in the column (B1) and call the previous cell (A1)? This formula needs to be repeated on each column, added and averaged.
View 5 Replies
View Related
Dec 22, 2011
I have a first column, which contains random integers typically with gaps of between 10 to 50 blank cells. The data may have up to 5,000 rows in total.
In the next column along I would like a calculation which averages the integer in the first column out across all rows until another integer is reached in the first column.
E.g. In the first column, the first number is 60, then 9 blank cells till the next entry. In the second column the first 10 cells each contain the number 6 ( = 60/10).
View 1 Replies
View Related
Jan 5, 2012
I have a large sheet of daily river flows and precipitation amounts, over 40+ years.
I want to calculate the average flows in each month of each year (ie 11/1968, 12/1968, 1/1969 etc). Dates are in Col A, flows in Col D.
How to average a particular month over all years like so:
{=AVERAGE(IF(MONTH(A2:A15282)=1,D2:D15282))}
But I don't know how to average per month of each year, or how to make a formula that will allow me to quickly calculate the averages of 100s of months.
View 6 Replies
View Related
Mar 19, 2013
I am trying to calculate final grades with weighted averages. However for the final grade I need to drop the lowest of the test grade which come after the bold line to the right, making the total relative weight of Tests, the weight of 2 tests. How would I go about calculating that with a SumProduct formula? I do not need to drop grades from B4:K4, however from L4:N4 I need to drop the lowest grade for my final grade, which needs to be rounded to the nearest integer. The relative weight of each assignment is given in Row 1.
View 3 Replies
View Related
Mar 20, 2008
I need to do the following and can't figure it out. How do I create a running average that will only calculate the averages in % each month. Example: Opt 1 for Jan, Feb, Mar =1 each= 3 total = 100%; OPt 2 for Jan, Feb, Mar =1,0,1= 2 = 66%; Opt 3 for Jan, Feb, Mar = 0, 0, 1 = 1 total = 33%. My problems is I want monthly running average that shows the yearly percentage up to date but only for the months there is a value 1 or 0. How can this be done because the way I have it now, those % are being divided by 12 and that isn't the correct %
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 30, 2008
My code below comes up with #VALUE! error msg
Function PIRR(NumPer As Single, StartVal As Double, EachVal As Double)
Dim Values() As Double
Values(0) = StartVal
For i = 1 To NumPer
Values(i) = EachVal
Next
PIRR = Application.WorksheetFunction.IRR(Values())
End Function
View 9 Replies
View Related
Nov 10, 2013
I have a workbook that calculates a RAND function based on input in another cell. Specifically, the formula in E2 selects a random text string from another range depending on data entered in D2. I want to be able to fill this formula down for use multiple times.
The formula works fine on its own, but the problem comes when I, say, move on to D3 to input more data. E3 does its random calculation as it should, but E2 also recalculates; I want E2 to remain static once it calculates the first time.
I'm attaching the workbook in question. The calculation I'm referring to is in the 3rd worksheet.
TLL Injury Machine.xlsx
View 2 Replies
View Related
Jul 24, 2006
I have a spreadsheet that keeps attendance. Now I need to calculate the
units (1 unit= 15 minutes) the children are actual here. On a normal
schedule they are here for 16 units but if the are late the units get
subtracted. When entering the data, they would like to use letter instead of
numbers.
View 9 Replies
View Related
Nov 18, 2008
I am trying to calculate payroll. I have 2 columns where regular hours and overtime hours are manually inputed. They are then multiplied by their respective pay per hour columns to come up with regular and overtime pay. The next column adds these to get total gross pay. That gross pay is then multiplied by the FICA and FICA Med factors to figure those taxes(2 different columns). I then have a column the adds all deductions to get total deductions(Fed,FICA,FICAMed,St). The last column subtracts total deductions from the gross pay column to get net income. My problem is the net pay column is $.01 off sometimes.
I think what is happening is I obviously have all columns in dollar amounts with 2 decimal points. Some function columns have multiple decimal points in the answer and then are only showing the 2 decimal points. When those columns are used in the next equation, instead of using the dollar amount that is showing with 2 decimal points, it is remembering the multiple decimal points. This is resulting in being a penny off when I get to the end. How can I get the equations to use what actually shows in the columns(2 decimal points) instead of remembering multiple decimal places?
View 2 Replies
View Related
Jan 15, 2008
I have written the code below. But it didn't work. In my opinion the error is most probably caused by irr worksheet function. Because the code is actually bigger and I tried it all part by part.
Option Base 1
Dim i As Integer
Dim j As Integer
Dim counter As Integer
Sub General() 'Makes general calculations
Dim Cash_Flow_Project_Entity(49) As Variant 'Toplam Yatýrým Nakit Akýmý
Dim FIRR(49) As Variant 'Yearly FIRR
Analysis_Period = Worksheets("Veri"). Cells(15, 5) 'Takes " analysis period" from "Veri" Worksheet
End Sub
View 9 Replies
View Related
Jul 6, 2014
I am making an excel sheet which can track the hypothetical effect building an order has on current inventory. The user is to change the quantities in this hypothetical order whenever they want.
It is composed of two sheets, the inventory sheet and the model sheet. The inventory sheet has the costs of every part carried and how many are currently in stock. The model sheet has a breakdown of what parts are required to build the different models.
I am looking for a function or VBA which can take a hypothetical order (build xx of model one + build xx of model two + build xx of model three), see how many total parts are needed, then calculate and display the effect it has on current inventory. This is only to see the hypothetical effect on inventory, not to actually change the inventory in the inventory sheet.
I've attached what I have so far - most everything is completed except for this final calculation. I have tried using the VLOOKUP function in VBA to try and come up with a solution but I couldn't figure out the best way to go about this. I think the hardest part might be that a hypothetical order can be made up of all three models.
View 9 Replies
View Related
Jul 21, 2014
I'm trying to calculate the mode for a large data set, but there is no 'mode function' in subtotal. I need to find the mode for each change in day - without having to retype the function.
View 11 Replies
View Related
Oct 27, 2009
I'm writing a spreadsheet where I need to calculate a nested IF function (I think), and I am unsure of how to do it.
The problem is this:
The total $ value of the quote is entered, then depending on two variables, a rebate may be deducted from this total value. The first variable is eligibility (a Y/N response), the second is the type of project - there are two types of project and each attract a different rebate amount.
So I have:
A: total quote $
B: Eligibility - 'Y' or 'N' (if 'Y', then value of 'C' is deducted from 'A'), (if 'N', then no value is deducted from 'A')
C: Type - '1' or '2' (if '1', then 'C' = $1000), (if '2', then 'C' = $1600)
D: final result
View 2 Replies
View Related
Jul 9, 2012
I'm struggling with the VBA DATEDIFF function. I have a person's DOB and am attempting to determine their age by comparing to a different date. It seems like their age only changes when I change the year, rather than choose a date within the year past their birth date... Here is my code:
Sub Test_click()
DOB1 = #7/19/1930# 'DOB
date2 = #7/18/2012#
Date3 = #7/21/2012#
age1 = DateDiff("yyyy", DOB1, date2) 'age should be 81 (but is 82)
age2 = DateDiff("yyyy", DOB1, date2) 'age is 82 (correct)
End Sub
View 5 Replies
View Related
Feb 27, 2007
I have a matrix w/ alpha characters in a 10x10 grid. I want to see how many times each letter shows up and then rank them asceding.
What is the function to do this? I looked at Frequency and Count, but do not think either will do the required work.
View 9 Replies
View Related
Aug 28, 2008
I have just built a spreadsheet to calculate training hours amongst other things. I have used the function NETWORKDAYS which calculates the number of working days between two dates. This works fine on my laptop which has the analysis tools pack installed. Unfortunately my work place IS policy wont allow the analysis pack to be installed so need to find away around this. Does anyone know a formula that will perform the same function as the NETWORKDAYS. THis is crucial to the accurate calculation of training hours.
View 9 Replies
View Related
Nov 28, 2009
I have a dataset of students' grades (see sample below) which I need to compute the aggregates for.
Blanks indicate that the student did not take the subject. Aggregate is computed based on 6 relevant subjects, lowest is best.
Rules for computing aggregates:
1st subject: EL or HMT
2nd subject: H1 or H2
3rd subject: M1, M2, S1 or S2
4th subject: H1, H2, M1, M2, S1 or S2 (but not previously used)
5th & 6th subjects: Any other 2 not previously used
I'm guessing this is too complicated to be done in a function, but have no idea how to implement this in VBA.
View 9 Replies
View Related
Mar 25, 2014
I currently have in my equation the trend function to linear extrapolate the date a line would cross either my upper or lower limits (100.1 and 19.9) between 2 data points. However if the 2 data points span both the upper and lower limits (as per the 2 attached examples) then unfortunately the trend function is only able to return the 1 date (where I've specified the x value). Is there any way for the TREND function to trend the 2 values? Do I simply add the 2nd x value to the new x's?
I have attached both the data and their graphs to visually explain my problem : EF Workbook.xlsx
View 2 Replies
View Related
Jun 15, 2014
If the data looks like this:
10
7
6
10
9
9
3
What would the formula be to calculate that array of values = 57% because 4 of them are equal to or greater than 9, and there are 7 of them in total? The formula should accommodate however many numbers are listed and calculate what percentage of them are 9 or 10.
View 5 Replies
View Related
Nov 12, 2008
I am using a formula to calculate the last day of the month, using any date of the month in a worksheet in cell A13, this cell is also linked to another worksheet to pick up a date, using the ISBLANK function to prevent a dummy date entry appearing if the field in the linked ASHBY RISE worksheet is blank
=IF(ISBLANK('ASHBY RISE'!$C$5),"",'ASHBY RISE'!$C$5)
The last day of the month function is shown below
=DATE(YEAR(A13),MONTH(A13)+1,0)
This works fine if there is a date in A13, but returns a #VALUE! error if cell A13 is blank. I have tried using the ISBLANK function, but I am still getting the #VALUE! error. Of course I may have the sysntax incorrect.
View 4 Replies
View Related
May 15, 2008
I want to obtain from some elements the number of cells it takes to appear:
We have for example A,B,C, and D,
and they appear in the next order:
1A
2C
3D
4A
5D
6B
7C
8A
9A
What I want to know is how much last in appear each element.
1A1
2C2
3D3
4A3
5D2
6B6
7C5
8A4
9A1
For example, the first “A” last one in appear, but the next element “C” last two in appear. In the forth line again cames the “A”, then are three cells. The “C” was in the cell2, and cames again in the seventh cell, then it takes five cells. In the cells eight and nine are two “A”, then in the cell nine takes one cell in appear again.
View 10 Replies
View Related
Jun 11, 2009
As everyone realizes that WORKDAY function can return a working date that exclude weekends and any dates identified as holidays. However, what if I want to return a working date excluding my designated holidays but including weekends?
For example,
Holidays are 1 Jun 2009, 2 Jun 2009 and 4 Jun 2009
Start Date: ???
Finish Date: 8 Jun 2009
Duration: 5 days
The Start Date should be 30 May 2009.
Seems like I am not able to use WORKDAY function to calculate the start date.
View 11 Replies
View Related
