Excel 2007 :: Computing Last Term Determines Score?
Apr 23, 2012
I'm using excel 2007 for analyzing my students grades. I ran two exams and look for a way to compute a final grade score, which should be equal to the grade of the first exam for those students who took just the first exam and should be equal to the second score for those students who took the second exam. (Clarification: for those students who took both terms, their final grade should be equal to their score in the second term).
View 9 Replies
ADVERTISEMENT
Jan 5, 2012
I am trying to set up a spreadsheet that will have a calculator where variables can be modified in order to properly see staffing requirements using the Erlang C formula. I have all of the known variables, but I can't seem to figure out how to calculate the formula to get the numbers I'm expecting.
[URL] is the website I'm using currently, and I'm stuck at the actual Erlang C formula. I'm not very good at math, but here is how I am reading the formula in English (I'm using the given numbers on the site for the variables so I can check my work).
Erlang C = calls per second to the power of agents staffed divided by the factorial of agents staffed. The result is then divided by calls per second to the power of agents staffed divided by the factorial of agents staffed again, that result is added to 1 minus occupancy, that result is multiplied by the summation of 0 to 54 (54 being the result of 55 agents staffed minus 1), which is then multiplied by calls per second to the power of "k" (i haven't yet figured out what k should equal, i assumed zero from the summation) divided by the factorial "k".
The result I get is not even close to what the website shows as the answer for Erlang, which should be .239. Am I making faulty assumptions anywhere? It's entirely possible I'm not computing the formula properly, it's been about 12 years since I've taken a math class, but since I know all the values for the variables (except for "k"), I should be able to write out a simple Excel formula to compute this.
View 9 Replies
View Related
Aug 10, 2009
I want to see if i can make a formula that works out how a player got a score based on a scoring system. See the attached file for more details. I want this to show that the player scored or didn't score, played a full match or was a substitute, was booked or wasn't booked and was sent off or wasn't sent off.
View 5 Replies
View Related
Oct 18, 2007
I want to write an "if" formula which determines the colour of the adjacent cell. For example if colour of A2 is red then B2 is 1,else B2 is 2.
View 9 Replies
View Related
Apr 4, 2009
I have daily streamflow data for a large number of years. One column is the date and the second column is the streamflow value. I want to compute the maximum value and the 3-day maximum (largest average value for any consecutive 3-day period)for each water year (starts on October 1 and ends on Sept 30). Of course the number of days each year varies by one during leap years.
View 5 Replies
View Related
May 11, 2007
determining a formula to compute a sales commission.
Here is a sales scenario.
A $25 commission will be paid on sales between $50 to $150.
A $50 commission will be paid on sales between $151 to $300
A $75 commission will be paid on sales between $301 to $600
The sales person will enter the sale amount into column B. Column C should compute the total commission for multilple sales.
Example:
Column A Column B
Sales Commission
$50 $150 (which is the comm. for the combined sales)
$175
$360
View 6 Replies
View Related
Jan 9, 2014
computing the number of business hours between 2 dates.
Given the following information:
- Business Hours: 8am -5pm (8:00-17:00)
- Business Days: Mon-Fri
- if entry queued falls on a weekend or beyond the business hours, it should count the first hour on the next business hour.
Example: scenario 1 = Starts Sunday, 10am and Ends Monday 9am; count of hours should be 1
scenario 2 = starts on Monday 6pm and ends on Tuesday 10am; count of hours should be 2
View 5 Replies
View Related
Sep 14, 2006
Im looking for an Excel macro that will allow me to input any date and time and produce x,y,z co-ordinate results for planetary positions relative to the centre of the universe. I already have a database with ephemeris data but find it difficult to use manually inputting date and time constantly. I need to perform so many calculations that automating this procedure is essential to me but i dont know how.
View 2 Replies
View Related
Feb 18, 2014
My spreadsheet opens. Cell A1 determines what today is, formatted as "weekday, day month year" (e.g., "Tuesday, 18 February 2014").
I have another cell in which I typically enter the date for the coming Friday. Instead of updating this field once each week, I'd like to calculate the date for the next coming Friday based off the value in cell A1 (to be concise, if "today" happens to be a Friday, then it would calculate the date of next Friday). (Continuing the example above, the coming Friday would be computed as 2/21/2014. If I open the spreadsheet on 2/21, it would calculate 2/28.)
What formula or step(s) to follow to render this value automatically?
View 4 Replies
View Related
Nov 26, 2009
Using Excel 2003, Column E has a varying and unpredictable number of rows, which only the final 252 rows are of significance. I need to compute the following formulas, here written in English as I can't determine the proper terminology to accomplish this task in Excel:
Final Row with a number in Column E/average (final 252 rows with numbers in column E)+
Final Row with a number in Column E/average (final 126 rows with numbers in column E)+
Final Row with a number in Column E/average (final 63 rows with numbers in column E)
Once again, what makes this not straightforward for me is the column may have wildly varying numbers of rows.
View 6 Replies
View Related
Apr 7, 2014
I have to compute monthly standard deviation (volatility) from daily data.
The problem consists on the fact that the days during a month are not constant and change over time.
I attach the excel file : Price_Time_Series.xlsx
In this xls file, you can find dates in column A, price in column B and returns in column C.
I want to compute standard deviation at the end of the month for stock returns (column C), by considering all available days.
As you can note from the file, the days change over time and they are note constant. How can I compute standard deviation of them?
View 9 Replies
View Related
Aug 7, 2014
I am trying to calculate what date 2/3 of a lease would be. If start date is 5/8/13 and expiration date is 3/19/12 on a 36 month lease, how do I find out the date 2/3rds into it?
View 2 Replies
View Related
Jun 4, 2008
I am trying to sort first by year then by term (i.e. summer, fall, spring) for a school year. I know how to do custom sort so i set up a list. The problem comes here:
Term Year
Spring2002
Summer2002
Summer2002
Summer2002
Summer2003
Summer2003
Summer2004
Summer2004
Summer2004
Summer2004
Summer2004
Summer2004
Fall2005
Summer2005
Summer2005
Because there is no spring 2005, the last entry it sees is summer so it puts fall 2005 when in reality i want summer 2005 next. How do i sort so that any time there is a new year in the year column, it starts the sort over with spring in the term column?
View 9 Replies
View Related
Feb 22, 2009
How would I go about summing every 10th term using the offset function, since the data needed is on a different sheet.
An example of what I have to sum every 10th row.
=SUMPRODUCT((MOD(ROW(Z1:Z100),10)=0)*(Z1:Z100))
View 7 Replies
View Related
Oct 27, 2009
I am writing a equation in if statement...Is there a procedure to select the the second and third letter from a sentence and write a condition based on that...
I mean, say if I had 1XY in cell A1 and I want to write a if condition based on the XY from 1XY term, how can i do that?
View 9 Replies
View Related
Apr 5, 2006
I need a formula to calculate the end date of a monthly term when the start
date is not the 1st of the month. This is to show the proration for the
first month only, so the end date should be the last day of the initital
month/yr. Then when showing the rates for each period, all subsequent
periods would start on the 1st of the month. For example, start date
8/16/06, initial end date needs to be 8/31/06. The current forumla I am
using is: ....
View 9 Replies
View Related
Jun 8, 2009
we still run reports on tiny term emulator v4.3.1720 is there a way to have each report export to excel like this report each colunm each row in there own
cell here is a example of a report
View 9 Replies
View Related
May 15, 2007
I am trying to use a lookup formula to search for a word in a column, and return the value of the column directly to its left. I know that this cannot be done using the vlookup function, and I am not able to move the columns around. Is there another possibility? In plain English then, I would like cell E2 to look up the value of D2 in column B2:B4 and return the relevant value in column A2:A4.
View 3 Replies
View Related
Sep 5, 2008
I looked for a long time through the past entries and couldn't find an answer for this specific situation. I have a spreadsheet with multiple occurances of names in column A
A
green
green
green
blue
blue
white
white
white
and in column D I need a total of how many occurances of that name there are. that total needs to be listed in that row for that name specifically. There are many names on there currently and many will be added via copy/paste from another spreadsheet so i need one formula to paste into each row to cover ALL recurring names. Basically i need to enter in column D "=COUNTIF(A:A,"a term that would total the reoccurance of each name from the A column the corresponding D cell")
A D
green 3
green 3
green 3
blue 2
blue 2
white 3
white 3
white 3
The goal is to be able to copy/paste new entries into the spreadsheet, sort them by name, and have them automatically update the totals in each row even if they are new names. i guess it would be a "number of occurance of each term" code to cover all terms.
View 2 Replies
View Related
Feb 5, 2014
I have a 14 000-item list of product codes that are generated from different attributes. What I am trying to do, is to create a cover sheet where the user can select attributes from dropdown lists and get the first matching product code. I have tried using index/match, but I am struggling with wildcard lookup.
Example of generated product code:Cell A1:
ABX**J
Here, AB is the product group, X is the pressure class, ** is material (which is, for the example, unknown), and J is the end connection. What I now want to do, is to look in the long list of codes and find the first match:
Cells A2:A5:AAY02J
ABY03F
ABX01J
View 1 Replies
View Related
Dec 13, 2013
I could really use some excel function. Within my office, we work with several hundred files. Each employee is assigned files based on the last two digits of the file number. What I need is a way to identify what file is assigned to which employee based on the term digits of the file.
So for example, I have the following list of files:
1002856101
22781721
1044863815
1008799064
1044779765
1006511115
1007641804
0729939256
5303486020
8364709
0014094759
0019921519
8172717
I'm able to do a formula to get the term digits (meaning the last two numbers), but i'd like to have another column that can put names based on the term digit column. For example, Tom might work 00-04, Sally works 05-09, Greg works 10-15, Lucy works 16-21.. etc
I came across the below IF formula that is exactly what i need, except it only works for two associates and not the multiple that i need.. but it looks to be a good starting point nonetheless.
------------------------------
=IF(C2<50,"Sheryl","Lisa"). You should enclose Sheryl and Lisa with quotation marks as these are string values.
You can also use (if A2 is where the Loan # is):
=IF(Right(A2,2)*1<50,"Sheryl","Lisa")
The formula will acquire the last 2 digits of the loan and check it if it's for Sheryl's or Lisa's.
-----------------------------
View 3 Replies
View Related
Feb 8, 2010
I need to produce a summary of the monthly spend with parts suppliers for a number of vehicles, with each vehicle having it's own worksheet. The suppliers' names are in column C with the costs in column H.
So what I need to do is sum the results of column H on each worksheet when the supplier's name (which is on the same row) matches "Triple 7" and it's in the same month. The idea is to produce a sheet which displays the total spend each month with our suppliers.
View 9 Replies
View Related
Jun 9, 2014
I am building somewhat of a rudimentary search engine that looks for a name, address or unique identifier, using an Index Match formula that searches with a wild card:
=INDEX(Name,MATCH("*"&F3&"*",Name,0))
"Name": is the defined name for column C on a separate sheet.
F3: is "search field"
The current formula (above), only returns the first match. The source data is only 927 lines so not overly large. I would like to have an additional page that will show the potential results from the wildcard search via a formula. Therefore needing results; 2, 3, 4, 5 etc. from the Index Match. I have searched high and low for solutions but I have found none in regards to retrieving multiple results from a wildcard search, only on a specific term.
View 3 Replies
View Related
Jan 8, 2014
=COUNTIF(Sheet1!$A$1:Sheet1!$A$32515, IndexSheet!B2)
+COUNTIF(Sheet2!$A$1:Sheet2!$A$32515, IndexSheet!B2)
+COUNTIF(Sheet3!$A$1:Sheet3!$A$32515, IndexSheet!B2)....
+ COUNTIF(Sheet20!$A$1:Sheet20!$A$32515, IndexSheet!B2)
Above formula check for the value in IndexSheet!B2 and searches it across all 20 sheets and returns total sum
Is there any shorter version of this?
View 2 Replies
View Related
May 9, 2014
I need to write a code to create visio flow diagram with excel inputs(Excel 2007). Attached is the requirement.
Requirement_Specification.docx
View 1 Replies
View Related
Mar 14, 2007
im using spreadsheet works which seems to be very similar to excel. i am making a table full of numbers and i want to count how many times the number 1 appears and for that amount to be displayed.
View 11 Replies
View Related
Jan 20, 2009
i was assigned to make a computation for our individual scores using this:
EMPLOYEES A-N
inputs:
SURVEY(0-100)
LH(lost hour)
AHT(average handling time)
compute for:
SCORE
RANK
the SCORE has a total of 100%
so to get the SCORE, each input gets a percentage: SURVEY=40% LH=30% AHT=30% then added.
but the thing is LH and AHT has an equivalent.
LH
0 to 1=100
1.1 to 2=90
2.1 to 3=80
3.1 to 4=70
4.1 to 5=60
5.1 above=20......
eXAMPLE:
EMPLOYEE A
SURVEY(0-100) = 33.00
LH(lost hour) = 12.00
AHT(average handling time) = 14.20
SCORE = CODE
RANK = CODE
now for the rank i just use the code =RANK(SCORE A, SCORE A:SCORE N)
im lost on how to compute the SCORE though coz i can just use =((SURVEY*40%)+(LH*30%)+(AHT*30%))
but the one that needs to be on the LH and AHT are the equivalents and not the inputs.
and there is 1st 2nd and 3rd place. the name of the top 3 employees should be shown under the table.
View 10 Replies
View Related
Nov 17, 2007
i have a list of names in column A and a corresponding score for each name in column S.
on a stats sheet B10:B15 was trying to make a top 5 list using =LARGE(Week1!S15:S46,1) ... with Large i am able to get my top 5 from the week1 sheet but how can i assign the corresponding top5 persons from column A15:A45 in week1 to the stats sheet in column A10:A15
View 9 Replies
View Related
Apr 27, 2009
I am currently working on a score sheet with list of question.
I have:
On Sheet 1 (will be hidden later)
List of 14 questions (e.g. question 1 = 6 possible answers, for answer 1 is score -2, for answer 2 is score 7... etc).
In column C are possible answers, in column D are the score values.
On Sheet 2 (will be visible for participants)
Drop-down list based on Sheet 1 questions (Sheet 1; Column C)
I have set Cell link for each question so when you choose the 3rd answer, there is number 3, but I need to set different values based on Sheet 1, could be that 3rd answer is score 12. I do not know which function to use (I tried IF, but to me it seems too long and difficoult).
View 9 Replies
View Related
Feb 22, 2004
I've seen a few bowling formulas within mr. excel but nothing in code. so i tried it and the only problem i have is when a player strikes.....then the code should add the next two balls thrown..... it works if the next two are strikes but not if there is no 'mark'. So basically its not adding the second ball. The code looks right unless I'm just missing something. heres the code....
View 9 Replies
View Related
