I'm struggling to find a way to index data in an array that meets certain matching criteria. I am looking for an employee's rate on a given day by searching a database that lists the dates that an employee's rate was changed. I was hoping to solve it with a crafty index and match array formula but have been unable to find something that works so far. I have attached a simplified example of what I am trying to do.
View 2 RepliesMy spread sheet reads column A teamname, B games played, C wins, D Losses, E Ties, F winning % and G GAMES BACK
I'm looking for a formula to determine games back based on wins, losses, ties and also based on the number of games played.
example, team 1 played 9 games and is 8-0-1. Team 2 has played 9 games and is 8-1-0. Team 2 is would be a 1/2 game back. Team three is 8-0-0 also a 1/2 game behind team 1 but tied with team 2.
So in column G it would read team1 "-", Team 2 ".5" and team 3 ".5"
I am tracking baseball batting statistics. The first column in each row is the player's name and the next column is the date. Several columns of statistics follow. I enter the batting stats game by game. I know I can use the SUBTOTAL function combined with AutoFilter to view each player's statistics individually totalled, but what I'd like is to see that subtotal line permanently displayed for each player.
View 2 Replies View RelatedI am making a spreadsheet for my fantasy baseball league and I have it set up how I want it, minus the correct formula(s) to make it work.
Basically, I have 15 different tabs in one .xlsx file. The first 14 are each team and their players. The 15th is a huge ranking list of all the players in the league basically.
What I want to happen is, as I enter a name in any of the first 14 tabs, somehow on the 15th the corresponding name with be crossed out, colored, etc.
I have used several IF and AND functions in the past in order to check to see if 2 values = true and return a third.
(for example =IF(AND(A1>10,B1<20),C1,"")
However I can't seem to figure out the senario below.
I have 2 coulmns, Column A has dates and Column B has a numeric value
I need to check to see if column A falls between 2 dates and returns the sum of column B
For example if have 100 rows and 10 of those rows have dates that fall between 06/01/09 and 07/01/09 then add up column b for those 10 rows and return the value.
I've been racking my brain for hours over the past few days trying to compile a spreadsheet for work. Currently the spreadsheet covers all the aspects of a auto loan process. It is used as a tool for auditors to check the work of others. The spreadsheet simply uses "P" to designate "Pass" or "F" to designate "Fail" on whichever section.
How do I reference the P's and the F's in the spreadsheet? I've had trouble getting excel to query any text in cells. Also, each loan application is started on the next line, so I need to make sure these formulas repeat as a new line is started - how would I go about doing this? Currently - Cell G3-CG3 contain the "P's" or "F's" - the formula I had used was {=IF(G3<>"P","FAIL","PASS")} - which does work, on a single cell. However, if I try to add the entire range in {=IF(G3:CG3<>"P","FAIL","PASS")} the cell returns #VALUE. Obviously, I'm inexperienced with excel in this fashion, and I need to know what formula I should be using.
I am trying to set up a query to search a date range within a table in one sheet based upon a more narrow date range in a second sheet AND return a simple count, median currency value or value based upon a ratio (see attachment).
Note: the Sale Price, List Price and Supply values will be based upon the filtered date range (each quarter) and return a value based upon that range. The Sale Price and List Price also need to be a median within each quarter value so I can chart the data.
Help.pdf
Here is what I need to return:
Num Sales - simple count from each quarter
Num Listings - simple count from each quarter
Sales Price - median value within each queried date range
List Price - median value within each queried date range
Supply - numeric value based upon absorption rate, supply = listings each quarter / absorption rate (absorption rate = sales each quarter / months each quarter)
I am trying to retrieve the "option" values and "inner text" from two dropdown lists on a website. Using the code below I can retrieve the data from the first list but the second dropdown is not populated until a value is selected in the first list.
Firstly, is it possible to specify a particular "select" element by using its "name" or "id" and if so how? Secondly, will I be able to populate the second list by looping through each of the values from the first list and retrieve the second list values each time?
I have been searching all day so far and cobbling together bits of code that I have found that do some but not all of the process I require.
[Code] .....
Below is a small section of the results so far. The second dropdown list will give the models based on the value selected in the first dropdown.
inner text value
Alfa Romeo 53
Aston Martin 69
Audi 16
Bentley 87
BMW 17
Chevrolet 77
Chrysler 54
Citroen 18
Dacia 92
Ferrari 95
Fiat 19
Ford 20
Honda 22
I have a list of stores (A1:AX) and corresponding customer affinity numbers (B1:BX). Each time a customer visits a store with her affinity car, a record is generated. So the data is quite simple - two columns.
A sample of my list:
StoreCustomer#Phl01180Det01808Nash02118Phl02542Hou02TE2Atl03094
The tricky part (as always) is the output. I would like to divide the customers into three groups:those who visit only 1-3 stores
those who visit 4-7 stores
those who visit more than 8 stores
I would like to create a way to view a list of stores in column A and see how many of its customers are in each of the three groups listed above. Does that make sense? Output might be:
Multi-Store CustomersLowMediumHighPhl23188Det240322Nash123489Phl3498790
I currently have an Excel file with the ODBC connection and using my own SQL. Doing it this way would require the super user to go into each sheet then to the SQL each time to change the dates. I know there is a way to allow them to simply change the dates in cells H1 (or H1 and H2) then refresh data and whoolaa it would be updated.
My goal if possible via this board and all the knowledge available would be to have a spreadsheet allowing a super user to change the date in Cells H1 and H1 & H2 on sheet Tst1 and have the data returned to sheets (Tst2, Tst3) based on my own SQL statements.
Here are some parameters that I think you might need to know.
(And as you may guess I do not have much VBA experience so if at all possible use my naming convention shown below. and feel free to write for a first grader.)
User Name (not actual): "UID"
Server (not actual): "DEV"
Password (not actual): "PWD"
File Name: Excel_ODBC.xls
Sheet Name: "Tst2"
"Tst3"
Output Cell: "A1" for both sheets.
Oracle table name: UAB .................
I am captain of a Dart Team in 2 different leagues. I have a excel spreadsheet that does some calculation for me (Wins, Losses, Percentage) when I manually enter the numbers. I have just created a workbook with multiple worksheets for each week of the season, including playoffs, and a sheet for overall and print. I want to be able to input the data for each week and have it auto calculate on the overall page and auto populate the print page. The overall page will have all the stats, wins singles games, wins doubles games and wins 3 man games, along with percentage in each category. I am pretty sure I can accomplish this task. The problems I am having is:
1. Adding wins and losses based on player number, I have one cell with a players number and I will put a 1 or 0 in that cell based on a win or loss. Off to the side I have the player roster with card numbers and a win column and loss column. I would like excel to see the 1 or 0 in the main column and add the appropriate to the roster column, calculating total for the night. Then adding that to the overall page.
2. The print page, I want a easy to read page that displays each players, wins and losses for each category described earlier and averages through each week displayed nicely to distribute each week. Would I have to export that page to word? I want to display wins and losses as 14-7 without it calculating it.
If someone can point me in the right direction that would be awesome. I can do simple stuff in excel, never had to do anything like this before, wanted to do it for a while and just didn't know where to start. What formulas to look up, not familiar enough with the nomenclature to look for the correct functions to accomplish this task, or if it's even possible.
I have many spreadsheets with sports stats. I pulled the information from the web. So on one page I have the stats. The next page I want to be able to type the teams name and have excel pull out all the stats I want. Makes it easy to compare teams stats that are playing each other. Is this possible I tried to use most of the lookup functions but I just cant figure this out.
I will attach the sheets.
I seem to find myself stuff on this project yet again, JBeaucaire was able to help me to get this far im hoping you will be able to help me with the next step of this project
The doc iv attached is able to give me the differnece between low and high numbers with this example low numbers being between 1-18 and 19 – 36 high numbers and 0 being a break if u test the project u will understand im now trying to create a pattern of 3s were im abe to get stat hits on for example the patterns of 3s are the following(numbers) low,low,low-low,low,high-low,high,high-low,high,low every time I type in the numbers 3 times in a row on the 3rd one I want it to show as a hit(success) if the 3 numbers I type in are not a hit like high,high,high I want it to show me a miss if it is a pattern I want it to show a if theres a 0 it must rest like from 0miss,4hits,o breaks to 0miss,0hits,1break and so forth im also wondering if at another part of the form it can keep the total stats like iv added In the doc
Is there a way to automatically update football stats from a website. I want to update many different stats each week of player and team performances. Can I specify a website to go to update the info and prefer to do it each Tuesday. Let me know if more details are needed.
View 1 Replies View RelatedI have a spreadsheet full to the gills of patient information. I have a separate sheet with information from the first spreadsheet like counts of a certain type of tests or diagnosis codes using a 'countif' function. This is based on all the info in that first sheet. I want to make a second sheet with the same basic setup, but only from the patients who visited during this fiscal year. Is there a code I can use to make this happen?
Let's say the date of the visit is in column A and the test type is in column N. The code I'm already using is =COUNTIF(Info!N:N,"*TestName*") How do I get this to return results only if the date of visit was within the fiscal year?
I am new to pivot tables and charts and to build.... I heard that the best is to use pivot tables but no clue how to do it I have tried but no luck....
How can I see the following information in chart?
-Non con close out time by person- so who is failing to close out on time in this case is completion date
-Frequency of reports by area/ shift
-Number of reports by month, split into critical/major/minor
-Main reasons for reports so trend of type of record
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.
i made another thread about this but this one is more detailed.
all my questions are in the attachment excel thing, on the main page.
I having trouble trying to find the right formula to display statistics onto a 'Stats' page for my horse racing results from each month.
On the Stats page I have a summary of each month where I can view winners loosers, profit and loss, and the different types of racing (eg Flat and Jumps,Sprint, Non-Sprint).
For January I have two rows on a column for Sprints and Non-Sprints here I want to diplays the results (selections,won,lost) for each. The infomation on the January is all in date order and sprint races and non-sprints are all mingled in together. How do I display the infomation for sprints and non-sprints seperately on my stats page??
layout so far: (how it should be)
Stats page
racetype|selections|won|lost|placed
Sprints | 2 | 1 | 0 | 1
Non-Sprints | 3 | 1 | 2 | 0
Jumps | 1 | 0 | 1 | 0
January Page
RaceType|Result
Sprint |placed
NoneSprt |won
Jumps |lost
NoneSprt |lost
NoneSprt |lost
Sprint |won
I have used the "sort" function on many occasion, and I am now stumped as to why it wont work
I have a table with 9 columns: Team, Games played, Games won, Games tied, games lost, goas for, goals against, diff and points.
Each of the collums and rows have different calculators in them.
I want to sort each time the stats are modified, to show who is is first place, second etc. Thus the sort is by Points first, then Diff.
What I would like to do is for each worksheet run "descriptive statistics" in data analysis on the data in column Q, which varies in length, and then copy the "mean, median, SD, and skewness" in the results down columns T:W to the last row based off of a count in Column Q. Finally, calculate the "zscore" in column S based off of the formula in cell S2 in the attached workbook.
View 8 Replies View Related