IF and Date Problem

[Maz1973]

Hi
I would like my target to change depending on the date.

<TABLE style="WIDTH: 110pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=146 border=0><COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 62pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=82 height=20>Actuals</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right width=64>15%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Target</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>25%</TD></TR></TBODY></TABLE>

So if we are in month Apr-Jun the target is 25%, Jul-Sep 50%, Oct-Dec, 75% and Jan-Mar 100%.

I think it involves a nested if, but would really appreciate some help. How can I get it to link into today's date and the quarter.

Many thanks

 

[VoG]

Try

=LOOKUP(MONTH(TODAY()),{1,4,7,10},{1,0.25,0.5,0.75})

 

[Weaver]

try:

=LOOKUP(MONTH(NOW()),{1,4,7,10},{1,0.25,0.5,0.75})

 

[Beate Schmitz]

Choose

Hallo,

Excel Workbook
	A
1	100%
Sheet

 

[lenze]

Hi and welcome to the Board!!!
How about CHOOSE?

=CHOOSE(MONTH(TODAY()),1,1,1,.25,.25,.25,.50,.50,.50,.75,.75,.75)

You could also use LOOKUP, or VLOOKUP
lenze

 

[Maz1973]

Re: Choose

Hallo,

Excel Workbook
	A
1	100%
Sheet

Thank you all for your quick replies. When I copy the formula in it comes up with 1 rather than with 25% - do I have to add something to the formula?

 

[VoG]

Just format the cell as %

<b>Sheet6</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >J</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">100%</td></tr></table><br /><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 >J3</td><td >=LOOKUP(MONTH<span style=' color:008000; '>(TODAY<span style=' color:#0000ff; '>()</span>)</span>,{1,4,7,10},{1,0.25,0.5,0.75})</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[Maz1973]

Thank you so much!