Require correct syntax of Single Formula with Few (Conditions / Criteria) using =IF(ISNUMBER(Search.....)

[SamDsouza]

Hello

if one could help for incorporating single formula in a cell with few conditions using ISNUMBER(SEARCH)

1. Condtion where the IF cell C16 Contains "Add" and value of cell D16 is text of numerical value = "20" then cell E16 = "0.00"
2. Condtion where the IF cell C16 Contains "As is" and value of cell D16 is text of numerical value = "20" then cell E16 = "20"
3. Condtion where the IF cell C16 Contains "As is" and value of cell D16 is text = "Free" then cell E16 = "0.00"
4. Condtion where the IF cell C16 Contains "Add" and value of cell D16 is text = "Free" then cell E16 = "0.00"

How to satisfy the above 4 condtions with single syntax of correct formula using ISNumber Search
I did try for 1 condtion

=IF(ISNUMBER(SEARCH("Add",$C16)),"0.00",$D16)

but was not able to incorporate the other 3 conditions with above formula

Your help will be really appreciated

SamD
151

 

[jasonb75]

What if none of the 4 conditions are met, Would that be 0.00 as well? If so then how about

=IF(AND(ISNUMBER(SEARCH("As is",C16)),D16=20),20,0)

BTW, "20" is not numeric, 20 is (no double quotes).

 

[SamDsouza]

Jasonb75

BTW, "20" is not numeric, 20 is (no double quotes).

infact 20 is without double quotes. in cell C16. The double quote in thread was to hilite. But in formula "0.00" which is mentioned is to rather to display "0.00"
FYI
Column E is Formatted with Decimal places 2 eventhough in formula is typed as "0.00"
Column C and D are not formatted with anything

=IF(AND(ISNUMBER(SEARCH("As is",C16)),D16=20),20,0)

Don't you think this will leave with values 20 in column D with all respetive rows where formula dragged and copied

if Value in Cell C16 "As is" then Cell D16 is for eg 3 or 25 or 20 then Value of E16 could be 3.00 or 25.00 or 20.00 as this depends on value of Column D and respective row. But if Value in Cell C16 "As is" and Cell D16 value is = Free then cell E16 = 0.00

To be specific
Values in Column C may have either "Add" or "As is"
Values in column D may have either "Free" or Numerical Value
so on the basis of 4 conditions. I want to derive the result in column E wherein in column E the formula is typed

Hope this clarifies

SamD
152

 

[jasonb75]

if Value in Cell C16 "As is" then Cell D16 is for eg 3 or 25 or 20 then Value of E16 could be 3.00 or 25.00 or 20.00 as this depends on value of Column D and respective row.

Referring back to condition 2,

value of cell D16 is text of numerical value = "20"

The phrasing is more than a little contradictive, text and numerical are two completely different things in excel which is why I pointed out that "20" is not numeric. The way that your post was phrased implied that the cell contained "20" which was the only valid content and that anything else in D16 ("3","5","10",etc) should return 0.00. The formula that I suggested was based on the direction that the criteria appeared to be aiming.

Based on what you now say, the correct condition 2 would be
2. Condtion where the IF cell C16 Contains "As is" and value of cell D16 a numerical value then cell E16 should show the value in D16

Using the information that you have now added, I'm going to suggest

=IF($D16="Free","0.00",IF($C16="Add",0,$D16))

Which I suspect will also be wrong, but it is what you have asked for.

Values in Column C may have either "Add" or "As is"
Values in column D may have either "Free" or Numerical Value

Implies that all rows will meet one of the 4 criteria, so I have not included a result for rows that do not meet one of the conditions.
Also the wording suggests that the text criteria will be the entire content of the cell rather than part of it. Based on this I have omitted the isnumber and search functions as they are not needed to match the entire content of the cell, only part of it.

 

[SamDsouza]

Workable. Thank you so much

=IF($D16="Free","0.00",IF($C16="Add",0,$D16))

As using IF statement in worksheet for First time ie why confussed
To understand above syntax i have broken as below to understand clearly

If D16 = "Free" And C16 ="Add" Then           
       E16 = "0.00"  
     Else                        
       E16 = D16         
End if

Any website links you would recommend for me to understand IF Statements incorpoarting in worksheets as formulas

Where and in which type of criteria or condtion We could have used
=IF(ISNUMBER(Search.....)


SamD
153

 

[jasonb75]

Using your method of breaking it down, it's actually

If D16 = "Free" Then
    E16 = "0.00"
Else
    If C16 = "Add" Then
        E16 = D16
    Else
        E16 = "0.00"
    End If
End If

When D16 contians "Free" the result is "0.00" regardless of what is in C16. This is dealt with by the first IF, as conditions 3 and 4 both have the same outcome there is no need to check if C16 is "Add" or "As is"

When D16 is not "Free" then we assume that it must be a number, the first IF returns false and passed onto the second one. As there are only 2 options for C16 only one of them needs to be checked then the result is shown based on that.

Regarding ISNUMBER(SEARCH(...)) you would only need that if you were searching in part of a longer string in the cell, for example finding "Sam" in a cell that contained "SamDsouza"

For trying to understand how they work, the best thing might be to look at some other threads in the forum and see how other people have used the functions.

 

[SamDsouza]

Thank you JasonB75 for lovely explanation and correcting the breaking down logic

Thanks Once More

SamD
154