Need Help in Combining "IF" Formula

[Sampat]

Hi,

I have some Report IDs like- 101-121, Invalids, Overdue, MDD.
Each of them have ageing & accordingly Alarm Status is decided.

For 101-121,
Ageing greater than 60- Red
Ageing greater than 50- Amber
Ageing less than 50- Green.

For Invalids & Overdue,
Ageing greater than 55- Red
Ageing greater than 42- Amber
Ageing less than 42- Green.

For MDD,
Ageing greater than 70- Red
Ageing greater than 60- Amber
Ageing less than 60- Green.

I have created formula-

101-121:

=IF(**>60,"Red",IF(**>50,"Amber","Green"))

Invalids-Overdue:

=IF(**>55,"Red",IF(**>42,"Amber","Green"))

MDD

=IF(**>70,"Red",IF(**>60,"Amber","Green"))

Where I replace ** with the cell no. containing Ageing.

The challenge is that, I have to select each report ID to put the formula. Can this be combined?

Thus, My G column contains the Report ID(101-121, Invalids, Overdue, MDD), Q Column contains ageing(0-200), S should show the alarm (Red, Amber, Green), i.e. the above three formulas should be combined.

Here is a small screenshot.

Thanks for your time!

 

[repairman615]

Hello,

If I understand correctly, this may do:

=IF(AND(G1>=101,G1<=121),LOOKUP(Q1,{0,50,60},{"Green","Amber","Red"}),IF(OR(G1={"Invalids","Overdue"}),LOOKUP(Q1,{0,42,55},{"Green","Amber","Red"}),IF(G1="MDD",LOOKUP(Q1,{0,60,70},{"Green","Amber","Red"}),"---")))

 

[Sampat]

Hi,

Thanks a ton for your reply. The formula is working fine for most of the cases.

But have a look in the highlighted row below-

It is "Invalids" report ID with Ageing 59 (<55) So, Status should be "Red", But it is showing Green!

Thanks again for helping me.

 

[Peter_SSs]

Sampat

The problem with the images you are posting is that the data cannot be copied to a worksheet for test. Many potential helpers will just bypass your thread because they don't want to spend the time typing out all that sample data.

Consider trying to post a screen shot directly here in your thread using one of the methods shown in my signature block. That way the data can be copied and you will likely get many more helpers.

 

[WinterRevole]

Hi, I may have an alternative solution. Looks a bit cumbersome though.

[Column A is report number, column B is Ageing]

IF(AND(A4>=101,A4<=121),IF(B4>=60,"Red",IF(B4>=50,"Amber","Green")),"XXX")
The line above is for report number 101-121, then,
substitude "XXX" with the next row:

IF(OR(A4="Invalids",A4="Overdue"),IF(B4>=55,"Red",IF(B4>=42,"Amber","Green")),"YYY")
which is for Invalids and Overdue, then,
substitude "YYY" with the next row again:

IF(A4="MDD",IF(B4>=70,"Red",IF(B4>=60,"Amber","Green")),"ERROR")
which is for MDD.

The final result should look like this:
=IF(AND(A4>=101,A4<=121),IF(B4>=60,"Red",IF(B4>=50,"Amber","Green")),IF(OR(A4="Invalids",A4="Overdue"),IF(B4>=55,"Red",IF(B4>=42,"Amber","Green")),IF(A4="MDD",IF(B4>=70,"Red",IF(B4>=60,"Amber","Green")),"ERROR")))

 

[markmzz]

