I'm basically creating a scoring worksheet, where there are a list of questions. Each question has a weighting as some are more impotant than others. Each question also has a range of answeres as some may not be clear yes/no answers. Also not all questions apply to all assessments so some may be left blank.
I basically need a formula which does the following to produce a total score:
[Total awarded score] / [Total available score of questions scored]
Dave H kindly provided the formula below to sum all cells in a range with multiple values: =SUM(COUNTIF(D3:Y3,"a*"),COUNTIF(D3:Y3,"b"),COUNTIF(G2:Y2,"c"))
however my supervisor has just informed me that any A,B or C that appears in the J column is worth 4 instead of 1. Very annoying not to be given all the facts beforehand
The formula below works apart from the first bit which i want to be if F23 says 'fail' then i want the cell to say 'no dissertation' but if f23 says pass then i want the formula past that bit to be put into action.
I am creating a complex formula with UDF, this Hlookup is part of my complete formula. Meanwhile, I am working on this piece, not Sure what I am doing Wrong
I try to conver "MonthCurrent" Variable in a Year because the MonthCurrent is 06/01/2014, so will be Year(6/1/2014) = 2014
My Range called "DailyRates" is based per Year = 2014, 2015 ......
The number of columns vary, but the number of rows is constant. Lets assume, max_col is the number of columns and max_row is the number of rows per report. Then, I need the following logic as VB macro.
I am working between two worksheets. In one worksheet I have a list of account numbers. In the second worksheet I have the same account numbers in a different order. What I am trying to do is, If the account number from worksheet one is found in the column with the account numbers in worksheet two, I want to display the matching title for the account number that is in the same row but different column in worksheet two.
So essentially (if acct # from column x in wks 1= an acct in number in column m from wks 2, display the corresponding title in the same row of the matching acct # found in column m). Is this possible?
I have a complex request which I think can be done either via vb or a macro, not sure which is best?, will try to explain:
The table shows a list of items that make up various BOM. there are quantities of each item required at the different BOM level
0 = Top level 1 = Level Below 2 = Level below that etc, etc
If the top level (0) has a quantity greater than 1 then all the items at the lower levels should be multiplied by that quantity
THEN move to the level 1's and whatever the quntity is in the level 1 field, multiple all lower levels by that number if the quantity fields are blank, then always multipy below by 1
This needs to continue on down the chain of levels
So basically you end up with the correct quantites of each item at each BOM level.
I am trying to generate a formula for formatting text that will remove all spaces, comma's etc. Examples as follows:
The Bank Job -> the _bank_job_small.gif Picture This! -> picture_this_small.gif War Games: The Dead Code -> war_games_the_dead_code_small.gif Chacun son combat (Never Back Down) -> chacun_son_combat_small.gif
I've figured out a formula that works but it seems overly complex - it uses multiple SUBSTITUTE, SEARCH and REPLACE functions. Is there an easy way to remove a range of symbols i.e. ! : ( ' ?
I have a table of data where i want to rank scores. I know the general formula for this would be =RANK(B2,$B$2:$B$7) as an example however the range of data is not next to each other and I have tried this formula =RANK(B2,B2,B4,B6) but this comes up with an error.
I have a grid set up to add scores of which there are none. The boxes each have a formula, ie: =IF(FIXTURES!D1="","",FIXTURES!D1 ) this produces a blank square until a score is registered. At the end of each column, down and across there is a formula for adding up the scores. Downwards is: = SUM(C2:C25), this works fine.
Across however is: =B3+D3+F3+H3+J3+L3+N3+P3+R3+T3+V3+X3+Z3+AB3+AD3+AF3+AH3+AJ3+AL3+AN3+AP3+AR3+AT3+AV3 This doesn't work, just gives the answer TRUE. It did work before the original formula was added.
I've tried some of the basic formulas, but this is beyond me.
I have table A of 500 rows and need to compare it to table B of 2000 rows.
A B C D E
1 D6 - 1.01 Table with PC 3 Yes
2 D6 - 1.01 Bookshelf 4 No
[Code] .......
For example with these two, from the second table to the first. IF column B equal to column and the corresponding column C equal to the corresponding column C, return for example "yes". Then I need to check the same thing for the corresponding other columns, but basically everything would be tied to B and C. The problem is the second table, larger one, is basically all over the place, I could manage to do this if we would be talking equal length tables, but I do not know what formulas I could use to check further down a row once we find an equal value.
upon further consideration I thought that it would be best to probably do verything in two passes or maybe not see below macro to get an idea of how the end process should function
I teach history to four different groups of students that, in my spreasheet, I have labeled "1", "2", "3", and "4." The number of the group to which each student belongs is listed in CK2:CK100
Each quarter, I give all students a test and list the corresponding score next to each student's name. The scores are listed in CM2:CM100.
The name of each student appears in CL2:CL100.
I am looking for the formulas to extract the highest five scores in each group starting at CS2. So CS2 would contain the value of the highest score obtained by a student in group 1, CS3 the second highest score in that group and so on through group 4.
I am trying to do a dynamic sumifs that allows the user to use data validation lists to select 5 items to query. This is the easy part.
The hard part is that the amounts range to sum goes across 6 weeks and I want the answer to also take account of another validation input which specifies up to what week the range should be.
I have done a simply if statement in row 8 that flags weeks relevant to input cell B6.
How do I do a formula in say cell A16 that does the sumifs and acknowledges that the user has requested a dynamic range to sum?
I have a formula that looks up a chart showing scores referenced against each other as shown below. (Scaled down version) ******** ******************** ************************************************************************>Microsoft Excel - Table Template.xls___Running: 11.0 : OS = Windows Windows 2000 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1B1C1D1E1= ABCDE12006-2007Altofts JnrsCarleton JnrsEmpire ColtsHemsworth Terriers2Altofts Jnrs- 3Carleton Jnrs12-3-4-0 4Empire Colts - 5Hemsworth Terriers -Results [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
The problem is when the score is i.e. 12-3 the formula is only looking at 2-3. Hence this actually then shows as a defeat. The formula causing this problen is an array and shown as =SUM(IF(NOT(ISERROR(LEFT(resultsHome,FIND("-",resultsHome)-1))), --(LEFT(Results!$B3:$K3,FIND("-",Results!$B3:$K3)-1)>MID(Results!$B3:$K3,FIND("-",Results!$B3:$K3)+1,9)))) Question. How can the formula be changed to reflect scores like 12-3 to be shown as a home win............. .
I have a list of team members and their scores for team building events. I need to create a list of the top ten scores (including any ties). The list will have to show the top ten scores as well as any additional names whose score ties with one of the top ten scores.
I tried large and also a pivot table using the advanced but cannot get it to show the top 10 unique scores as well as any duplicate scores.
But I'm trying to make the formula a bit more dynamic. Is there anyway to have a set of data in B1:B50 (50 scores will probably be the most) and take the top x scores? X will be set in a separate cell (lets just say A1)?
I have a raw score for each case. I need to map those raw scores to scale scores. For example, a raw score of 0 to 0.49 maps to a scale score of 120, a raw score of 0.5 to 0.99 maps to a scaled score of 110, etc. There are a total of 13 possible scaled scores (120, 110, 100, ... 20, 10, 0). Is there a way to do this in Excel2003? (Note, if anyone on the list is 'bilingual' (Excel and SPSS), I'm looking for the Excel equivalent of the SPSS command RECODE raw (lo thur .49=120), (.5 thru .99=110), etc. INTO scale.
Say I have 3 columns of data: A1:C10 and I want to run a Match() function on them all together to see if I get a match any one those cells, say the value of have in X1.
Since, Match only allows a One-Column lookup array.. is there a way to "concatenate" or "append" the 3 columns together within a formula so now I would be looking to Match in an array that is 1 column * 30 rows?
Basically want to convert =Match(X1,A1:C10,0) to =Match(X1,A1:A30,0) without moving around the raw data in the sheet.
And I want to avoid doing an AND or OR formula that uses 3 separate MATCH() for each column.
I have a hunch that the MMULT or MMULT/TRANSPOSE functions are involved, but can't seem to get it right.
i need to replicate what i did using array formulas with VBA macro (array variable). to make things clear and simple i created an example for illustration only. look at it & u will find what i did & what i need to do ,much of it in writing so that i accurately describe my problem. attached is my example
Ive been reading up on Arrays and how to use Arrays etc so i believe that it could be done with an array but i could be complete wrong.
what i have is players names in A4 and there averages in H4. i have 22 players listed but i want to take the top 5 scores from h4 and display them in a different section with there as so: NAME(A4): AVERAGE(h4) repeated 5 times and updates Automatically.
I am an experienced Excel user, but it does not seem possible for this formula to be written all within one single cell. The SUM combines rounding a known value, followed by calculations, and subtracting a rounded number with modified calculations.
This is the formula which does work, although I would prefer for the sake of accuracy (and succeeding in the task of making the formula do exactly what I wanted it to do!!):
The function LOGEST fits to the equation y=b*m^x. Does anyone know if more complex functions can be found in Excel? Ideally I would like to fit to y=b*m^(x-a) + c. If anyone has come across a method, or some code, which allows this.