IF Statement, convert...

excelbetter

Board Regular
Joined
Oct 13, 2010
Messages
182
If I have a bunch of numbers in Column A, all between the value of 1 - 10, how can I in Column B covert the # in Column A if the number is a specific number?

For example:

- If the number in column A is a 9 or 10, I want the number in column B to be a 10;

- If the number in column A is a 7 or 8, I want the number in column B to be a 1;

- If the number in column A is 1 through 6, I want the number in column B to be a 0;

Thanks.
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,719
Office Version
  1. 365
Platform
  1. Windows
Try:
Code:
[TABLE="width: 176"]
<colgroup><col width="176"></colgroup><tbody>[TR]
   [TD="width: 176"]=IF(A1<7,0,IF(A1<9,1,10))[/TD]
 [/TR]
</tbody>[/TABLE]
 

vlady

Active Member
Joined
Jan 26, 2012
Messages
413
base on your condition above the if statement should work. post2
another approach is =LOOKUP(A1,{0,7,9},{0,1,10}) ->change the numbers inside the braces to suit.
 

excelbetter

Board Regular
Joined
Oct 13, 2010
Messages
182

ADVERTISEMENT

This is perfect. Thanks for the help!!
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,719
Office Version
  1. 365
Platform
  1. Windows
I would stick with vlady formula, but IF works when I try it.

Excel Workbook
ABC
110
220
330
440
550
660
771
881
9910
101010
11
Sheet1
 

Watch MrExcel Video

Forum statistics

Threads
1,122,694
Messages
5,597,571
Members
414,156
Latest member
WDMix

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