I'm looking for a formula or a macro that would change the value in column C depending on the value in column B. I need to formula to look for > 96 on the cell in column B and put an "E" on coumn C. Please see the example.
View 5 RepliesI have a range of cells each containing a name. Based on a number that has to be entered manually I want excel to return the names concatenated in one cell. So for example:
Number of variable entered: 5
q9001
q9002
q9003
q9004
q9005
q9006
etc.
Should give me: "q9001 q9002 q9003 q9004 q9005"
I have been trying to work with formulas using IF and CONCAT functions. But so far I haven't figured out how to have excel return me the correct amount of variables for each separate number that can be entered seeing the number of variables entered can vary from 1 up to 50.
(Using Excel 2010)
i have a column of data part number part text e.g 7w1,8b1 in the next column i want to write a function that will look at the other and dependant on whats in it place a word e.g. for 8b1 i need it to look at the "b" and enter beauvale if theres no "b" but a "w" enter wollaton, i have tried an if function and used "*b*" but it doesnt seem to like wildcards ....
View 9 Replies View RelatedI'm trying to use IF function with multiple statements in Excel 2003. However, whenever I try I get the "The formula you typed contains an error message".
I've tried even copying and pasting the example provided in the very Excel help file:
=IF(A2>89,"A",IF(A2>79,"B", IF(A2>69,"C",IF(A2>59,"D","F")))) (without changing any value or symbol) in a blank worksheet but even so I get the same error message.
I've also tried by copying and pasting examples I've found in different web pages, but the result is the same.
However, there's no problem with single statement.
This time I'm trying to use the max value and row value at the same time, but it won't let me. Here's what I'm trying to do...
A B C D
1 a b c d
2 7 9 4 2
3 e f g h
4 8 2 7 8
5 i j k l
6 4 3 3 9
I want Cell A7 to return the letter before the highest value in column A...
A7=e
B7=b
C7=g
D7=l
I tried to do something using two cells, but even then I couldn't figure it out.
I thought a ROW(MAX(A1:A6)) would give me the row and I could go from there, but that didn't work.
im looking to return an answer in cell c28. in b28 it will say "best goalkeeper"
in c28 i need a formula to look in range d2:d6 then return the highest answer and correspond that to whats in b2: b6. so we could say
(b2)shilton(d2) 33
(b3)corrigan (d3) 55
(b4)parkes (d4) 66
(b5)schmeichel (d5) 100
(b6)cudicini (d6) 14
in c28 it will return the answer schmeichel because he is the highest
and in d28 it would return the points he has.
I don't even know if this is possible but can you use some formula within Excel to return a value to a cell based on the cell formatting of another cell? Here is specifically what I am trying to do:
Cell A2 has a value of 10. If the font color of that cell is black (or "automatic") I want a formula in cell A1 that will return a value of "+10". If the font color of cell A2 is red, I want the formula in cell A1 to return a value of "-10".
I'm trying to put in a formula that will populate a cell with either "Yes" or "No" depending on the date enterd in a different cell.
View 8 Replies View RelatedI have a cell with a %. I want to return text in another cell based on that data i.e. between 90 - 100 = excellent, 75 - 90 = good, or something like that.
View 3 Replies View RelatedI am trying to put together a calculation sheet for court costs and I need a cell to return a value based on the amount entered in another cell. For example, if I input a value between 1 and 5 in cell A1, I would like "Cat" to appear in cell A2. However, if I input a value between 6 and 10 in cell A1, I would like "Dog" to appear in cell A2. Is this something that can be done without VB?
For the real spreadsheet, I would need the following outputs in cell A2 based on the respective ranges in cell A1:
Output to A2 = $405 if input to A1 is < $50,000.
Output to A2 = $905 if input to A1 is ≥ $50,000 and < $250,000
Output to A2 = $1,905 if input to A1 is ≥ $250,000
I have inlcuded my workbook that I use for tracking the NFL season and I'd like to add in some team logos just to make it look a little cooler.
The attached workbook includes three sheets, the first of which uses a drop-down for selecting picks. The second sheet has the team logos and the third sheet feeds off the first.
I'd like for team logos to populate into the third sheet based on what is selected in the first sheet.
I've already tried a few methods, all unsuccessfully. I tried this [URL] which was a great idea but it didn't allow for the same image to be repeated potentially 100 times. I also tried a method involving named ranges and indirect lookups to the linked picture image but that would require a different named range for every person and every pick which isn't feasible.
if it's possible to just do a massive nested if statement with a vlookup to return an image but I haven't been successful in that either.
I would like cell CM2 to look at cell G2 and get the value there. Here is where it gets interesting. I would like it to reference sheet "LookupTables" in column A.
Cell G2 will be a 4 digit number.
Cells in column A of sheet "LookupTables" will be formatted like this: 3 digit number-4 digit number-4 digit number, like this "001-0001-0001".
If cell G2 exists in column A of the LookupTables sheet, (but it needs to ignore the first 4 digits and the last 5 digits of that number) then it should return that value in cell CM2. I would like it to look through the entire column A of the LookupTables sheet and return all numbers that match the above criteria, and separate them with a comma.
So, if G2 is 0001 and column A of the LookupTables sheet contains several values including:
001-0001-0006
004-0001-0187
098-0001-0874
098-0002-4356 (it should ignore this since the middle numbers don't match the value of G2)
then CM2 should display: 001-0001-0006,004-0001-0187,089-0001-0874
Also, if there are no matches, then it should just return a blank cell.
I have 8 columns of data, the first 4 and the last four columns have the values which correspond to each other.. Here is an example:
VALUES(A to D)NAMES(A to D)
ABCDABCD
1.3451641.3383581.286221.359808321325370391
So value 1.345164 corresponds to 321 and 1.28622 to 370. I need a function that will return a value from one of the VALUES columns which corresponds to the second smallest cell value from the NAMES columns. For example – here the second smallest number in NAMES column is 325 so the function would return the corresponding value of 1.338358.
I have a spreadsheet that has (amongst other things) 2 charts based on dynamic data with ranges that redraw using vb... The charts will draw envelopes that correspond to an aircraft's moment or c of g envelope - there will typically be 2 envelopes drawn, "normal" and "utility" or "aerobatic".
Plotted on the charts are an aircraft's takeoff and landing weights and moment/C of G.
I'd like to be able to use a formula or VB to determine which envelope takeoff/landing information falls in (or if it falls outside all envelopes). This data would then be used to generate an error message/possibly also in conditional formatting, etc.
I have a questioner in a excel spreadsheet. Column C have yes or no answers. If answer yes is implied then I want the number 2 to appear in Column F of no is implied then the number 3 will appear. I am having trouble writing the language for this.
View 2 Replies View RelatedI am looking at developing a table that has three possible options for different categories, being Yes, No and N/A. A yes score a certain percentage depending on what column it is, and the use of N/A changes the percentage of other scores. (i.e. A yes in column 5.1 = a score of 5, a yes in 5.2 = 5, a yes in 5.3 = 10 and a yes in 5.4 = 15. However if 5.4 is N/A then this amount is diveded amongst the remaining 3, i.e. 5.1 = 10, etc.)
View 3 Replies View Relatedi have a user form in my spreadsheet that uses option buttons and text boxes for user entry. i need to take the values and true false entries from the option buttons and place them in cells. i am alittle lost with this.
View 6 Replies View RelatedI have a table of repair jobs done over a period of time. I just need to determine during which shift was the repair job done. The time during which the job was initiated is called Notification Time.
If the job was done at, say, 0100 hrs, then it was done during Shift 1 (0000hrs to 0800 hrs)
If done at 0830 hrs, Shift 2 (0801 to 1600hrs)
If done at 2030 hrs, Shift 3 (1601 to 2359 hrs)
I need a formula to allow me to determine the Shift no. by just checking against the Notification Time column.
I have a spreadsheet where in cell E2 there is a drop down box with the following options to select: Warranty Replacement, Insurance Claim, Billing Issues, Retention Opportunities.
In cell F2 is where wait time minutes are generated depending on what is selected from the drop down box in E2. I am trying to create a nested IF formula for the following scenario:
Warranty Replacement = 20
Insurance Claim = 20
Billing Issues = 15
Retention Opportunities = 20
I have in A1 a string of 100 characters..
I would like to create a SELECT CASE where,
if in A1 there is the word "BLACK" , put 1 in A10
if in A1 there is the word "WHITE", put 2 in A10
else put 50 in A10
I'm trying to use the instruction INSTR but with negative results..
(I have many cases other than BLACK and WHITE, that's why I need a SELECT CASE)
I have the following formula: =IF(C319<=300,9,IF(AND(C319>300,C319<500,6.75),IF(C319>500,6))) It returns 9 if C319<=300 but returns FALSE for all other numbers. I want the funtion to return 9 If <=300 and 6.75 if C319 is greater than 300 but <=500 and if it greater than 500 it should return 6.
View 5 Replies View RelatedI am attempting to choose a cell containing a text string based on information placed in a raw data area.
My output from this formula will be a line of text picked from yet another reference cell, based on each of the four choices.
Removed non pertinent information and edited for clarity.
I need a macro that based on multiple cell dates determines if the person is qualified or not, and puts yes or no in column A... all of the dates are yearly.
View 5 Replies View RelatedI have a rather large table that I need to return the column heading from.
Within the table I have a list of Names in the left most Column (A) and a list of Percentiles in the header row (row 2) (incrementing by 5%). By inputting the name and a cell value I am trying to return the column header.
Using an example;
5% 10% 15%
Bob 3.5 4.1 4.15
John 3.6 4.2 4.30
Chris 3.3 4.9 4.95
Sammy 3.7 4.6 4.7
Like I said, knowing the name and the cell value, I want to return the percentage value. So for example, Bob would be one input value, and another input value would be 4.1. I would want excel to return 10%.
I have found different suggestions using a combination of index/match but even using these functions I cannot get it to return the COLUMN value instead of a cell value.
I have been stuck for about a week on trying to create a Case Select macro. I am a novice in using VBA, so I am not sure what I am doing right OR wrong. What I need the macro to do is go through all of the text in a column and if the text is one of the 50 states, I need it to return one specific value, and if it is anything other than one of the 50 states, it needs to return a different value in a column 5 over from the column with the states in it. I have changed the code MANY times and so far I haven't been able to get anything to work. Here's the gist of what I have been trying:
Sub usstates()
Dim R As Range, s As String
Set R = Sheets(1).Range("J1:J50000")
For i = 5 To R.Rows.Count
X = UCase(R.Cells(10, 1))
If X = "AL" Or X = "AZ" Or X = "AK" Or X = "AR" Or X = "CA" Then
R.Cells(i, 2) = "SC"
End If
Next i
End Sub.................
Trying to write a formula that looks for the first number in the same row, then returns the value in that column in a different row. In the example included, cell A-7 should contain the formula. Within row 7, determine the column where the first number is listed (column D in this case), then return the value listed in row 3 of that column ('C' in this case).
I've tried Lookup, Index, and Match functions, but can't seem to get the right combination.
how return a true/false based on a cell containing a string (text) vs a value?
I have a column where most cells are blank, some contain text, and others contain dates. I would like to select and manipulate only the ones that contain dates (or values).
I have a spreadsheet that give me the percentage difference of two cell say a1 and a2 (=a2/a1)[format as % two decimal places]. The result is on say b5 as a %[format as % two decimal places]. Now on B6 I want to do this:
if B5 is >3.01% then b6 =" Market Test Required"
if B5 is +3% then b6 =110
if B5 is +2% then b6 =106
if B5 is +1% then b6 =103
if B5 is 0% then b6 =100
if B5 is -1% then b6 =96
if B5 is -2% then b6 =93
if B5 is -3% then b6 =90
if B5 is >-3.01% then b6 ="Market Test Required"
So on and so on.... I would really appreciate your help on this issue.
I have used excel for some time now but not with complex formulas or any vb.
This is to manage which departments (approxiamately 30) within a business need which compulsary training (approximately 11 courses)
Spreadsheet currently reads list of new employees and I want to be able to have "YES" or "No" values under the different courses
Is there a formula/function that i can use (like the IF Formula) to complete the following information;
EG: =IF(OR(A3=H2, A3=H5 etc... ), "YES", "NO"
Column H lists all departments
Column A lists deaprtments
A3 representing the 1st Department needing training
I would like to create a qestionairre with checkboxes used to answer the question "Do you need____" (Check if yes). On the following workbook I would like a list of all items needed, and a list of items not needed on yet another workbook.
Is there a way to create these lists without having blanks for values that are not true? (and because Im sure there is HOW?)