Problem with if statement 3

[Digitelpersona]

Hi Everyone,

I'm very new to Excel and the formulas that can be created in it. By doing some reading and searching around, I've come up with this IF statement but it doesn't seem to be working right!
=IF(INT((H6-$B$8)/365.25)+1,<4,5.83,<10,8.75,<16,11.67,<17,12.25,<18,12.83,<19,13.42,<20,14,>20,14.58)

What I basically need to do is look at two dates and come up with a number: example if 7/1/1998 - 1/21/2005 = 7 then the formula should return 8.75.

The problem is that this needs to calculate for 12 month, for each person.
So for example:
John Smith started working with us on 2/4/2002, so the formula for January (1/1/2005-2/4/2002) would produce
3 and he’s accrual would be 5.83, however the next column for February (2/1/2005-2/4/2002) would produce 4 and he’s accrual would now be 8.75 and so on….

So my formula above is clearly wrong, what I need it to say is (if dateone-datetwo=# take that # and figure out if it's <4 then 5.83, <10 then 8.75, <16 then 11.67) etc.

Please let me know if anyone has any suggestions on how to solve this, or if you need more information from me to help me solve it.

Thanks for your time,
Joe

 

[sawedoff]

Try this (although I think you would be better served with a lookup table, perhaps in a 2nd worksheet.)

=IF(INT(((+C8-$H$6)/365.25)+1)<4,5.83,IF(INT(((C8-$H$6)/365.25)+1)<10,8.75,IF(INT(((+C8-$H$6)/365.25)+1)<16,11.67,IF(INT(((+C8-$H$6)/365.25)+1)<17,12.25,IF(INT(((+C8-$H$6)/365.25)+1)<18,12.83,IF(INT(((+C8-$H$6)/365.25)+1)<19,13.42,IF(INT(((+C8-$H$6)/365.25)+1)<20,14,14.58)))))))

Sawedoff

 

[bluedragon2]

What you need to do is create an array that has the year/accrual listed:

Col A Col B
0 5.83
3 8.75
9 11.67
15 12.25
16 12.83
18 13.42
19 14
20 14.58


Then use the statment:

Lets say start date is cell C1 and Reference date is cell D1

=VLOOKUP(INT((D1-C1)/365.25),A1:B20,2,1)



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[AnotherHiggins]

There are many ways to do this but since you are just learning If statements, I'll simply correct your formula. (Note: What follows won’t be the prettiest formula for the job, but it’ll work and it follows the logic you are trying to apply/learn).

Let's start with the syntax for a simple if statement:
[COLOR=black white]=IF(logical_test,value_if_true,value_if_false)[/color]
(you can find 'reminders' for the arguments by typing =if and then <Ctrl>+<Shift>+<A>. this works for any Excel formula)

To combine several IF statements, you need to nest one within another. The syntax for nested IFs is like this:
[COLOR=black white]=IF(logical_test_1,value_if_true,IF(logical_test_2,value_if_true,value_if_false))[/color]
All we did is replace the first value_if_false statement with another complete IF. Get it?

Stringing this all together, your formula should look like this:
[COLOR=blue white]=IF(INT((H6-$B$8)/365.25)+1<4,5.83,IF(INT((H6-$B$8)/365.25)+1<10,8.75,IF(INT((H6-$B$8)/365.25)+1<16,11.67,IF(INT((H6-$B$8)/365.25)+1<17,12.25,IF(INT((H6-$B$8)/365.25)+1<18,12.83,IF(INT((H6-$B$8)/365.25)+1<19,13.42,IF(INT((H6-$B$8)/365.25)+1<20,14,14.58)))))))[/color]
Where A1 contains the most current month and A2 contains the beginning date.


[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[Digitelpersona]

I just want to thank you all for being so quick and helpful in your responses. I actually think that I'm very close to making this work!

However, I do have one other quick question; is it possible in Excel to figure out the difference between two dates using only the month and year (excluding the day). It seems like our fiscal dept is using this logic when doing calculation and varations in the DAY can change the result of the date formula.

Big thanks again for your great help!
Joe

 

[sawedoff]

Try =A1-A2 (where A2 contains the later date, and A1 the earlier, then format the answer as 'yy "Years" M "Months" ' (custom format, less single quotes). For example, 1/21/05 minus 8/15/02 would appear as 02 Years 5 Months

Sawedoff

 

[Digitelpersona]

The problem is I need it to do the subtraction using the month and year only. Example mm/yyyy - mm/yyyy.

Here is an example 1/1/2005 - 2/4/2002=3
and 2/1/2005 - 2/4/2002=3

However, if the date was 2/5/2005 - 2/4/2002 I'd get 4.

I believe this is happening because Excel is using the entire date (mm/dd/yy) to do the subtraction.

But I'm wondering if I can tell it to use just the month and year.

Because what I need is the result to be:
1/2005 - 2/2002=3
2/2005 - 2/2002=4


Does that make better sense?

Joe

 

[sawedoff]

To get just the number of years difference, do =YEAR(A2)-YEAR(A1) then format general.

Sawedoff

 

[AnotherHiggins]

Where is H8 coming from? You seem to be using this as the begining of the current month. Why would it ever be "2/5/2005"?

In any case, changing both the beginning and end date to the beginning of the respective months should work.

[COLOR=black white]=now()-day(now())[/color] will give you the last day of last month. Adding 1 will get you the first day of this month.

Applying that logic to your date ranges would result in this:
[COLOR=blue white]=INT(((H6-DAY(H6)+1)-($B$8-DAY($B$8)+1))/365.25)+1[/color]

Does that help?

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[Digitelpersona]

I did try that, it does work but gives me the same result for 1/2005-2/2002= 3 and 2/2005-2/2002= 3. Is it possible to factor in the month as well? not even sure how I would write this out as a formula.

because again 1/2005-2/2002 is a three year difference, but 2/2005-2/2002 is a 4 year difference... I guess that's the logic I need.

Joe

 

[AnotherHiggins]

Digitelpersona,

We seem to have cross-posted. Did you try my last suggestion?

To only use the Month and Year in the calculation you should just change the Day of both dates to the first. My last post should cover that for you.


[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[Digitelpersona]

John,

Thank you very much, I think you solved it for me!

Joe

 

[Digitelpersona]

I'm sorry to keep posting regarding the same issue;

the formula is finally working up to a point:

=IF(INT((((H7-DAY(H7)+1)-($B$19-DAY($B$19)+1))/365.25)+1)<4,5.83,IF((((INT(H7-DAY(H7)+1)-($B$19-DAY($B$19)+1))/365.25)+1)<10,8.75,IF((((INT(H7-DAY(H7)+1)-($B$19-DAY($B$19)+1))/365.25)+1)<16,11.67,IF((((INT(H7-DAY(H7)+1)-($B$19-DAY($B$19)+1))/365.25)+1)<17,12.25,IF((((INT(H7-DAY(H7)+1)-($B$19-DAY($B$19)+1))/365.25)+1)<18,12.83,IF((((INT(H7-DAY(H7)+1)-($B$19-DAY($B$19)+1))/365.25)+1)<19,13.42))))))

But I need to add a couple more nested IFs... Excel keeps giving me an error once I add any more IFs beyond what I've already entered.

Is there some kind of a limit to nested IFs and if so, how can I get around it?

Thanks again,
Joe

 

[AnotherHiggins]

There is a limit of 7 nested ifs.

I think it's time for you to look into bluedragon2's suggestion posted earlier.

If you need help, feel free to list out exactly what conditions you are trying to meet and someone here should be able to help you.

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[01Patrik]

The limit is actually based on the number of opening peranthesis "(" - you can't have more than 8 open without closing.


Easiest way to get around is to divide your function in two different cells/columns and use another if-function in a third cell/column; and end up by hiding the first two cells/cols.

A1:

=if(question,true,false)

B1:

=if(question,true,false)

C1:

=if(and(A1=true,B1=true),true,false)

Of course you can vary with OR instead of AND; and nest with other functions.


// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.

 

[bluedragon2]

Just a side note on using huge nested IF's...

What if your hard data changes (5.86 needs to be changed to 5.96) You will have to change all of your IF statments. Using a lookup table, you only need to change it once.

Just a suggestion...

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Digitelpersona]

Thank you bluedragon2. After a little bit of playing around with VLookup I think I've figured it out.

Again, big thanks!

Joe

 

[sawedoff]

Hence my first statement (smile).



Sawedoff

 

[Digitelpersona]

Yes! I'm sorry I wasn't familiar enough with excel to figure that out on my own, but thank you for your help!

Joe