Help with formula

[mazher]

Hi,

I am using this formula

=IF(E2<=0,"Expired",IF(E2<=7,"1 Weeks Left",IF(E2<=14,"2 Weeks Left",IF(E2<=21,"3 Weeks Left",IF(E2<=30,"4 Weeks Left",IF(E2<=60,"2 Months Left","More Than 3 Month"))))))

e2<= 0 its expired
e2 = 1 to 6 than "Less than a week"
e2 = 7 to 13 then "Less than 2 weeks"
e2= 14 to 20 then "Less than 3 weeks
e2= 21 to 31 then "Less than month"
e2= 32 to 60 then "Less than 2 months"
e2= 61 to 90 "Less than 3 months"

else " More than 3 Months


Can some one help me constructing this =if() or some better approach.

Regards

Mazher

 

[AlphaFrog]

Maybe try something like this...

=LOOKUP(E2,{-9.99999999999999E+307,1,8,15,22,29,61},
      {"Expired","Less than a week","Less than 2 weeks","Less than 3 weeks","Less than 4 weeks","Less than 2 months","More Than 2 Months"})

 

[mazher]

Thanks.

Can u plz explain what this formula is doing?

Regards


Mazher

 

[jeffreybrown]

Hi Mazher,

If you look in Excel help for the Lookup function it has a great explanation which should help you understand what's going on.

Especially take a look at the Array form of the Lookup and Example 2.

 

[T. Valko]

Hi,

I am using this formula

=IF(E2<=0,"Expired",IF(E2<=7,"1 Weeks Left",IF(E2<=14,"2 Weeks Left",IF(E2<=21,"3 Weeks Left",IF(E2<=30,"4 Weeks Left",IF(E2<=60,"2 Months Left","More Than 3 Month"))))))

e2<= 0 its expired
e2 = 1 to 6 than "Less than a week"
e2 = 7 to 13 then "Less than 2 weeks"
e2= 14 to 20 then "Less than 3 weeks
e2= 21 to 31 then "Less than month"
e2= 32 to 60 then "Less than 2 months"
e2= 61 to 90 "Less than 3 months"
else " More than 3 Months


Can some one help me constructing this =if() or some better approach.

Regards

Mazher

If I were you I'd setup a lookup table.

AlphaFrog's formula doesn't follow your levels exactly.

 

[snoopyhr]

How about something like this...

Excel Workbook
	A	B	C	D	E	F
1	Days	Outcome	*	*	Days	Time to take action
2	0	"Expired"	*	*	40	"Less than 2 months"
3	1	"Less than a week"	*	*	0	"Expired"
4	7	"Less than 2 weeks"	*	*	6	"Less than a week"
5	14	"Less than 3 weeks	*	*	14	"Less than 3 weeks
6	21	"Less than month"	*	*	22	"Less than month"
7	32	"Less than 2 months"	*	*	33	"Less than 2 months"
8	61	"Less than 3 months"	*	*	65	"Less than 3 months"
9	*	*	*	*	13	"Less than 2 weeks"
10	*	*	*	*	2	"Less than a week"
11	*	*	*	*	7	"Less than 2 weeks"
12	*	*	*	*	6	"Less than a week"
Sheet2

<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >F2</td><td >=LOOKUP(E2,$A$2:$A$8,$B$2:$B$8)</td></tr></table></td></tr>Copy down as needed....</table>

 

[mazher]

Thanks All,

T. Valko can u please tell me how to do that.

Regards

Mazher

 

[T. Valko]

Thanks All,

T. Valko can u please tell me how to do that.

Regards

Mazher

e2<= 0 its expired
e2 = 1 to 6 than "Less than a week"
e2 = 7 to 13 then "Less than 2 weeks"
e2= 14 to 20 then "Less than 3 weeks
e2= 21 to 31 then "Less than month"
e2= 32 to 60 then "Less than 2 months"
e2= 61 to 90 "Less than 3 months"
else " More than 3 Months

OK, your first level is <=0. Does that mean it's possible that E2 might be a negative number?

 

[mazher]

yes it can be less than zero,

As its calculating the difference between two dates which can be negative

 

[T. Valko]

yes it can be less than zero,

As its calculating the difference between two dates which can be negative

Ok, this table:

Book1
	A	B	C
1	From	To	Status
2	-1000000	0	Expired
3	1	6	Less than a week
4	7	13	Less than 2 weeks
5	14	20	Less than 3 weeks
6	21	31	Less than a month
7	32	60	Less than 2 months
8	61	90	Less than 3 months
9	91	>91	More than 3 Months
Sheet1

Conforms to this set of rules:

e2<= 0 its expired
e2 = 1 to 6 than "Less than a week"
e2 = 7 to 13 then "Less than 2 weeks"
e2= 14 to 20 then "Less than 3 weeks
e2= 21 to 31 then "Less than month"
e2= 32 to 60 then "Less than 2 months"
e2= 61 to 90 "Less than 3 months"
else " More than 3 Months

Although you really don't need column B in the table it makes it easier to find the exact level you're looking for.

E2 = some number of days

Enter this formula F2:

=LOOKUP(E2,A2:C9)