Or try this (in S2):

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>G</TH><TH>H</TH><TH>P</TH><TH>Q</TH><TH>R</TH><TH>S</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="FONT-WEIGHT: bold">Report Number</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold">Ageing</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold">Alarm status</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>Overdue</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">98</TD><TD style="TEXT-ALIGN: right"></TD><TD>Red</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>Overdue</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">62</TD><TD style="TEXT-ALIGN: right"></TD><TD>Red</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right">102</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right"></TD><TD>Green</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>Invalids</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">85</TD><TD style="TEXT-ALIGN: right"></TD><TD>Red</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>MDD</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">93</TD><TD style="TEXT-ALIGN: right"></TD><TD>Red</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD>Invalids</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right"></TD><TD>Green</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD>Invalids</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">19</TD><TD style="TEXT-ALIGN: right"></TD><TD>Green</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD>Invalids</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">97</TD><TD style="TEXT-ALIGN: right"></TD><TD>Red</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right">107</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">55</TD><TD style="TEXT-ALIGN: right"></TD><TD>Amber</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: right">112</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">67</TD><TD style="TEXT-ALIGN: right"></TD><TD>Red</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD>Overdue</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">48</TD><TD style="TEXT-ALIGN: right"></TD><TD>Amber</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="TEXT-ALIGN: right">102</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">29</TD><TD style="TEXT-ALIGN: right"></TD><TD>Green</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD>MDD</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">99</TD><TD style="TEXT-ALIGN: right"></TD><TD>Red</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD>Invalids</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right"></TD><TD>Green</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD>Overdue</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">11</TD><TD style="TEXT-ALIGN: right"></TD><TD>Green</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD style="TEXT-ALIGN: right">119</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">24</TD><TD style="TEXT-ALIGN: right"></TD><TD>Green</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD>Invalids</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">47</TD><TD style="TEXT-ALIGN: right"></TD><TD>Amber</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">19</TD><TD>Overdue</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">62</TD><TD style="TEXT-ALIGN: right"></TD><TD>Red</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">20</TD><TD>Invalids</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">57</TD><TD style="TEXT-ALIGN: right"></TD><TD>Red</TD></TR></TBODY></TABLE>

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>G2</TH><TD style="TEXT-ALIGN: left">=CHOOSE(RANDBETWEEN(1,4),"Invalids","Overdue","MDD",RANDBETWEEN(101,121))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>Q2</TH><TD style="TEXT-ALIGN: left">=RANDBETWEEN(0,99)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>S2</TH><TD style="TEXT-ALIGN: left">=IF((G2="Overdue")+(G2="Invalids"),LOOKUP(Q2,{0,"Green";42,"Amber";55,"Red"}),
IF(G2="MDD",LOOKUP(Q2,{0,"Green";60,"Amber";70,"Red"}),
IF((G2<=121)*(G2>=101),LOOKUP(Q2,{0,"Green";50,"Amber";60,"Red"}),"Invalid Data")))


</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Markmzz

 

[Peter_SSs]

It is unclear what to do at the borders. For example, for the section below you haven't clarified what the result should be if ageing is exactly 50. Can you also clarify what should happen if ageing is exactly 60 (your table would indicate Amber but clarification would be good).

For 101-121,
Ageing greater than 60- Red
Ageing greater than 50- Amber
Ageing less than 50- Green.

Assuming ageing is always whole numbers, perhaps you could build a lookup table something like this and keep the formula a bit simpler.

May have to tweak the table depending on ..
a) My assumption about whole numbers, and
b) The border issue raised above
.. but I think this should go close.

Formula in S2 copied down.

Excel Workbook
	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Report Number										Ageing		Alarm status			Green	Amber	Red
2	Overdue										98		Red		101	0	51	61
3	Overdue										62		Red		Invalids	0	43	56
4	102										6		Green		MDD	0	61	71
5	Invalids										85		Red		Overdue	0	43	56
6	MDD										93		Red
7	Invalids										10		Green
8	Invalids										19		Green
9	Invalids										97		Red
10	107										55		Amber
11	112										67		Red
12	Overdue										48		Amber
13	102										29		Green
14	MDD										99		Red
15	Invalids										2		Green
16	Overdue										11		Green
17	119										24		Green
18	Invalids										47		Amber
19	Overdue										62		Red
20	Invalids										57		Red
21	120										60		Amber
22	120										50		Green
Lookup Table

 

[Sampat]

