Multiple IF

[reza_doang]

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

 

[shemayisroel]

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]

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]

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]

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
	A	B
1	1	1
2	2	1
3	3	1
4	4	2
5	5	2
6	6	2
7	7	3
8	8	3
9	9	3
10	10	4
11	11	4
12	12	4
13	13	5
14	14	5
15	15	5
16	16	6
17	17	6
Sheet1

 

[shift-del]

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]

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

 

[reza_doang]

nice, work greats.

thanks