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!

 

[Sampat]

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).

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

Peter - I really apologies for not posting hlml maked excel. I have already downloded the Mr. Excel HTML Maker. But inspite of trying a long hours, I was unable to create html file from my Excel 2007.

Many thanks for taking the trouble to create a dummy sheet just to help me. Thank You. Feeling really privileged.

Sorry again for being un-clear. The ageing criteria should be..
For 101-121,
Ageing equal to or greater than 60- Red
Ageing equal to or greater than 50- Amber
Ageing less than 50- Green.

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

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

As per your advice, I will surely try to build lookup table, but I have to study & work more with excel for that. I believe, surely I can do that one day. I will keep trying.

Thank You again for all the helping hands!

 

[Sampat]

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

Jeff - Many thanks for pointing out the mistake!

The reason behind is You have created the formula without being able to test in my work file. What I have posted was an image file. I was unable to create html coded excel. Apologies for that.

In my excel, the row 1 is the naming row. Here lies the mistake. I should have check the row no. well before posting a negative reply. Sorry again.


Thank you for correcting me. Thank you for your time & care.

Keep up the Good Work Always!

 

[repairman615]

I am glad you have good results.

There is a test forum where you can test out the html maker or any other posting test.

http://www.mrexcel.com/forum/forumdisplay.php?f=14

welcome to the Message Board!!

-Jeff

 

[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,

Thank you for the feedback.

Note: the important thing for us all is that your problem has been resolved.

Markmzz

 

[Peter_SSs]

... I will surely try to build lookup table, but I have to study & work more with excel for that.

You don't need to study anything to do that, you could just copy my lookup table from the screen shot below. I have modified the table in columns U:X slightly to match your clarified criteria.

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	50	60
3	Overdue										62		Red		Invalids	0	42	55
4	102										6		Green		MDD	0	60	70
5	Invalids										85		Red		Overdue	0	42	55
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		Red
22	120										50		Amber
Lookup Table

I have already downloded the Mr. Excel HTML Maker. But inspite of trying a long hours, I was unable to create html file from my Excel 2007.

If you downloaded from the link in my signature block you should also have received a Word document explaining how to install, activate and use it. Have you tried to follow that Word document? If so, can you describe at what point your process is failing?

 

[Sampat]

If you downloaded from the link in my signature block you should also have received a Word document explaining how to install, activate and use it. Have you tried to follow that Word document? If so, can you describe at what point your process is failing?

Peter - Thanks for explaining!

To tell you the real fact, I have downloaded the Mr. Excel html maker from some other place. I will surely try the one from the link in your signature tab.

I have also installed Excel Jaenie, will use the same next time I need to post something in forum.

Thanks for all the help & guidance you offered, what else I can say!

Feeling really good!