HELP please! IF function for multible different numbers

martijnvanderveldt

New Member
Joined
Sep 10, 2014
Messages
13
Hi!

I want to try to use the IF function to make a car option with both positive and negative attributes out of 12 ratings a participant gives about these 12 attributes. For the car i want the following ratings to be positive: 1,3,4,6,7,8,10,12, whereas the others inbetween are supposed to be negative (2,5,9,11) . I made the following formula, but it does not seem to do what i want:

=IF(A2=1+AND(3,4,6,7,8,10,12),"Has good mileage","has poor mileage")

There is something wrong in the formula i just don't know what it is. the OR function also does not seem to work. Does anyone have a clue how i can make these ratings the positive ones while the other onesare negtive?

I really apreciate the help
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,037
Office Version
  1. 365
Platform
  1. Windows
I hope my formula is not an overkill :)

Do you need Something like this

<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 width="25px" 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 style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">3</td><td style=";">Good Mileage</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">12</td><td style=";">Good Mileage</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">9</td><td style=";">Poor Mileage</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">11</td><td style=";">Poor Mileage</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">Sheet8</p><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: #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">B6</th><td style="text-align:left">=IFERROR(<font color="Blue">IF(<font color="Red">MATCH(<font color="Green">A6,{1,3,4,6,7,8,10,12},0</font>)>0,"Good Mileage"</font>),""</font>) & IFERROR(<font color="Blue">IF(<font color="Red">MATCH(<font color="Green">A6,{2,5,9,11},0</font>)>0,"Poor Mileage"</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />
 

martijnvanderveldt

New Member
Joined
Sep 10, 2014
Messages
13
Your formula does not seem to work in My excel (2010 Holland), but i found a working formula!

=IF(ISERROR(MATCH(A2;{2;5;9;11};0));"Has good mileage";"Has poor mileage")

If i have any more questions i will let you know. Thank you so much!
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,037
Office Version
  1. 365
Platform
  1. Windows
My formula would have been shorter but I didnt want to assume that anything not 2,5,9,11 would be bad, 'cos some naughty person could put a value greater than 12

Maybe the formula not working has something to do with colon(;) being your separator while comma(,) being mine. Just guessing
 

Watch MrExcel Video

Forum statistics

Threads
1,108,991
Messages
5,526,099
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top