Nested IF formula Nightmare

[Rana Gray]

Hey Everyone,

I have gotten most of my formula to work but I need to add one more condition and I can't seem to figure it out. Here is what I have so far (that I've tested and works): =IF(E16="*Mortgage*",5.25%,IF(F38>5.25%,SUM(F38+2%),5.25%))*OR(IF(E16="*Mortgage*",5.25%,IF(F38>5.25%,SUM(F38+2%),5.25%)))

What I need to add is: If E16="<>*Mortgage*" and/or "<>*Homeowner*" Then use value in cell F38 as is.

As always thank you in advance <3

 

[awoohaw]

Rana,
is it possible you can post a mini workbook using the xl2bb add in? (Link below)? Or at least post a table?

 

[awoohaw]

in your original formula can you explain what this does:
OR(IF(E16="*Mortgage*",5.25%,IF(F38>5.25%,SUM(F38+2%),5.25%)))
as far as I can tell there is only one logical statement to compare for the OR function, so that function is essentially this:
IF(E16="*Mortgage*",5.25%,IF(F38>5.25%,SUM(F38+2%),5.25%))

actually it looks like the statements before and after the *OR are identical.

 

[Rana Gray]

in your original formula can you explain what this does:
OR(IF(E16="*Mortgage*",5.25%,IF(F38>5.25%,SUM(F38+2%),5.25%)))
as far as I can tell there is only one logical statement to compare for the OR function, so that function is essentially this:
IF(E16="*Mortgage*",5.25%,IF(F38>5.25%,SUM(F38+2%),5.25%))

actually it looks like the statements before and after the *OR are identical.

Hey hun I apologize I wrote the second criteria incorrectly - this is the actual sheet:

 

[Rana Gray]

I just realized a flaw in my formula will update shortly

 

[Dave Patton]

Please explain what happens if the test is true and what happens if it is false
You do not need the Sum and if the formula is the same for both Mortgage and HomeOwner see below.

Consider the following

 

[Rana Gray]

in your original formula can you explain what this does:
OR(IF(E16="*Mortgage*",5.25%,IF(F38>5.25%,SUM(F38+2%),5.25%)))
as far as I can tell there is only one logical statement to compare for the OR function, so that function is essentially this:
IF(E16="*Mortgage*",5.25%,IF(F38>5.25%,SUM(F38+2%),5.25%))

actually it looks like the statements before and after the *OR are identical.

=IF($E$16="*Mortgage*",5.25%,IF(SUM($F$26+2%)>5.25%,SUM($F$26+2%),5.25%))*OR(IF($E$16="*Homeowner*",5.25%,IF(SUM($F$26+2%)>5.25%,SUM($F$26+2%),5.25%)))

so new sheet for reference basically if "Product Name" contains "Mortgage" or "Homeowner" the 'Qualifying Rate' should be 5.25% of 'Customer Rate' + 2% whichever is higher. But for all other Products just use the 'Customer Rate'

I hope that makes sense haha

 

[Dave Patton]

T202303a.xlsm
	D	E
16		x
17
4d

Cell Formulas
Range	Formula
D16	D16	=IF(OR(E16={"*Mortgage*","*HomeOwner*"}),IF(F38>0.0525,F38+0.02,0.0525),"")

 

[Dave Patton]

It will help if you post your sheet with the forum's too named XL2BB.

explain what happens with each If
true part
false part

What works with your existing formula?

T202303a.xlsm
	D	E
16	5.25%	Mortgage
17
4d

Cell Formulas
Range	Formula
D16	D16	=IF(OR(E16={"Mortgage","HomeOwner"}),IF(F38>0.0525,F38+0.02,0.0525),"")

 

[Dave Patton]

T202303a.xlsm
	D	E	F
16	8.00%	a Homeowner xyz
17
4d

Cell Formulas
Range	Formula
D16	D16	=IF(OR(IFERROR(SEARCH("Mortgage",E16),0),IFERROR(SEARCH("Homeowner",E16),0)),IF(F38>0.0525,F38+0.02,0.0525),"Customer rate")