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


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 View Related

Nested If Error Too Many Variables

Nov 5, 2007

I am receiving an error message for a nested if statement. Excel is telling me my statement contains too many variables.

I am using a VLOOKUP to look up data in a table based upon a result in the first table, however, the entry that is returned is different based upon a unique cell value.

Would anyone have any suggestions on how to shorten this down?


View 9 Replies View Related

Nested If Statements With Multiple Variables

Apr 29, 2013

I am very new at in depth excel formulas, usually in my daily work I can get by with simple summations, if statements etc. This one has thrown me for a loop.

I'm making a spreadsheet on soil compressibility and I feel like I am just knocking my head against the wall. Here is what I am working with. I need to calculate values in a column, we'll call them "Rm" values.

There are a few basic things that dictate which "Rm" equation you use. I'm substituting V,W,X,Y,Z in for the equations to simplify the question.

I = 3 Rm= W*log(K)

View 9 Replies View Related

Nested If Formula/Function With Other Variables

May 14, 2008

I am creating a worksheet and I have a formlua where the value is text. I can make it work standing alone but not with other variables. =IF(E11=S1,0)+(IF(E12=S1,0)+(IF(E13=S1,0.0025))+IF(E14=S1,"Call for Prciing"))

View 2 Replies View Related

Nested IF Formula That Takes Into Effect Many Variables

May 5, 2014

Basically I need a formula that takes into effect many variables. It needs to check a size, thickness, and material, to determine a cost...

1.5 O.D. - 16GA - 304SS = $X


1.5 O.D. - 18 GA - 304SS = $Y

and such.

The problem I have is not only am I not understanding a lot of Excel jargon, the data set is not in perfect "example" conditions.

O.D. is in N:N yet there are blanks between N(x) and N(y), GA is in O:O yet blanks between O(a) and O(b), and so on.

Here is what I have at the moment, it uses a second sheet to derive the cost based on the parameters. Please ignore the first few terms, they are used with this cost lookup to give me my final. There also may be incorrect syntax in this, but since I get a nesting error first, I really don't know.

