Formula

[vince1209]

I am trying to add to a formula already written and need some assistance.

I have this formula: =IF(N7="A2-1",IF(S7="Threshold","$50.00", IF(S7="Target","$75.00", IF(S7="Stretch","$100.00")))).

So far it works fine. I need to adjust it a little bit. What I need to add to that formula is the following: if the hire date is between 2/1/2008 and 2/29/2008.

I am not sure how to write it.

 

[SkipVought]

Hi,
[tt]
=IF(AND(HireDateCellRef>=CellRefForDate1,HireDateCellRef<=CellRefForDate2),YourCurrentIfGoesHere,"WhatIfTheHireDateIsNOT")
[/tt]


Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[vince1209]

Skip, Thanks

I forgot to mentioned that I needed add the percentages

=IF(N7="A2-1",IF(S7="Threshold","$50.00", IF(S7="Target","$75.00", IF(S7="Stretch","$100.00")))).

if the hire date is between 2/1/2008 and 2/29/2008 then two-thirds or 66% of the payout

if the hire dat is greater than 3/1/2008 then one-third or 33% of the payout

 

[SkipVought]

From you table in a previous post...
[tt]
Threshold Target Stretch
A2 $100 $150 $200
A2-1 $50 $75 $100
B2 $125 $175 $225
C2 $150 $200 $250
[/tt]
there is NOT WAY that =IF(N7="A2-1",IF(S7="Threshold","$50.00", IF(S7="Target","$75.00", IF(S7="Stretch","$100.00")))) answers your requirement.

Did you try the approch that I posted in that thread?

With this additional requirement, you need to design a similar TRUTH TABLE that describes ALL your possibilities, in order to design the decision logic.


Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[vince1209]

I was able to get the results I needed with this formula =IF(N7="A2-1",IF(S7="Threshold","$50.00", IF(S7="Target","$75.00", IF(S7="Stretch","$100.00")))).

Basically the end user will type in either target , threshold or stretch and the appropriate dollar amount will pop up. Now I am trying to understand how to add to that formula if the hire date is between 2/1/2008 and 2/28/2008 and add the percentage 33%

 

[SkipVought]

[tt]
=IF(AND(HireDateCellRef>=CellRefForDate1,HireDateCellRef<=CellRefForDate2),.66,.33)*YourCurrentIfGoesHere
[/tt]

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[vince1209]

Skip:

Is this correct?
=IF(AND(I7>="2008/02/02", I7<="2008/29/02"),.66), IF(N7="A2-1",IF(S7="Threshold","$50.00", IF(S7="Target","$75.00", IF(S7="Stretch","$100.00")))))

 

[SkipVought]

Ne because dates are not strings! Which is why I suggested using a cellreference for each date. Data like that should not be HARD CODED into formulas. Data like that CHANGES often.

AND where is the .33??? You missed the FALSE condition.

AND what is the COMMA doing between the two IF formulas? My code does the PRODUCT of the two.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[vince1209]

Skip, I think I am close.

=IF(AND(I2>=2008-3-1,I2<=2008-3-31), 0.33)*IF(N2="A2",IF(Y2="Threshold","$100.00", IF(Y2="Target","$150.00", IF(Y2="Stretch","$200.00"))))

When I use the formula it is giving me $0. What am I doing wrong? For example if I have $800 * 33% the equals to $264. I am getting $0.

 

[SkipVought]

Now you have changed your date strings to an arithmetic expression -- (2008 MINUS 3 MINUS 1)!!! That's what your current formula means.

If you INSIST on using a HARD CODED DATE (really dumb, cuz you're gonna be changing it every month!)
[tt]
=IF(AND(I2>=datevalue("2008-3-1"),I2<=datevalue("2008-3-31")), 0.33,.66)*IF(N2="A2",IF(Y2="Threshold","$100.00", IF(Y2="Target","$150.00", IF(Y2="Stretch","$200.00"))))
[/tt]
I'd rather see this, if you're using last month...
[tt]
=IF(AND(I2>=date(year(today()),month(today())-1,1),I2<=date(year(today()),month(today()),0), 0.33,.66)*IF(N2="A2",IF(Y2="Threshold","$100.00", IF(Y2="Target","$150.00", IF(Y2="Stretch","$200.00"))))
[/tt]
then you never have to adjust it.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[AnotherHiggins]

2008-3-1 = 208 MINUS 3 MINUS 1 = 2004
That's still not a date!

Data like that should not be HARD CODED into formulas

If you insist on hardcoding, then use DateValue("2008/02/02")

For more info on how Excel deals with dates and times, see faq68-5827

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.