Formula: Calculating Averages To Reach A Goal
Aug 27, 2007
Below is my data point for each month. The goal I need to hit is 99%. So I need to figure out what minimum monthly percentage I need for the rest of the year, I will need to reach a goal of 99%, and if I can't reach it, return an error. Lastly, i want to be able next month to go in and fill in the AUG percentage with an absolute number (i.e. 89%) and then I would like the rest of the percentages to automatically update by figuring out the new minimum monthly average given the new value for August. I thought that I might be able to do that if there is a function that says "If cell is a number, leave it alone, if it's a formula, then include that cell in the calculation of the minimum monthly average.
Jan 89%
Feb 88%
Mar 83%
Apr 89%
May 90%
Jun 86%
Jul 82%
Aug
Sep
Oct
Nov
Dec
Goal 99%
View 9 Replies
ADVERTISEMENT
May 21, 2009
I want to save one million dollars in 10 years earning 8.00% interest. How do I calculate how much I need to save per month.
View 11 Replies
View Related
Feb 19, 2008
I'm trying to Calculte the Averages of just the Teachers Salaries in my spreadsheet. If possible i would like to put the average at the end of each
of Teachers information.
Is there code that would allow me to do so.
I attached a sample file to view.
View 9 Replies
View Related
Jun 7, 2013
I have a spreadsheet that we are using as a Skills Matrix for the team. The area that the team member deals with is listed in Row 3. The scores for each person are recorded in Columns D to M. The process steps being scored against are in Rows 8 to 38, and are seperated by the area of responsibility.
I am trying to work out the average scores of each person depending on their area of responsibility. I can work out the totals easily enough using:
=IF(ISNUMBER(FIND("CM",D3)),SUM(D11,D12,D13,D14,D18,D19,D20,D23,D24,D25,D26,D27,D28,D29,D30),0)
+IF(ISNUMBER(FIND("V",D3)),SUM(D33,D34,D35,D38),0)
But if I use the same for the average scores is works fine until a person covers two duties:
=IF(ISNUMBER(FIND("CM",D3)),AVERAGE(D8:D10,D12:D17,D19:D22,D24:D30),0)
+IF(ISNUMBER(FIND("V",D3)),AVERAGE(D31:D32,D34:D37),0)
This is becuase it adds the 2 averages instead of working out the total average score. Is there an easy way around this?
i.e. For team members who have CM in Row 3 I need an average of certain cells, for team members who have V in Row 3 I need a different average and for those team members with both CM and V I need an overall average.
View 1 Replies
View Related
Oct 1, 2013
I have a database with monthly tabs and a summary sheet, on the summary sheet I calculate an average length of stay for each month (tab) but I need to find the yearly average and I don't know if there is a formula that would calculate the true average by using the monthly averages.
View 2 Replies
View Related
Aug 9, 2006
The data was taken in 15 min intervals and is organized by date. I have one column with the date and time and another column with the data. I need to find the average for each day. I have almost a years worth of data. Is there any formula I can enter to find the values in a given day and return the average of the values (without having to select the data for each day)? I want to be able to copy the formula down a column with the value per day.
View 4 Replies
View Related
May 2, 2014
I need vba macro code or excel function to calculate average for some intervals in my excel sheet which have more than one value... I need all those values to be get averaged and placed in one cell...
I have intervals like 0-2, 2-4, 4-6.... 22-24.
In these intervals, few have got more than one values like
16-1804/11/201416:29:2616503.9902
04/11/201417:52:4917390.0293
18-2004/11/201419:52:5517204.8086
Now I need 16503.9902 and 17390.0293 need to get averaged and placed in one cell like,
16-1804/11/201416:29:2616947.00975
18-2004/11/201419:52:5517204.8086
View 14 Replies
View Related
May 10, 2006
I want to be able to keep an average grade for my students through the course of a year. Work is marked with a grade A - U. I can use VLOOKUP to convert this to a numerical value but I can't work out how to generate an average from these numbers because every week there will be a new unit of work marked.
On the sample spreadsheet I have attached, the problem I have had is trying to get an average of the numerical values in row 7 because the cells without numbers have a VLOOKUP formula in them and mess up the calculation. And as the range of numbers will increase each time I mark a piece of work I can't use a fixed formula to find the average from say 10 numbers...
View 3 Replies
View Related
Jan 20, 2014
generating a formula that takes the weekly values in a quarter and uses them to generate a quarterly average. Quarters are split up as follows.
Q#1: 09-11 (First week of September-last week of November)
(Used to generate price for January)
Q#2: 12-02 (First week-Last week)
Q#3: 03-05 (First week-Last week)
Q#4: 06-08 (First week-Last week)
I've attached an excel sheet with some dummy data.
View 3 Replies
View Related
Apr 28, 2008
I am currently using the following formula to calculate the average weekly demand of a product:
=SUM($G8:$CF8)/COUNTIF($G8:$CF8,">=0"))
It works fine, but sometimes the data I'm analysing doesn't span the full horizon (78 weeks). What I therefore want, is for the COUNTIF part to recognise the last populated Cell in the array, and to use the length of the array up to that point to calculate the average. Put another way, if I only have 52 weeks of data (the last populated Cell is $BF8), I want the COUNTIF to calculate based on 52 weeks and not 78 weeks.
View 9 Replies
View Related
Jan 19, 2007
I have a sheet with daily data starting from 01/01/2000. I want to calculate daily averages for each quarter (i.e 2000Q1 value will be the average of values between 01/01/2000-31/03/2000, 2000Q2 will be average of values between 01/04/2000-31/06/2000, 2000Q3 average(01/07/2000-31/09/2000) and 2000Q4 will be the average of (01/10/2000-31/12/2000) etc. for all years afterwards.
I want to have the values in the corresponding cells starting with range ("e2")
View 6 Replies
View Related
Sep 28, 2006
CSS = P divided by S.
PS Required is a number that needs to be added to both S and P to achieve target of 83%
The answer to the below = 5. But i dont know how to get it using a formula
(If 5 is added to S and P both, S will become 36 and P will become 30; 30/36 = 83.33%)
Name S P CSS Target PS required
Adam 31 25 80.65% 83.00% ?
View 9 Replies
View Related
Jun 4, 2014
Say employee John Doe has a salary X. Giving him a salary raise Y (euro/dollar) induces a pension premium to be paid by the employer, on that amount.
The premium is (fixed) 17% of the part above a (fixed) treshold value of 3.750. On the part of the raise below the 3.750, the pension premium is (fixed) 4,35%.
E.g. having a salary of 3.600, with a raise of 400.
4,35% of (3.750 - 3.600 = 150) equals 6,525
And 17% of the remaining 250 equals 42,500
So the total premium is 49,025
But, I want to give a raise with a total cost of no more than 400. So, I use goal seek to find a raise that, together with te pension premiums equals 400.
In the example, with 3.600 as a salary to start with, that raise would be 358,10 Because the part below 3.750 stays the same and induces a premium of 4,35% of 150, being 6,525 and the remaining part is 208,10 at 17% is 35,377.
Proof: 358,10 + 6,525 + 35,377 = 400
The question is: is there a possibility to have a formula that calculates the (e.g.) 358,10 based on a salary to start with (e.g. 3.600), an aimed total cost (e.g. 400) and the fixed constants 3.750, 17% and 4,35%?
View 3 Replies
View Related
Mar 1, 2007
In the following cells I have an average:
B6,J6,R6,Z6, and AH6 and a total average on cell AP6
How would I go about making a formula for that. The reason why I ask is because all the info I have seen refers to the cells with the averages need to be in sequence or range and nothing about the cells being seperated. I also tried using the countif method, but same prob, the cells need to be within a horizontal or vertical range
View 9 Replies
View Related
Jan 1, 2007
I need is a formula that averages totals from different months out of the year. I already have a yearly average. That was easy. But what I need is a 3 & 6 month average.
I also need it to be most current, so when I am in September, it will take the 3 previous months and average them and same with the 6 month. Then when I move into October, it would take its 3 previous months, i.e. - July,August, September.
View 9 Replies
View Related
Nov 3, 2008
I am working on a spreadsheet which has a column which shows square footages (Column AE) and I also have a column which shows costs (Column Y).
What I would like to do is to bracket these footages in to 6 bands (0 - 5000, 5000 - 10000, 10000 - 15000, 15000 - 20000, 20000 - 25000, 25000+) and then average the costs within a particular band.
View 9 Replies
View Related
Jun 3, 2014
I want to know if it's possible to calculate the probability that a certain range of values, reach to a certain point.....
The range is this (example):
4
3
9
7
14
22
15
20
42
46.....
What I want to know is if this tendency will reach to 1000. Or how many steps more will be need to reach 1000.
Like I said, and don't know if this is in the right place, but if you put this values in a graphic you will see that the tendency is growing. I assume that this tendency will reach to 1000. But will be in the next 10 steps or in the next 30.
View 3 Replies
View Related
Apr 21, 2009
Is there a way that Excel can automatically enter to next row once the typing has reach the end.
Example: I have 5 columns (A to E) and I am typing at column A. While typing, the text will go along to cell B, C, D and E. The problem is, if I don't manually go to next row and continue typing, the text will go to column F, G and so on. Is there a way where excel can automatically jump to next row if the text has reach column E?
Another problem is, say I have 3 rows full of text (column A to E). If I edit one of the row to exter some new texts, the whole sentence will go along to column F, G and so on. What I can do now is, re-edit all the rows to adjust them back.
View 9 Replies
View Related
Dec 7, 2007
I have a target to achieve every month in my department for the number of items completed named BC.I am trying to create a formula so that I know how many minimum items I need to complete every day in order to achieve this target by month end. The target to complete each month is calculated with multiple variables and therefore I am not sure how create a formula to calculate the minimum item to be completed everyday to achieve the target by month end. I have attached the spreadheet and appreciate help. The target to achieve is named "Target BC" in red.
In addition, at the beginning of the month, we only have forecasted numbers and these numbers needs to be replaced by actual numbers everyday.
View 9 Replies
View Related
Dec 11, 2013
I've put together a spreadsheet look at the time taken for a vehicle to get from one building to another when called. I've set up conditional formats where the timings turn orange after 5 minutes and red after 10 minutes taken. What I also have to show though is who is taking the vehicle and break it down to show any delays to patients. Is there a formula that will automatically tell me when there has been a delay to patients?? Be amazing if there is as it would save me uber amounts of time as i have to collate a whole years worth of data!
I've attached a small sample of what I've done to show you how the spreadsheet looks.
sample.xlsx
View 5 Replies
View Related
Mar 24, 2009
I have a UPC list. Some are more than 12 digits, and some with less than 12 digits. I need to make sure there are 12 digits in each UPC. I know how to count using LEN, strip leading zeros of those UPCs that are >12 digits using RIGHT.
What I need now is any number with less than 12 digits, such as 000123, add a 4 to the beginning (4000123) and fill in '0's in between the 4 and the short UPC number to make 12 digits, 400000000123. They vary from 1 to 13 digits.
View 4 Replies
View Related
Jan 14, 2006
function that add to any cell that doesn't reach the disired amount.
If the required number is 14 and a calculated cell comes up with 13 or less; I need a funtion to take that number and add a penalty number to it.
For example: For arguements sake the penalty is $150.00. Suppose cell 1A has 5 and cell 1B has 5; cell 1C adds them for a total of 10. I need cell 1D to realize that the number in 1C did not equate to atleast 14 and therfore 1D should show $150.00 and for every cell selected that falls short of 14 should add an additional $150.00 to 1D.
View 9 Replies
View Related
Oct 28, 2005
I am looking for a Formula that can process the following:
The Sum Target Value is variable e.g.; 147
I have two columns of numerical values: Column “A” and Column “B.”
Column “A” Houses the Numerical Labels that I wish to have Returned when the
Sum Target Value is processed /reached - Subtract Sum Target Value as noted
below.
To Subtract Sum Target Value:
Start from LAST non-zero numerical value in Column “B” and Subtract one Cell
value at a time (or Sum up the Column) until the Sum Target Value or nearest
possible Sum BELOW, the Sum Target Value is reached. In this instance, it is
147. I wish to Sum the values in Column “B” to 147 per the above. The
summed values can be below BUT NOT over the Sum Target Value.
Return the Numerical Value that is Offset ONE Cell to the LEFT (Column “A”)
and ONE Row Above LAST value Subtracted (in Column “B”) to reach Sum Target
Value. The Result – Numerical Label should come from Column “A.”
Col “A” Col “B”
2003
2051
2105
21511
22010
22515
23016
23510
24011
24529
25020
Reaching the Sum Target Value of 147 in Column “B” would go up to value 26,
Label 295 in Column “A”, totalling 131 which is below the Sum Target 147 but
including the value of 31 above it, would exceed the Sum Target Value of 147.
The required Result is returned from Column “A” Label 290 which is ONE Cell
to the LEFT (Column “A”) and ONE Row Above LAST value Subtracted (in Column
“B”).
View 14 Replies
View Related
May 30, 2009
Trying to automate Goal Seek using variables for the three arguments. It's working when I define the cells references & values within the code, but cannot figure out how using variables. I'm sure the example below is incorrect, but will give an idea what I need to accomplish:
GoalCell = Worksheets("Parts").Cells(DestRowID + 10, DestColID)
Value = Worksheets("ICA").Cells(CopyRowID + 13, CopyColID).Value
ChangeCell= Worksheets("Parts").Cells(DestRowID + 9, DestColID)
GoalCell.GoalSeek Goal:=Value, ChangingCell:=ChangeCell
View 9 Replies
View Related
Aug 15, 2008
I need to goal seek cell B1 to zero by changing C1.
Then, I need to goal seek cell B2 to zero by changing C2.
Then, I need to goal seek cell B3 to zero by changing C3.
...................
...................
I need to goal seek cell B100 to zero by changing C100.
I don't want to have to perform 100 different goal seeks. Is there a way to do them all at once?
View 10 Replies
View Related
Dec 8, 2012
I am very new to excel and my formulas dont add up.
I need to calculate based on my quarterly goal and my quarter to date actual sales what my percentage pacing is.
Now the way i did it is =B5/A5
Do i need to add the Quarter date calculations in there because its not giving me the accurate percentage.
Goal Actual Pacing
View 3 Replies
View Related
Apr 6, 2013
Goal seek function
Code:
Worksheets("Sheet1").Range("Polynomial").GoalSeek _
Goal:=15, _ ChangingCell:=Worksheets("Sheet1").Range("X")
Is it possible to modify this such that I can use variables? For example, the variables are 'left term', 'right term' and 'sigma max' .
I want to set the value of the following equation 'left term - right term' to 0 by changing variable 'sigma max' Everything is done on the userform and not in the spreadsheet.
(Note: In spreadsheet format, the above query is equivalent to setting a cell which has a formula to 0 by changing the value in another cell, fairly simple).
View 3 Replies
View Related
May 24, 2008
I'm trying to understand how complex numbers are handled/processed in Excel.
As related to my application, an interesting exercise would be to use Goal Seek w/s command to find the roots of the equation:
X^2 + 4 = 0
setting the (rounded) value in cell A2 to 0 by changing A1
A1:: 1+i
A2:: =COMPLEX(ROUND(IMREAL(IMSUM(IMPOWER(A1,2),4)),6),
ROUND(IMAGINARY(IMSUM(IMPOWER(A1,2),4)),6))
Obviously a conventional or direct use of Goal Seek wouldn't work since Excel treats complex numbers as text.
Perhaps, one should use Goal Seek twice in this case:
first: find the coefficient "a" for IMREAL(A2) = 0
second: find the coefficient "b" for IMAGINARY(A2) = 0
and the root would be "a+bi".
View 9 Replies
View Related
Nov 25, 2008
I've been playing around with GoalSeek in VBA because of this thread and found something about the syntax that I don't understand.
If I want to find out whether GoalSeek worked, I can use
Ok = Range("B1").GoalSeek(Goal:=10, ChangingCell:=Range("A1"))
Debug.Print Ok
This requires parentheses around the GoalSeek arguments.
If I just want to do the GoalSeek, then I need
Range("B1").GoalSeek Goal:=10, ChangingCell:=Range("A1")
with no parentheses. Why? The Help says that GoalSeek returns True if it works, and the proforma syntax includes parentheses, but then the only example uses the syntax without parentheses.
View 9 Replies
View Related
Jan 22, 2009
I need to use a Multiple Cell Goal Seek.
I used the code from this site.
Here is the ....
View 9 Replies
View Related
