Embedded If/Then Formula

[Mackey00]

Hello all,

I'm having an issue with an embedded IF/THEN Formula which has three different IFs. Every values in the L column will change. The different choices are:

No quotes, I'm putting them there to show the spaces before or after the value.

1. "# %not " (# being a single number)

2. ST00

3. ST## (## being two or four digits, from 0001 to 95)

3. ##PERORDER (## being two or three numbers)

That leaves at least four possible choices. In the sheet below, rows 3,4, and 5 are correct. I need help with rows 6 and 7.

Q6 should be 900 from (.0009 * 1,000,000)
Q7 should be 7,500 from (.25 * 30,000)

Data

*	L	M	N	O	P	Q
3 %not	ABC
50PERORDER	ABC
ST00	ABC
ST0009	ABC	*
ST25	ABC	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:48px;"><col style="width:48px;"><col style="width:64.8px;"><col style="width:61.6px;"><col style="width:82.4px;"><col style="width:54.4px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]10000[/TD]
[TD="align: right"]0.300000[/TD]
[TD="align: right"]$3,000.00[/TD]
[TD="align: center"]90.00[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]2000[/TD]
[TD="align: right"]0.295000[/TD]
[TD="align: right"]$590.00[/TD]
[TD="align: center"]50[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]25000[/TD]
[TD="align: right"]0.295000[/TD]
[TD="align: right"]$7,375.00[/TD]
[TD="align: center"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]1000000[/TD]
[TD="align: right"]0.300000[/TD]
[TD="align: right"]$300,000.00[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]30000[/TD]
[TD="align: right"]0.295000[/TD]
[TD="align: right"]$8,850.00[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula P3 =N3*O3 Q3 =IFERROR(IF(L3="ST00",0,IF(ISNUMBER(FIND("PERORDER",L3,1)),LEFT(L3,LEN(L3)-8),LEFT(L3,LEN(L3)-6)/(100)*(P3))),"") P4 =N4*O4 Q4 =IFERROR(IF(L4="ST00",0,IF(ISNUMBER(FIND("PERORDER",L4,1)),LEFT(L4,LEN(L4)-8),LEFT(L4,LEN(L4)-6)/(100)*(P4))),"") P5 =N5*O5 Q5 =IFERROR(IF(L5="ST00",0,IF(ISNUMBER(FIND("PERORDER",L5,1)),LEFT(L5,LEN(L5)-8),LEFT(L5,LEN(L5)-6)/(100)*(P5))),"") P6 =N6*O6 Q6 =IFERROR(IF(L6="ST00",0,IF(ISNUMBER(FIND("PERORDER",L6,1)),LEFT(L6,LEN(L6)-8),LEFT(L6,LEN(L6)-6)/(100)*(P6))),"") P7 =N7*O7 Q7 =IFERROR(IF(L7="ST00",0,IF(ISNUMBER(FIND("PERORDER",L7,1)),LEFT(L7,LEN(L7)-8),LEFT(L7,LEN(L7)-6)/(100)*(P7))),"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[FDibbins]

Unless I have missed it, you have told us what you have, but not what you are trying to do?

 

[Mackey00]

Sorry I can see I wasn't very clear. I need help if the value in the L column is ST and any number except 00. Eg. ST0009, ST05, ST015 are all posible choices that will change the values in the Q column.

 

[jtakw]

Hi,

Yeah, your description was a little hard to follow...but I think this is what you want:

Excel 2010
	L	M	N	O	P	Q
3	3 %not	ABC	10000	0.3	$3,000.00	90
4	50PERORDER	ABC	2000	0.295	$590.00	50
5	ST00	ABC	25000	0.295	$7,375.00	0
6	ST0009	ABC	1000000	0.3	$300,000.00	900
7	ST25	ABC	30000	0.295	$8,850.00	7500
Sheet1

Cell Formulas
Range	Formula
Q3	=IFERROR(IF(L3="ST00",0,IF(ISNUMBER(FIND("%",L3)),LEFT(L3,1)*P3/100,IF(LEFT(L3,2)="ST",("."&RIGHT(L3,LEN(L3)-2))*N3,IF(ISNUMBER(FIND("PERORDER",L3,1)),LEFT(L3,LEN(L3)-8),LEFT(L3,LEN(L3)-6)/(100)*(P3))))),"")

Q3 formula copied down.

 

[Mackey00]

Thank you very much! The formula looks great. I see how you did that now.

 

[jtakw]

Thank you very much! The formula looks great. I see how you did that now.

You're welcome.

Actually, I just realized I added the test for "# %not " (# being a single number) in my formula, but you already have that at the end, so I took my part out and modified the end of your original formula.

This is an updated version:

Excel 2010
	L	M	N	O	P	Q
3	3 %not	ABC	10000	0.3	$3,000.00	90
4	50PERORDER	ABC	2000	0.295	$590.00	50
5	ST00	ABC	25000	0.295	$7,375.00	0
6	ST0009	ABC	1000000	0.3	$300,000.00	900
7	ST25	ABC	30000	0.295	$8,850.00	7500
Sheet1

Cell Formulas
Range	Formula
Q3	=IFERROR(IF(L3="ST00",0,IF(LEFT(L3,2)="ST",("."&RIGHT(L3,LEN(L3)-2))*N3,IF(ISNUMBER(FIND("PERORDER",L3,1)),LEFT(L3,LEN(L3)-8),LEFT(L3,1)/(100)*(P3)))),"")

 

[Mackey00]

I noticed that last night and couldn't see why the my formula was in there twice, but I figured it would work, lol. Thank you for the correction.

 

[jtakw]

You're welcome.