IF and AND formula

[makku]

Hi,

I'm working with Excel 2007.

I have the following formula:

=IF(AND(D10>=0,D10<=4),"Don't Benefit € 0 to -4",IF(AND(D10>4,D10<=8),"Don't Benefit € -4 to -8",IF(AND(D10>8,D10<=12),0)))

Basically what the formula is doing is assigning a Spending range - eg. Don't Benefit € 0 to -4, if the spend is between 0 & 4.

What I would like to do it use a customised table what will be easier to maintain. What i mean is, I would like to replace the >=0, <=4, >4, <=8 etc. that are hard-coded in formula and instead I will reference these variables to a table eg:

From To
>=0 <=4
>4 <=8 etc.

Is there a way I can do this?


regards,

 

[Richard Schollar]

Hi

Look up the use of VLOOKUP in Excel Help. Here's an example:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td width="25px" style="color: #161120;text-align: center;">1</td><td width="63px" style=";">-9.90E+307</td><td width="63px" style=";">Undefined</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">2</td><td width="63px" style="text-align: right;;">-0.0001</td><td width="63px" style=";">Undefined</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">3</td><td width="63px" style="text-align: right;;">4</td><td width="120px" style=";">Don't Benefit £ 0 - 4</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">4</td><td width="63px" style="text-align: right;;">8</td><td width="120px" style=";">Don't Benefit £ 4 - 8</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">5</td><td width="63px" style="text-align: right;;">12</td><td width="63px" style="text-align: right;;">0</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">6</td><td width="63px" style="text-align: right;;">100</td><td width="63px" style=";">Undefined</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">7</td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">8</td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">9</td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">10</td><td width="63px" style="text-align: right;;">4</td><td width="120px" style=";">Don't Benefit £ 0 - 4</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">11</td><td width="63px" style="text-align: right;;">10</td><td width="120px" style=";">Don't Benefit £ 4 - 8</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">12</td><td width="63px" style="text-align: right;;">9</td><td width="120px" style=";">Don't Benefit £ 4 - 8</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">13</td><td width="63px" style="text-align: right;;">-1</td><td width="63px" style=";">Undefined</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">14</td><td width="63px" style="text-align: right;;">15</td><td width="63px" style="text-align: right;;">0</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">15</td><td width="63px" style="text-align: right;;">5</td><td width="120px" style=";">Don't Benefit £ 0 - 4</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">16</td><td width="63px" style="text-align: right;;">15</td><td width="63px" style="text-align: right;;">0</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">17</td><td width="63px" style="text-align: right;;">10</td><td width="120px" style=";">Don't Benefit £ 4 - 8</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet2</p><br /><br /><table 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: #BBB"><thead><tr style=" background-color: #DAE7F5;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: #DAE7F5;color: #161120">B10</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A10,$A$1:$B$6,2,TRUE</font>)</td></tr></tbody></table></td></tr></table><br />

 

[makku]

Thanks for your reply.

However, I need to cater for a list of around 146,000 possibly different denominations, therefore i need some kind of range (>0 to <4, >4 to <8 etc.) mechanism in order to group customers.


regards,

 

[Richard Schollar]

You have a grouping mechanism in the example above - in Excel Help, pay particular attention to the 4th argument of VLOOKUP - it is this that gives it a grouping mechanism.

 

[makku]

Hi,

I know what you are saying however the "True" condition gives approximations. I need "accurate" classifications.

For eg. in your example when the amount = 9 the result the vlookup returns is "Don't Benefit £ 4 - 8" which is not the case!


regards,

 

[Gerald Higgins]

I know what you are saying however the "True" condition gives approximations. I need "accurate" classifications.

Not correct.

True will give accurate classifications, not approximate.
But I think Richard has just put the categories in the wrong row.

Try this instead, with the definitions each one row higher.
-9.90E+307...Undefined
-0.00001......Don't benefit 0-4
4.000001......Don't benefit 4-8
8.000001......0
12.000001....Undefined

I've also changed the cutoff levels slightly.

 

[makku]

Thanks,

I got it. You're right you have to shift the rows one up as you said.


Thanks,
Mark