Trouble Creating Nested If/Then/And Statement

[rlw989]

I have a value that is calculated in Cell B9. This value is then used in an equation, with the chosen equation being based off the value in Cell B9.

Is it possible to combine all this into a single statement, so that this is calculated automatically for me? I have to do this about 200 times, each time with a different value in cell B9, and thus the potential for it to use a different one of the equations above each time.

Here are the actual equations I am trying to put into a statement:

If B9 < 1, use B9
If 1 < B9 < 1.2 Cs=(B9-1)x.05+1.12 
If 1.2 < B9 < 1.4 Cs=(B9-1.2)x.1+1.13 
If 1.4 < B9 < 1.6 Cs=(B9-1.4)x.05+1.15 
If 1.6 < B9 < 1.8 Cs=(B9-1.6)x.1+1.16 
If 1.8 < B9 < 2 Cs=(B9-1.8)x.05+1.18 
If 2 < B9 < 5 Cs=(B9-2)x.00333+1.19 
If 5 < B9 < 10 Cs=(B9-5)x.006+1.20 
If 10 < B9  Cs=(B9-10)x.014+1.23

Ultimately I am after the Cs value, and as you can see if can vary based upon what is in cell B9.

 

[MrKowz]

Try:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">1.7</td><td style="text-align: right;;">1.21</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C9</th><td style="text-align:left">=IF(<font color="Blue">B9<1,B9,(<font color="Red">B9-LOOKUP(<font color="Green">B9,{1,1.2,1.4,1.6,1.8,2,5,10}</font>)</font>)*0.5+LOOKUP(<font color="Red">B9,{1,1.2,1.4,1.6,1.8,2,5,10},{1.12,1.13,1.15,1.16,1.18,1.19,1.2,1.23}</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Jonmo1]

Welcome to the board.

Try

=CHOOSE(MATCH(B9,{0,1,1.2,1.4,1.6,1.8,2,5,10}),B9,(B9-1)*0.05+1.12,(B9-1.2)*0.1+1.13,(B9-1.4)*0.05+1.15,(B9-1.6)*0.1+1.16,(B9-1.8)*0.05+1.18,(B9-2)*0.00333+1.19,(B9-5)*0.006+1.2,(B9-10)*0.014+1.23)

 

[rlw989]

In the equation you have 0.05 held as a constant, but it changes in each version of the equation.. I think this will work, I don’t fully understand it, but I am not sure what to edit to make the 0.05 change with each equation.

 

[rlw989]

Thank you both.. Its working. I would have never thought of that statement to make it work.

 

[MrKowz]

In the equation you have 0.05 held as a constant, but it changes in each version of the equation.. I think this will work, I don’t fully understand it, but I am not sure what to edit to make the 0.05 change with each equation.

Ahh, I didn't notice that part changing. Try:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">1.7</td><td style="text-align: right;;">1.26</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C9</th><td style="text-align:left">=IF(<font color="Blue">B9<1,B9,(<font color="Red">B9-LOOKUP(<font color="Green">B9,{1,1.2,1.4,1.6,1.8,2,5,10}</font>)</font>)*LOOKUP(<font color="Red">B9,{1,1.2,1.4,1.6,1.8,2,5,10},{0.05,0.1,0.05,1,0.05,0.003333,0.006,0.014}</font>)+LOOKUP(<font color="Red">B9,{1,1.2,1.4,1.6,1.8,2,5,10},{1.12,1.13,1.15,1.16,1.18,1.19,1.2,1.23}</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

(Although Jonmo's solution is much better, as it only has to use the match once)

 

[Jonmo1]

Glad to help out.

Just commentary here...

This has to be the wierdest formula I've ever done.
I can't even begin to understand the logic of the math it's doing..

What is it's purpose?

 

[snoopyhr]

or maybe as an alternative this :

Excel Workbook
	B	C	D
8		CS value	Lookup table can be placed anywhere
9	1	1.12	0
10			1
11			1.2
12			1.4
13			1.6
14			1.8
15			2
16			5
17			10
Sheet3 (2)

The lookup table can be place anywhere and then can be made hidded if desired.