Or try this (in S2):

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>G</TH><TH>H</TH><TH>P</TH><TH>Q</TH><TH>R</TH><TH>S</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="FONT-WEIGHT: bold">Report Number</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold">Ageing</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold">Alarm status</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>Overdue</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">98</TD><TD style="TEXT-ALIGN: right"></TD><TD>Red</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>Overdue</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">62</TD><TD style="TEXT-ALIGN: right"></TD><TD>Red</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right">102</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right"></TD><TD>Green</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>Invalids</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">85</TD><TD style="TEXT-ALIGN: right"></TD><TD>Red</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>MDD</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">93</TD><TD style="TEXT-ALIGN: right"></TD><TD>Red</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD>Invalids</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right"></TD><TD>Green</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD>Invalids</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">19</TD><TD style="TEXT-ALIGN: right"></TD><TD>Green</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD>Invalids</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">97</TD><TD style="TEXT-ALIGN: right"></TD><TD>Red</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right">107</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">55</TD><TD style="TEXT-ALIGN: right"></TD><TD>Amber</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: right">112</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">67</TD><TD style="TEXT-ALIGN: right"></TD><TD>Red</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD>Overdue</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">48</TD><TD style="TEXT-ALIGN: right"></TD><TD>Amber</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="TEXT-ALIGN: right">102</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">29</TD><TD style="TEXT-ALIGN: right"></TD><TD>Green</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD>MDD</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">99</TD><TD style="TEXT-ALIGN: right"></TD><TD>Red</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD>Invalids</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right"></TD><TD>Green</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD>Overdue</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">11</TD><TD style="TEXT-ALIGN: right"></TD><TD>Green</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD style="TEXT-ALIGN: right">119</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">24</TD><TD style="TEXT-ALIGN: right"></TD><TD>Green</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD>Invalids</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">47</TD><TD style="TEXT-ALIGN: right"></TD><TD>Amber</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">19</TD><TD>Overdue</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">62</TD><TD style="TEXT-ALIGN: right"></TD><TD>Red</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">20</TD><TD>Invalids</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">57</TD><TD style="TEXT-ALIGN: right"></TD><TD>Red</TD></TR></TBODY></TABLE>

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>G2</TH><TD style="TEXT-ALIGN: left">=CHOOSE(RANDBETWEEN(1,4),"Invalids","Overdue","MDD",RANDBETWEEN(101,121))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>Q2</TH><TD style="TEXT-ALIGN: left">=RANDBETWEEN(0,99)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>S2</TH><TD style="TEXT-ALIGN: left">=IF((G2="Overdue")+(G2="Invalids"),LOOKUP(Q2,{0,"Green";42,"Amber";55,"Red"}),
IF(G2="MDD",LOOKUP(Q2,{0,"Green";60,"Amber";70,"Red"}),
IF((G2<=121)*(G2>=101),LOOKUP(Q2,{0,"Green";50,"Amber";60,"Red"}),"Invalid Data")))


</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Markmzz

Markmzz - Marvelous!

Thank You soo very Much. U don't have any idea how much time (which I required to select each report ID & paste formula .. Day after Day) You saved!

Keep up the Good Work, Sir!

 

[Sampat]

Hi, I may have an alternative solution. Looks a bit cumbersome though.

[Column A is report number, column B is Ageing]

IF(AND(A4>=101,A4<=121),IF(B4>=60,"Red",IF(B4>=50,"Amber","Green")),"XXX")
The line above is for report number 101-121, then,
substitude "XXX" with the next row:

IF(OR(A4="Invalids",A4="Overdue"),IF(B4>=55,"Red",IF(B4>=42,"Amber","Green")),"YYY")
which is for Invalids and Overdue, then,
substitude "YYY" with the next row again:

IF(A4="MDD",IF(B4>=70,"Red",IF(B4>=60,"Amber","Green")),"ERROR")
which is for MDD.

The final result should look like this:
=IF(AND(A4>=101,A4<=121),IF(B4>=60,"Red",IF(B4>=50,"Amber","Green")),IF(OR(A4="Invalids",A4="Overdue"),IF(B4>=55,"Red",IF(B4>=42,"Amber","Green")),IF(A4="MDD",IF(B4>=70,"Red",IF(B4>=60,"Amber","Green")),"ERROR")))

Thank You Buddy! It is really very helpful!

Amazed to have soo many talented members here, who really cares for others!

 

[repairman615]

Hello,

It works for me with the input data Invaladis - 59.

It looks as if your formula is 1 row off.
The cell you have selected is in row 283... the formula is looking at row 282. It is in the wrong row.

-Jeff