7 Nested Functions Alternative - Substitute Function

Nov 4, 2008

I want to substitute the following "special" characters for an underscore. i need to do 9 different characters!!! is there another way?

as you are limited to 7!!!

excel 2003!

=INDIRECT("_animal_"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2," ","_"),"/","_"),"-","_"),"&","_"),"~","_"), "(", "_"), ")", "_"), "$", "_"), ":", "_"))

View 9 Replies


Alternative To Nested IF Functions

Nov 2, 2004

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.

View 5 Replies View Related

Nested IF: Substitute Three Variables

Dec 10, 2008

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.

View 2 Replies View Related

An Alternative To Nested IF's

Sep 19, 2007

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:


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.

View 9 Replies View Related

Alternative To Summing Nested If's

Oct 8, 2009

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.

View 5 Replies View Related

Alternative For Extra Long Nested If Statement

Sep 20, 2012

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.

=IF(COUNT(SEARCH({"process","nim","root/bin","agent","genhkdly","script","dbsync_dly","vcs","listener","horcm","msoffline","cpu mdmprd"},S2)),"APP")
=IF(COUNT(SEARCH({"percent","inode","space","check-kerberos","nfs","file system","jfs"},S2)),"FLS")

[Code] ....

View 3 Replies View Related

Alternative To Inkey, Timer Functions For Auto Log Off

Nov 13, 2009

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.

View 11 Replies View Related

NESTED IF And AND Functions?

Jun 30, 2014

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.

View 2 Replies View Related

Nested If/or Functions

May 7, 2009

IF/OR function below is not working

=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")))))

View 4 Replies View Related

IF AND Functions Nested

Aug 20, 2008

I recently came across a spreadsheet an ex-employee wrote with an interesting IF-AND statement in it. The only way I've ever written one is:

=IF(AND(something=1, somethingelse>2), do something)

His statement was written in the following manner:

=IF(something=1*AND(somethingelse>2), do something, if(thing3>1*AND(thing4>1), do something)

View 9 Replies View Related

Nested Functions Limits

Feb 9, 2009

I have the following formula in a cell:

View 2 Replies View Related

Multilple Functions In Nested IF

Nov 9, 2009

I have these 4 functions that i want to be able to "run" in a nested IF where the functions are used in this manner:

=if((funct1 or funct2 or funct3 or funct4);"OK";"")

funct1: =IF(AND(B7=1;D7=1;E7=1);"OK";"")
funct2: =IF(AND(B7=0;C7=1;F7=1;A7<10000);"OK";"")
funct3: =IF(AND(B7=0;C7=1;G7=1;A7<25000);"OK";"")
funct4: =IF(AND(B7=0;C7=1;H7=1;A7<50000);"OK";"")

View 14 Replies View Related

Using Nested RANK Functions

Aug 7, 2006

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:
where F is the sales value and G is the % difference. Budget figures are in column D.

View 11 Replies View Related

Nested Functions On Text

Jun 6, 2009

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

View 10 Replies View Related

Nested Functions Limit

Apr 1, 2008


I need to nest the above formula within an IF statement. Unfortunately, I am already up to the Excel limit of nesting 7 functions.

Can anybody see, at a glance, how I can achieve the same result with at least one less nested function?

View 9 Replies View Related

Nested IF And COUNTIFS Functions

Feb 23, 2010

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:

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.

View 9 Replies View Related

Excel 2007 :: Too Many Nested Functions

Jan 31, 2014

Attached is a small spreadsheet, my problem is I am using Excel 2007 and it only allows me 7 nested functions and I need to do 31.

Nested Function.xlsx‎

View 8 Replies View Related

Multiple Nested IF & SUM Functions/Formulas

May 23, 2008

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")))))))

View 5 Replies View Related

Using Substitute Function In VBA?

Feb 7, 2012

Following code doesn't work

Dim Value1 as String
Value1 = Substitute(Cells(4, "A"), "A", "", 1)

correct syntax to assign the result of substitute function in the Variable?

View 3 Replies View Related

Multiple Condition Nested Logical Functions

Feb 17, 2014

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")

View 4 Replies View Related

Nested Functions IF, AND, OR, NOT: If A Part Is Discontinued, It Cannot Be Ordered

Nov 27, 2009

using function IF, AND, OR, NOT the following is the description and attached is the photo of the excel file.

1-If a part is discontinued, it cannot be ordered.

2. If a part is not discontinued, is a high-demand part and is currently stocked at less than 75% capacity then its time to order more.

3. If a part is not discontinued, it not a high-demand part and is currently stocked at less than 50% capacity then its time to order more.

View 5 Replies View Related

Wildcards In Substitute Function

Jul 10, 2007

I have numeric values in approx 1000 cell entries that I need to edit:

example cell C1 contains the value 00100300308W400

I'm trying to edit this entry to show 00/10-03-003-08W4/0.

I've tried using wildcards with the SUBSTITUTE function:

View 10 Replies View Related

COUNTIF Function After SUBSTITUTE Function

Jan 3, 2012

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?

View 9 Replies View Related

SUBSTITUTE Function To Replace Commas

Mar 2, 2007

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
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.

View 12 Replies View Related

SUBSTITUTE Function With Wildcard For Symbols?

Sep 30, 2011

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.

This is the formula:

(C6,"/"," "),"%"," "),"!"," "),","," "),"*"," "),"-"," "),"("," "),")"," ")

It works thus far, but I would still like to add more symbols into the nest. Is there a way/formula to do this that replaces ANY symbol with a space?

View 9 Replies View Related

Substitute Function Non-case Sensitive

Nov 2, 2006

is it possible to make a SUBSTITUTE finction non- case sensitive?

For example I want to replace all letters "e" and "E" in a cell.

View 3 Replies View Related

Substitute Function With Find, Mid & Left

May 25, 2007

Got the following formula:-

=LEFT(L5, FIND("(",L5)-1)

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").

View 4 Replies View Related

Substitute Function Giving TEXT Output?

Oct 2, 2012

I've in cell A1 an entry like this: 123*456*7890

=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?

View 2 Replies View Related

VB Alternative To If Function?

Jun 4, 2009

I use a spreadsheet to keep track of stock at work.
The invoices are put into rows with the stock code and qty next to each other.

Invoice No | Stock Code | Qty | Stock Code | Qty | Stock Code | Qty | Stock Code | Qty ect..

I am currently using an If function to check all the cells for an instance of each stock item in a total of four columns.

View 3 Replies View Related

Indirect Function And Substitute In Order To Create Conditional Dropdown Menus

Jun 26, 2014

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.

View 6 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved