latarshac

New Member
I am attempting to get a formula to display a dollar amount of 7 if the Order Number contains the word, "Apple" or "Orange"

This is a formula to calculate a commission based on certain criteria. This is what the formula is currently but I am looking to add the condition to it.

=IFERROR(IF(AND(I8="BONUS",F8="EXPIRED"),SUM(AH8:AL8),IF(OR(F8="NOT-ACTIVE",F8="CHANGED",D8="EXPIRED",D8="PAID IN FULL",D8="REMOVED"),0,IF(OR(B8="BLUE",B8="GREEN",B8="WHITE"),SUM(L8*K8),IF(OR(B8="YELLOW",B8="ORANGE",B8="RED"),15,IF(B8="PURPLE",20,IF(B8="BLACK",10,)))))),0)

This is the formula that I am attempting that is not working

=IFERROR(IF(FIND("APPLE",A8),7,IF(FIND("PEAR",A8),7,IF(AND(I8="BONUS",F8="EXPIRED"),SUM(AH8:AL8),IF(OR(F8="NOT-ACTIVE",F8="CHANGED",D8="EXPIRED",D8="PAID IN FULL",D8="REMOVED"),0,IF(OR(B8="BLUE",B8="GREEN",B8="WHITE"),SUM(L8*K8),IF(OR(B8="YELLOW",B8="ORANGE",B8="RED"),15,IF(B8="PURPLE",20,IF(B8="BLACK",10,)))))))),0)

The portion that is giving an error is:
IFERROR(IF(FIND("APPLE",A8),7,IF(FIND("PEAR",A8),7,

Now, if the cell A8 actually contains the word "APPLE", the formula will give the correct result, but if the cell contains the word "PEAR", it gives a #VALUE!.
So.. the first when the first logical test results in TRUE, then I get the correct result of 7, but if it is FALSE, then the string will not go on to the next logical test, it just gives the #VALUE!

Any help or advice is much appreciated.
LaTarsha

<SCRIPT language=JavaScript src="<a href=" popup.js" colo puremis sun www.interq.or.jp http:></SCRIPT><CENTER><TABLE" target="_blank">http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT><CENTER><TABLE< a>cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" colSpan="14" bgColor="#0c266b"><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left>Microsoft Excel - TEST COMMISSION FORMULA.xlsx</TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: 12.0 : OS = Windows XP </TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" colSpan="14" bgColor="#d4d0c8"><TABLE width="100%" align=center border=0 VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption">(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout</TD><TD vAlign=center align=right><FORM name=formCb264683><INPUT onclick='window.clipboardData.setData("Text",document.formFb321525.sltNb671829.value);' type=button value="Copy Formula" name=btCb384167></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" colSpan="14" bgColor="white"><TABLE border=0><TBODY><TR><FORM name=formFb321525><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb321525.txbFb007341.value = document.formFb321525.sltNb671829.value" name=sltNb671829><OPTION value='=IFERROR(IF(AND(I8="BONUS",F8="EXPIRED"),SUM(AH8:AL8),IF(OR(F8="NOT-ACTIVE",F8="CHANGED",D8="EXPIRED",D8="PAID IN FULL",D8="REMOVED"),0,IF(OR(B8="BLUE",B8="GREEN",B8="WHITE"),SUM(L8*K8),IF(OR(B8="YELLOW",B8="ORANGE",B8="RED"),15,IF(B8="PURPLE",20,IF(B8="BLACK",10,)))))),0)' selected>J8<OPTION value='=IF(FIND("APPLE",A12),7,IF(FIND("PEAR",A12),7,IF(F12="NOT-ACTIVE",0,IF(OR(D12="EXPIRED",D12="REMOVED"),0,IF(OR(B12="BLUE",B12="GREEN"),30,IF(OR(B12="TEAL",B12="GREY",B12="RED"),15,IF(B12="BLACK",20,IF(B12="WHITE",10,))))))))'>J12<OPTION value='=IF(FIND("APPLE",A14),7,IF(FIND("PEAR",A14),7,IF(F14="NOT-ACTIVE",0,IF(OR(D14="EXPIRED",D14="REMOVED"),0,IF(OR(B14="BLUE",B14="GREEN"),30,IF(OR(B14="TEAL",B14="GREY",B14="RED"),15,IF(B14="BLACK",20,IF(B14="WHITE",10,))))))))'>J14</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8>=</TD><TD align=left bgColor=white><INPUT size=80 value='=IFERROR(IF(AND(I8="BONUS",F8="EXPIRED"),SUM(AH8:AL8),IF(OR(F8="NOT-ACTIVE",F8="CHANGED",D8="EXPIRED",D8="PAID IN FULL",D8="REMOVED"),0,IF(OR(B8="BLUE",B8="GREEN",B8="WHITE"),SUM(L8*K8),IF(OR(B8="YELLOW",B8="ORANGE",B8="RED"),15,IF(B8="PURPLE",20,IF(B8="BLACK",10,)))))),0)' name=txbFb007341></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" width="2%" align="middle">
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</CENTER>
</CENTER>

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Domenic

MrExcel MVP
Try replacing...

FIND("APPLE",A8)

with

ISNUMBER(FIND("APPLE",A8))

and

FIND("PEAR",A8)

with

ISNUMBER(FIND("PEAR",A8))

Or, alternatively, replace both with the following...

=IFERROR(IF(OR(ISNUMBER(FIND({"APPLE","PEAR"},A8))),7,IF(AND(I8="BONUS",F8="EXPIRED"),SUM(AH8:AL8),...

Hope this helps!

latarshac

New Member
You are the BEST! Thank you, that worked like a charm

latarshac

New Member
Wonder if you can do me a favor and tell me where I can lookup more information about the { } that you used in that formula. I have never seen it before and really dislike *not* knowing something.. especially about excel!!

Tarsha

Domenic

MrExcel MVP
Have a look at Excel's help file under...

1) Values that do not change in array formulas

2) What an array constant can contain

Hope this helps!

Replies
10
Views
718
Replies
4
Views
2K
Replies
3
Views
1K
Replies
0
Views
533
Replies
5
Views
861

1,191,196
Messages
5,985,226
Members
439,950
Latest member
Xearo96

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browserâ€™s toolbar.
2)Click on the icon in the browserâ€™s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browserâ€™s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browserâ€™s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browserâ€™s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back