=V7+(Q7*M7*(IF(N7=2=AND(O7=20)=AND(L7=304),’Steel Tube Cost WIP’!$DD$19,IF(N7=2=AND(O7=20)=AND(L7=316),’SteelTubeCostWIP’!$DD$20,IF(N7=2=AND(O7=20)=AND(L7=2205),
’SteelTubeCostWIP’!$DD$21,IF(N7=2=AND(O7=18)=AND(L7=304),’Steel Tube Cost WIP’!$DD$16,IF(N7=2=AND(O7=18)=AND(L7=316),’SteelTubeCostWIP’!$DD$17,IF(N7=2=AND(O7=18)=AND(L7=2205),
’SteelTubeCostWIP’!$DD$18,IF(N7=2=AND(O7=16)=AND(L7=304),’Steel Tube Cost
[Code] ......

Mentioning LOOKUP, can I even use it? I've attempted many times however it isn't going too well What can I do?

View 2 Replies View Related

Macro To Add Nested Formula To Cell With Variables

Jun 20, 2008

I want the range in the VLOOKUP below to display its actual value - ie "'6 June 08'!$A:$C" in the formula rather than the actual variable name 'strResult'.

Sub PreviousCount()
strDate = "6 June 08"
strColumnRange = "$A:$C"
strResult = "='" & strDate & "'!" & strColumnRange

Dim i As Long
i = Range("A2"). CurrentRegion.Rows.Count
Range("D2:D" & i).FormulaR1C1 = "=IF(RC[-3]="""", ""Column A blank!"", IF(ISNA(VLOOKUP(RC[-3],strResult,3,0)), ""NEW INSTALL"", VLOOKUP(RC[-3],strResult,3,0)))"
End Sub

View 4 Replies View Related

Unzip Code - Works Without Variables, Breaks With Variables...

Feb 5, 2009

Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...

I have the

View 2 Replies View Related

Mid Substitute Formula

Oct 2, 2008

I have data coes that need to be converted, basically need to remove 1st and 12th digits, 12th digit only, or 11th digit. I have built spreadsheet with a mid sub formula to do all 3 separately, but cannot figure out how to combine the formula to do all three.

I am attaching the spreadsheet,

View 11 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

Substitute Apostrophe

Oct 2, 2007

I have a formula who examines if they are the same. The last number at first quote is not a problem (the 45). The problem occurs only and allways when the horse's name have an apostrophe " ' ". In first example apostrophe is different from the usual. If i manually delay it and replace it - type keyboards apostrophe everything works fine. Obviusly its a symbol. I want a small macro for replacing all these symbols at, lets say column B ,where these names are located. How can i use substitute at this case?

View 9 Replies View Related

Substitute For Filesearch

Aug 19, 2008

I understand that Microsoft left Filesearch out of VBA for Office 2007 because it was buggy. Sadly, however, I still need it. It would help if they placed a comment in VBA help that stated that it was discontinued. As it is, they make it look as if Filesearch is still available for use. OK, I'll stop ranting now.

After reading several earlier posts, apparently I have to use the Dir function. I learn visually and cannot figure out how to use the function from the VBA help file since there are no examples. Does anyone have an example of code using the Dir function to insert filenames in an array? Once I see the code, I'm sure I can adapt it to suit my needs.

View 9 Replies View Related

Is There A Substitute Character That Can Be Used

Nov 6, 2008

I have a whole bunch of dates in a column that look like this:

5/01/1998 when it is supposed to be 5/01/2098 is there a character you can use when you are using the find & replace mode i.e.

*/**/20** I know it is not the asterisk but I believe there is some other character that can be used and it won't change any other of the numbers except the 19. I tried reformating and it does'nt work because of the way they sent it to me.

View 9 Replies View Related


Oct 19, 2009

Is there a way to use something like the SUBSTITUTE function when sending an e-mail using a macro?

Here's my ....

View 9 Replies View Related

Formula To Substitute Given Symbols

May 17, 2014

I've got a workbook with multiple formulas e.g.:




which are built by implementing the symbols:

t, S, D, TCh, Ch, Q, TC, Co, i

I'd like to substitute the symbols in following sequence:

t = a
S = b
D = c
TCh = d
Ch = e
Q = f
TC = g
Co = h
i = x


1) Which formula could substitute the symbols automaticly?

2) If there are up to 100 formulas in the workbook in the range of A1:E100,-

would it be possible to to set the substitution for the mentioned range?

See the attachment.

View 5 Replies View Related

Substitute Text String

Jan 11, 2014

I would like to take a string such as R0-H6-D2 and return a number (1-4) based on one of four values for R, H and D in a separate column for each. The attachment should make what I am trying to do clearer.


View 7 Replies View Related

Using Substitute When There Is Text Followed By N Digits

Feb 9, 2009

I have a text field (description) and in the description i have a product code S followed by 7 digits and then in the process of pasting into excel i have lost the space after this code and before the next text. E.g. "Ballpoint pen S1234567With Free Delivery" should be "Ballpoint pen S1234567 With Free Delivery".

I dont know how to say =if("S" followed by 7 numbers,subsitute ..... etc)

I understand how to use IF and substitute. its the 7 numbers part i am stuck on.

I could do it in access with the wildcards but excel is different.

View 14 Replies View Related

Adding Second Substitute Command

Jun 28, 2009

Adding second substitute command
What I need to do is change this:

View 2 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

How To Use CONCATENATE And SUBSTITUTE Formula Together

Nov 17, 2011

Is this possible because when i try to use the Substitute formula my Concatenate formula is doesn't work. This data in cell A1 is linked from another sheet



When I use the Concatenate formula only, it works fine but when i try to add the Substitute to this, it looks like this


I want it to look like this, removing any blank lines within cell A1


Something wrong with the formula? how come it doesn't do both?

View 1 Replies View Related

How To Substitute Cell Value Into Equation

Aug 6, 2012

if i obtain an eqn by plotting a graph in excel, etc,

y = 1.22952117E-06x3 - 1.08197863E-05x2 + 9.97531712E-01x + 3.99014495E-01

and i wish to subsitube a cell value from another sheet into the 'x' in the above eqn and i need to do this 20 times.

View 3 Replies View Related

Fuzzy Substitute ABCD - AC = BD

Jul 9, 2008

LIST1 "long" strings
LIST2 strings with characters from LIST1: the order may differ

the expected result is the "substraction"
all characters form LIST1 which are NOT in LIST2 should appear in LIST3

to make it easier to read, I have put the same string in entire LIST1


View 9 Replies View Related

Substitute Error Values

Jul 14, 2009

I've got a small problem using VLOOKUP. If the function does not find a value, it returns #N/A. I would like it to say "Not Installed" instead.

The same goes for when VLOOKUP returns a zero - I would like it to return "Installed" instead. Since I've got a great number of rows, I would need an efficent way to change this.

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 Names In Existing Formulas

Aug 25, 2009

I've got a bunch of formulas with cell references in them.

I've gone back and assigned names to those cells.

Is there a quick and easy way to update all the formulas with the cell names now that I've assigned them?

View 9 Replies View Related

Using Substitute To Change Text In Variable?

Mar 25, 2014

I have a text variable MtgDate containing "25/03/2014"

I need to produce another text variable (to build into a file name) MtgDate1 with the text "2014-03-25"

I was going to use the Substitute function to replace the "/" with a "-" then Mid to juggle the dd-mm-yyyy to yyyy-mm-dd but at the moment I can't even find the right syntax for the Substitute.

Got as far as:

[Code] .....

but this just sets MtgDate1 as "=Substitute(MtgDate, " / ", " - ") ie reduces the double quotes to single ones.

View 2 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 A Number Into Cell Reference?

Oct 28, 2012

I have the formula in cell A2 =average(A100:A1100). However the range is not always the same so need a formula that will allow me to enter '200' in cell B2 and that will make the formulae in cell A2 calculate =average(A200:A1100). Likewise, if '300' is entered in cell B2 the formula in A2 will calculate =average(A300:A1100).

View 8 Replies View Related

SUBSTITUTE With Conditions - Large Dataset

Jun 16, 2014

I have the following structure in values found within a large dataset

6A-6200 A

I used =SUBSTITUTE(CELL," ","") to remove the space in the first value: Works fine

I am trying to remove the 2nd occurence of the - in the second value: Desired Result 6A-6156A

And how I may be able to complete both within one formula.

View 3 Replies View Related

Replace Or Substitute Inverted Commas With Nothing

Jun 17, 2006

I have a problem to achieve the above subject, as per the attached picture.

View 2 Replies View Related

Copyrights 2005-15, All rights reserved