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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Momentman

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

Do you need Something like this


Excel 2010
AB
63Good Mileage
712Good Mileage
89Poor Mileage
911Poor Mileage
Sheet8
Cell Formulas
RangeFormula
B6=IFERROR(IF(MATCH(A6,{1,3,4,6,7,8,10,12},0)>0,"Good Mileage"),"") & IFERROR(IF(MATCH(A6,{2,5,9,11},0)>0,"Poor Mileage"),"")
 

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,142
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,016
Messages
5,834,943
Members
430,327
Latest member
Mr_Himalayan778

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top