I have a paper-based scoring sheet that we use to determine a rating category. I cannot figure out how to convert this scoring sheet into a lookup region and use some type of LOOKUP formula to determine the rating. I have looked at the various Grading examples on the Internet that show you how to look up a grade, based on a score, but they only look up a grade, based on a single input (the score).
Here I have two values going in--the person's age and their score, and I need to look up the rating, which is the column heading.
Can anyone point me in the right direction?
I've attached a sample spreadsheet which shows the original scoring sheet.
I have a table of data where each row is a person, and each column is a group that person might belong to. In the intersecting cell of a person row and group column is text showing what position they hold in that group (eg Director, Head, member etc). In many cases the cell is empty because the person is not involved with the group.
There are then two lookup tables which have scores; one for the group name and one for the position type. The group name is always the column header. I have used these lookup tables as I would like the scores for each group or position to be easily adjustable.
Probably best that I just attach an example. I tried to solve this using SUMPRODUCT. VLOOKUP on the two lookup tables would have been good but it seems it doesn't work with arrays the way I expected. My attempt (which doesn't work obviously!) is given.
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.
I have a formula in a cell which is refered to other cell within the same sheet and also to other sheet from other file. say
C1 = A1+B1+data file c1 Data file is another file in other location.
I was trying to break a external reference link (from Data File) from Data _ Edit Link, but it is converting all the formula to value. How is it possible to break only the external link without damaging the formula within the sheet say c1 = A1+B1+26 (value from data file c1)
In my workbook I have multiple sheets but I'm attaching a very simple workbook to demonstrate what I'm trying to accomplish. In my "Lookup" tab/sheet. I want to have known Latitude and Longitude data that will exist in columns A&B. Columns C & D will have address numbers and Street Name. I would like my lookup formula to find the longitude and latitude data from my "lookup" sheet, when the matching address information is typed in, in my 2009 sheet. I have to keep the street numerics and street name separate on this worksheet as well. I believe I'll need two separate lookup formulas as I need these formulas to start in cell G4 & H4 in my "GeoCoding1" sheet. Is it possible to have four columns of data to be viewed in a lookup formula? I tried this formula in cell G4 (GeoCoding1 sheet)
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).
I run a golf society and need to set up a golf aggregate sheet that will allow me to enter the players weekly scores and the sheet will then look for the lowest score entered for each player and replace it with their score of this week.
I'm Seeking in InRegards To Working Out How To Display Multiple Leaders Within The Same Cell Based Apon There Score In A Game Of Golf, Or If This Cant Be Done Perhaps A Message Saying "Multiple Leaders" So That It Is Known That Its Not Just The One Persons Name That Is Been Displayed. I Have Attatched The Document I Have Been Playing With While Trying To Work It Out Before Making A Big Sheet.
As You Will See "Daniel" And "Paul" Both Have The Same Score, And Below In The Summary It IS Currently Only Showing "Daniel" As The Scratch Leader, Is There A Way Of Getting It To Show Them Both As A Leader, EG: "Daniel, Paul", Or As Said efore Just Saying "Multiple Leaders" Thus Having To Manual Go Through Them ( The Fist Option Would Be Better As There Will Be Approx 85 Names In The Final Sheet)
I'm helping out a community that puts out a role playing game by making a character sheet for their gaming system. It's normally a pen-and-paper type affair, but a few people have reached out to me and asked me to make it. Now, one thing that I really thought would be nifty would be to make it persistent. I know of the Excel basics, creating basic formulas and the such, but this is where I hit a brick wall.
For the character sheet, they normally have basic attributes on a 1-20 scale. These attributes are accomanied by a bonus. The only thing is those, while these bonuses go across a linear path (For attribute 1, it's -30, for 2 its -20, for 3 its -10, for 4 its -5 for 5 its 0....), I cant seem to figure out how to create some kind of If/Then formula for it....
Heres my example:
Brad makes a character that has a score of 9 for one of his attributes. The bonus given to someone with a 9 is a +10. What I'm looking to do is create a table that sees that 'If (c9)=9, Then (d9)=+10'...
And my second related issue...
Is there any possible way to do the above mentioned formula in relation to a drop down selection bar? It's the same thing here; To break it down simply, It would be excellent if there was a way for it to say If (a10)=Warrior, then (c10)= +10.
I am using Excel 2002 in Xp and also want to use the spreadsheet on my Pocket PC using Pocket Excel.
I have a column of 10 numbers with rows 1,3,5,7 & 9 representing the individual gross scores of the 5 team members. Rows 2,4,6,8 & 10 have either a 0 or -1 (calculated) in them representing the strokes (or discount) the player receives for that hole. So the net scores for each player will be row 1 + row 2 and so on.
I need a formula that will calculate the total of the 2 lowest net scores and 1 low gross score with the score for each player only being used once which will then represent the team score for that hole.
A sample column would have 4,0,3,0,4,0,3,0,5,-1 in it.
I'm creating a Speech and Debate calculater that presents winners names by place according to their score. If you look at Tab HSSE Results it will read the inputed information from HS Speech English. I want the names of the students to appear in the correct ranking order in the respected grade rankings chart. I currently have a countif formula which reads the grade, but I think I need to link that with an index formula which I'm having problems with.
I have an excel sheet i would like to make into a journal. I would like to make a page for each day of the year. I want to duplex print it, but i cant seem to do it directly from excel would it be possible just to make the file into a pdf?
Here is the macro i was trying:
Sub ghgh() Dim i As Long, temp As String temp = InputBox("Enter date") If Not IsDate(temp) Then Exit Sub For i = 0 To 2
Trouble is it only does one page at a time, so i cant duplex print.
I have data in two columns on the same sheet that I need to transpose into rows on a separate sheet (same workbook). One problem is that I need to reference off one of these columns (column B - in Sheet "Gp Trg Plan") as the number of lines will vary.
Example - Sheet "Gp Trg Plan" Column B Column D Tower Module 1 Tower Module 2 Tower Module 3 Building Module 1 Building Module 8 Street Module 6
Ideally the row will look like.
Example - Sheet "Status WS"
Column A Column B Column C Column D Column E Column F Column G Gp Name Sub Gp Person 1st Mod Date 2nd Mod Date
There is an undefined amount of training modules (columns D in Sheet "Gp Trg Plan"), but no more than 10.
I have a portfolio of 23 funds and I need to calculate IRR for each one every month.
The problem is that I have dispersed information for each fund.
For instance in sheet CashCallsBD I have a table with the funds cash calls. This table has a first column with the Fund Name then other column with the cash call date, another column with the cash call value. This table may have other columns. One fund may have several cash calls.
In sheet DividendosBD there is a table with some columns that refer to cash distribution movements. The main columns relevant for this case are Fund Name, Date and finally Cash Distribution (signed in red in the attached document)
And then my problem: In order to calculate IRR for each fund I need to create a table (for each fund) that merges and sorts the information of CashCallsBD, Dividendos BD and present market value for each fund and then apply the XIRR function. For each fund this sorted table would have at least 2 columns (date and values).
I have a button with a macro assigned. The macro needs to create a new worksheet at the end of the workbook, rename the new sheet to "New County", make the cell size match the rest of the workbook, and then paste in a template from another sheet called "Master".
I recorded a macro that does this, but it has a static reference to the new sheet instead of what ever the new sheet is. So the Macro works one time, but never again. I'm sure this is an easy fix, and perhaps there is a much easier way to do this, but my limited VBA knowledge is hampering me.
Sub AddNewCounty() ' ' AddNewCounty Macro ' Adds a new County sheet to the workbook. ' '
I have a working formula that I want to convert to vba code... I only want to store the formula result in each cell in my range, not the formula itself. It is an ARRAY formula (entered with CTRL + SHIFT + ENTER) and it would be ideal if it could follow R1C1 format to keep it relative for each cell.
Here is the formula as it's entered in my spreadsheet:
I am not super familiar with using INDEX/Match combos which is why I am struggling with this one...
The end result should be:
For each cell in myRange cell.value = 'Formula result here Next cell
If possible to fill the range in one step:
myRange.value = 'Formula Result Here, utilizing R1C1 relative reference... (this may be a stretch given the range is 55krows x 18 columns)
The problem at hand is I need to fill a range of approx. 999,000 cells with values and the values are a VLOOKUP with 4 matching criteria. So returning the value from Col U on my LookupTbl when the criteria in Col's A, D, Q, and R are an exact match to my values in A, D, Q, and Z:AQRow1.
I have an excel work book where in one page (sheet1) contains formula types to be used based on a code. and in the other page (Sheet 2) when I enter the code I need it to get the formula from 1st page and then do the calculation in the second based on the code. Ex : Sheet 1
Code Formula 0 Cell A * CellB 1 Cell B - Cell A*5% 2 (Cell A + Cell B)*10%
I have two sheets, the first called 'AF' and the other called 'POR'. For one of the columns in the 'AF' sheet I used the following formula - VLOOKUP(B2,POR!$A$2:$A$111254,1,FALSE) to retrieve a string consisting of 7 numerical digits (Ex. 4678451). Now if I want this result of 7 digits in the same 'AF' sheet to be converted to the word "YES" - how do I achieve this? (All the 7 digits numbers are different or unique). Is there some sort of REPLACE command I use here or some other means?
I have whole bunch of formula's added to cells by code. So far so good. How could I then set in macro code that the result is stored in cell as value rather than formula? Now when I go to cell you can see =PIBVSearch(.... yadda yadda yadda in the field above.
I would prefer to change it so that content of cell changes to it's value so there's no formula visible(and above all not stored to harddisk(LOTS of disk space lost. Whole project started to trim down 269Mb excel file into smaller piece! I don't want it to go back there too easily...) or in danger of getting updated which might take a very long time easily).
Code works so that as long as there's rows which haven't got update yet but do have product run code(or something like that. Not quite sure what that number is but I figure it's product run) after code reached that far(which it copied from another file). Then for each row it calls functions for each cell which are responsible for filling invidual cells.
So either way to replace content of whole row with values or invidual cells, I can insert both into loop easily though whole row after cells are calculated would be a) faster to add(just one place to add, not 30...) and likely faster to run(project is about getting the file to run faster afterall...).