I have 12 teams(rows) and would like a cell(A21) to show the team name after determining the max value of the column(O). Is there another way to do it besides "ifs", I've maxed out the function at 7, but there are 12 teams

If I could get this figured out it would be great, and a seperate question/addition problem I would also like to do is have the cell come up the same color as it is above.

I can choose among three variables in cell A1. They are named P10, P50 and P90. I want to substitute them with 1,2 and 3 in cell B1. Is it possible to do this by using the substitute-function or do I have to use a nested IF.

I was creating a formula in excel yesterday that used nested IF's but I soon found out that the maximum number you can use is 7. So I did a bit of digging and found out that I would be better of using a Select Case in VB.

What I want to do is this:

=IF((SEARCH("case1",A1)),"text1",IF((SEARCH("case2",A1)),"text2",IF((SEARCH("case3",A1)),"text",...etc until I have listed every possible case and I want to do this for a range of cells A1:A1000 or however many entries I have.

I am assuming I would have to use code similar to the solution in this thread:

[url]

The only thing is I cannot translate this to what I am doing. Do I need to write a loop to go through the entire range of cells I want to check? How do I determine if a specific string is contained within a cell? Once the macro is written do I refer to it from a cell or run it from within the macro menu? Please bear with me as I am new to this and have not coded in VB since school.

I have a table of approx 20k customers (column A) and their $ sales (column B)for a time period. I need to sum the sales of approx 50 customers. I have the names of the 50 customers to be summed in one column so i can reference them and easily change out a customer if need be.

I was trying to search for a set of keywords in column1 and if the logic is true then print predefined word in column 2. The problem is I have 14 sets of keywords for which I tried creating the longest if statement only to find out that the limit is 7. Below are few of the sets.

I need to incorporate some vba code which will close my workbook if no key or mouse button has been pressed for about 5 minutes. I'm told that Inkey and Timer functions are not available in VBA.

I am trying to write a formula that will satisfy the following:

C1 and G1 are number values.

IF (cell E1 does not contain any text) AND (G1-C1-12.5>=0), then output (G1-C1-12.5), otherwise output nothing (""). BUT, IF (cell E1 DOES contain text) AND (G1-C1-13>=0), then output (G1-C1-13), otherwise output nothing.

=IF((OR(B14=1,2,3,12,15,16)),"A",IF(OR(B14=4,5,6,7,8,9,10,11,13,14,21,40,41,42,43,44,45,46,47,50,51, 52,53,54,55,56,59,60),"B",IF(OR(B14=20,30,57,58),"C",IF(OR(B14=33,31,32,34,35,22),"E F OR G",IF(OR(B14=36),G,"ERROR")))))

I have a sales ladder which ranks my people by the % difference between budget and sales to date, but not everyone has made a sale yet and the % difference is zero for 6 people. These 6 people all have a rank of 75. Where this happens, I want to then rank only those 6 people, based on their budget figure.

My current formula reads: =IF(F83=0,75,RANK($G83,$G$9:$G$83,0)) where F is the sales value and G is the % difference. Budget figures are in column D.

=If(isError(find("-";a1));mid(a1;3;9);mid(left(a1;search("-";a1)-1);3;9)) where: a1=AN03048-12 and its result I wanted is the following: 03048 (great! ) But... what if I'd have ANN03048-12? Then the result would be N03048...

How can I obtain only 03048 or whatever (but only numbers) avoiding previous letters? How can I only pick numbers without letters starting from the left beginning of the record? I'd like to get only numbers before the "-" symbol. I think I should add a function before or instead of the "3" number (which I highlighted in red) in the second mid() function

I would like Excel to look at column A, and if there is a number0, count it and go to the next row. If it's =0, then go to column B and see if THAT number is 0. If it is, count it; if not, go to C. So my data looks like:

A B C 0 2 3 4 0 0 0 0 0

I only want it to count one time per row, so right now I am using =IF(COUNTIFS(A2:A30,"0",B2:B30,"0")0,COUNTIFS(A2:A30,"0",B2:B30,"0"),IF(COUNTIFS(B2:B30,"0",C2:C30,"0")0,COUNTIFS(B2:B30,"0",C2:C30,"0"),0)))

I also tried it with a SUMPRODUCT to simplify but both formulas are getting me the wrong answer. I'm using Excel 2007.

I am currently having trouble with what I thought was a simple IF statement. As you will see from the formula I want to complete a statement for every month of the year but Excel will not let me go past July. Is there a limit to the number of arguments in an IF statement and how do I overcome this? =IF($A$3="Jan'08", SUM(C7),IF($A$3="Feb'08",SUM(C7:D7),IF($A$3="Mar'08",SUM(C7:E7),IF($A$3="Apr'08",SUM(C7:F7),IF($A$3="May'08",SUM(C7:G7),IF($A$3="Jun'08",SUM(C7:H7),IF($A$3="Jul'08",SUM(C7:I7),"n/a")))))))

I am having trouble evaluating two conditions with nested logical operators to produce the output I need.

For example Condition 1 has 5 choices (A,B,C,D,E) and Condition 2 has 3 choices (a,b,c).

Breaking it down, [Condition 1] option "A" always applies and should give "Yes" (No matter [condition 2], "A" always gives "Yes") [Condition 2] option "c" always applies and should give "Yes" (No matter [condition 1], "c" always gives "Yes")

[Condition 1] option "D" never applies and should give "No" (No matter [condition 2], "D" always gives "No")

[Condition 1] options "C" and "B" only apply if [condition 2] is "b" (if so, the answer is "Yes")

What I need is the equivalent of:

if ((([condition 1] == A) || ([Condition 2] == c)) || (([condition 1] == B || C) && ([condition 2] == b)), "Yes", "No")

I am using COUNTIF functions with Defined Name lists to quickly determine if a certain number is on a list. As a backdrop, I am using Chemical Abstract Service (CAS) numbers and attempting to somewhat streamline chemical approval for a small company. CAS numbers are often in the format of XX-XX-X with varied amounts of numbers. One of the defined lists however does not have dashes.

Thus, I am using cell B1 to enter the CAS# once and then for each list having a column to itself with an associated worksheet with a defined name list. Most of the columns have the function =B1 with the conditional formatting of =COUNTIF(definedname,BX) and formatted to turn red if the chemical is on the list. This is working for all of the columns except for the list that needs the dashes removed. For instance, CAS 64-67-1 is put in B1 and cell B5 has the formula =SUBSTITUTE(B1,"-","") which brings the number to 64671 which matches the number in my defined name list. However, the cell will not turn red. What am I missing?

I m using SUBSTITUTE function to replace commas with fullstop so I can multiply the end result with a number. But when I try to multiple for example B2 (0.1831) with 5, i get the VALUE! error.

Value Real Value (after substitute function) 0,1831 0.1831 23,3333 23.3333 12,5199 12.5199 5,5000 5.5000 20,5999 20.5999 24,4671 24.4671 200,0000200.0000 2,5386 2.5386 0,4000 0.4000 1,5019 1.5019

how I can resolve this so I can use the real values for computations (eg Real value *5), without having the VALUE! error message.

In trying to create a formula that remove EVERY symbol from a cell (C6) and replacing it with a space.... the "SUBSTITUTE" function is telling me I have too many nested Substitutes.

I need to add the SUBSTITUTE function to this but can't figure out where it goes if somebody could point me in the right direction please? My substitute formula is SUBSTITUTE(L5,"car","train").

=SUBSTITUTE(A1) in cell 'A2' gives me: 1234567890 =ISTEXT(A2) in cell 'A3' gives me: TRUE

BUT, =A3+1 gives me: 1234567891

Hows that happening? Substitute function gives me the output which is a TEXT, and how is it that when I add 1 to it, I get an answer? Shouldn't I get a #VALUE! error instead?

I work for a Machine Shop in the Toledo area. We use a quotation sheet to quote our products to our customers. It has 3 cells that describe the Customer, the Contact person at that customer and finally that Contact's Email Address. I have a drop-down menu in each of these cells. The first, Customer, is based off a simple list of our customers. The Contact drop-down then uses the Indirect Function to search the worksheet for that Customer. The drop down is actually based off of a Range Name saved as that Customer's Name.

However the problem is many of my customers have & or , and since Excel does not allow those symbols or even spaces in a Range Name I have to use Substitute to be able to keep the spaces and the & or ,.

It is important that on the Quote Sheet the Name of the Customer is identical to the actual name of the company so I cant use AND instead of &.

Currently my Second drop down, Contacts, has a formula that looks like this {=INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($H5,"_"," "), "3", "&"), "2",","))}.

My problem is that when I give the Range Name a Name , my drop down doesn't work and Excel tells me that the above formula results in an error.