Multiple IF

reza_doang

Board Regular
Joined
May 31, 2010
Messages
187
Hi All,
i just want to have a shortcut for this formula using if or another function.
in 1 column i have value from 1 - 100. now what i want to achieve, IF value
1-3 = 1
4-6 = 2
7-9 = 3
10-12 = 4
13-15 = 5
until
>100 = 31
this is multiple 3
thanks
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

shemayisroel

Well-known Member
Joined
Sep 11, 2008
Messages
1,867
Something like the below set up should do it

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold">MIN</TD><TD style="FONT-WEIGHT: bold">MAX</TD><TD style="FONT-WEIGHT: bold">RESULT</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">3</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">2</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">9</TD><TD style="TEXT-ALIGN: right">3</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">9</TD><TD style="TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right">4</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right">15</TD><TD style="TEXT-ALIGN: right">5</TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E1</TD><TD>=INDEX(C2:C6,MATCH(D1,A2:A6,1))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
 

reza_doang

Board Regular
Joined
May 31, 2010
Messages
187
thanks for quick response, but this is not what i want.
My data in 1 column, from 1-100, and i need formula to simple work.
my formula is =IF(D6<=3,1,IF(D6<=6,2,IF(D6<=9,3,....IF(D6<=99,33,34)))))))
now i want to simple it?
thanks
 

shemayisroel

Well-known Member
Joined
Sep 11, 2008
Messages
1,867
Not sure how else you could do it without having a similar set up

The last thing you would want to write is lots and lots of IF's.

Sorry how does you data layout look like? Ar eyou saying that you have it like below?

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: right">1-3</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: right">4-6</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: right">7-9</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: right">10-12</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-FAMILY: Verdana">13-15</TD></TR></TBODY></TABLE>
 

reza_doang

Board Regular
Joined
May 31, 2010
Messages
187

ADVERTISEMENT

something like this.
This is only sample, because if i do that, i need multiple if until <=99
So value in Column A, range between 1 until 100, can have duplicate values. Now in column B, if A between 1-3, B have 1, IF A 4-6, B=2 and soon.

Excel Workbook
AB
111
221
331
442
552
662
773
883
993
10104
11114
12124
13135
14145
15155
16166
17176
Sheet1
 
Last edited:

shift-del

Well-known Member
Joined
Aug 28, 2009
Messages
583
Office Version
  1. 365
Platform
  1. Windows
Hello Reza

May be more like this?
<table valign="middle" colspan="3" style="font-family: Calibri; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-size: 11px; font-weight: normal; font-style: normal;" border="1" cellpadding="2" cellspacing="0"><colgroup><col width="28pt"><col width="60pt"><col width="60pt"></colgroup><tbody><tr style="background-color: rgb(250, 250, 250);"><td colspan="3" align="middle">Arbeitsblatt mit dem Namen 'Tabelle2'</td></tr><tr style="background-color: rgb(202, 202, 202);"><td>
</td><td align="middle">A</td><td align="middle">B</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">1</td><td align="right">1</td><td align="right">1</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">2</td><td align="right">2</td><td align="right">1</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">3</td><td align="right">3</td><td align="right">1</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">4</td><td align="right">4</td><td align="right">2</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">5</td><td align="right">5</td><td align="right">2</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">6</td><td align="right">6</td><td align="right">2</td></tr></tbody></table>
<table valign="middle" colspan="2" style="table-layout: auto; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-family: Calibri; font-size: 10px;" border="3" cellpadding="2" cellspacing="0"><colgroup><col width="40pt"><col></colgroup><tbody><tr style="background-color: rgb(238, 170, 170);"><td>Zelle</td><td>Formel</td></tr><tr><td>B1</td><td>=QUOTIENT(A1+2,3)</td></tr></tbody></table><table style="font-family: Arial; font-size: 7pt;"><tbody><tr><td style="color: rgb(51, 51, 51);">Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg</td></tr></tbody></table>
 

shemayisroel

Well-known Member
Joined
Sep 11, 2008
Messages
1,867
Hello Reza

May be more like this?
<TABLE style="FONT-WEIGHT: normal; FONT-SIZE: 11px; COLOR: rgb(0,0,0); FONT-STYLE: normal; FONT-FAMILY: Calibri; BACKGROUND-COLOR: rgb(255,255,255)" cellSpacing=0 cellPadding=2 border=1 colspan="3" valign="middle"><COLGROUP><COL width=28><COL width=60><COL width=60></COLGROUP><TBODY><TR style="BACKGROUND-COLOR: rgb(250,250,250)"><TD align=middle colSpan=3>Arbeitsblatt mit dem Namen 'Tabelle2'</TD></TR><TR style="BACKGROUND-COLOR: rgb(202,202,202)"><TD>

</TD><TD align=middle>A</TD><TD align=middle>B</TD></TR><TR><TD style="BACKGROUND-COLOR: rgb(202,202,202)" align=middle>1</TD><TD align=right>1</TD><TD align=right>1</TD></TR><TR><TD style="BACKGROUND-COLOR: rgb(202,202,202)" align=middle>2</TD><TD align=right>2</TD><TD align=right>1</TD></TR><TR><TD style="BACKGROUND-COLOR: rgb(202,202,202)" align=middle>3</TD><TD align=right>3</TD><TD align=right>1</TD></TR><TR><TD style="BACKGROUND-COLOR: rgb(202,202,202)" align=middle>4</TD><TD align=right>4</TD><TD align=right>2</TD></TR><TR><TD style="BACKGROUND-COLOR: rgb(202,202,202)" align=middle>5</TD><TD align=right>5</TD><TD align=right>2</TD></TR><TR><TD style="BACKGROUND-COLOR: rgb(202,202,202)" align=middle>6</TD><TD align=right>6</TD><TD align=right>2</TD></TR></TBODY></TABLE>
<TABLE style="TABLE-LAYOUT: auto; FONT-SIZE: 10px; COLOR: rgb(0,0,0); FONT-FAMILY: Calibri; BACKGROUND-COLOR: rgb(255,255,255)" cellSpacing=0 cellPadding=2 border=3 colspan="2" valign="middle"><COLGROUP><COL width=40><COL></COLGROUP><TBODY><TR style="BACKGROUND-COLOR: rgb(238,170,170)"><TD>Zelle</TD><TD>Formel</TD></TR><TR><TD>B1</TD><TD>=QUOTIENT(A1+2,3)</TD></TR></TBODY></TABLE><TABLE style="FONT-SIZE: 7pt; FONT-FAMILY: Arial"><TBODY><TR><TD style="COLOR: rgb(51,51,51)">Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg</TD></TR></TBODY></TABLE>

Thanks for this - never seen this function before. Saved for future. Thanks
 
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,163,838
Messages
5,833,921
Members
430,244
Latest member
Ireland1

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