Excel 2008 : RESOLVED Configuring Nested IF Logic

**baraktorvan** · 11-22-2010, 03:26 AM

I am trying to generate an IF statement that will take the data in cell D7 and if it is more than 4 the result is 15, if it is between 2 and 4 years, the result is 10, if it is between 1 and 2 years, the result is 5, if it is less than 1, the result is 0.

What I have is =IF(AND(B7="FT",D7>=4),15,IF(OR(B7="FT",D7>=2<=4),10,IF(OR(B7="FT",D7>=1<=2),5,
IF(OR(B7="FT",D7=0),0)))

The result is always 10, even if the cell is 4 or greater.

What have I got that is wrong and can you help?

Excel 2010 on the Windows Partition
Excel 2011 on the Mac Partition.

**DonkeyOte** · 11-22-2010, 03:49 AM

You don't mention the significance of FT in your narrative but based on combination of original formula and original narrative:

Please Login or Register  to view this content.

or, assuming the step of 5 to always be constant (going forward)

Please Login or Register  to view this content.

In both cases it is assumed that D7 is a number (and not number stored as text - if so change SUM to N and encase 2nd reference to D7 with N also)

I modified your title slightly - please keep focused on specifics of problem.

**baraktorvan** · 11-22-2010, 04:16 AM

Thanks for the help and for the the title edit-that will help in the future.

The FT is text indicating a full time employee, PT would be used for part time employees. The text of D7 is the result of the formula =DATEDIF(C6,Z6, "Y").

Basically this spreadsheet is used to calculate vacation leave and paid family care time.

For vacation we grant 15 days after 4 years, 10 days between 2 and four years, 5 days from 1-2 years, and none if you have less than 1 year based on calendar years and not date of hire. The revised formula works perfectly and I thank you for the edits!

For paid family care, we have 5 paid days off if you are greater than one year, 3 days if you have worked for less than one year or if you are part time having worked more than 1.5 years. I haven't started on that formula yet.

I hope that makes my intention clearer! Thanks again for your help.

**DonkeyOte** · 11-22-2010, 04:35 AM

Originally Posted by baraktorvan

For paid family care, we have 5 paid days off if you are greater than one year, 3 days if you have worked for less than one year or if you are part time having worked more than 1.5 years.

If you're looking for a solution to the above (unclear) then one possibility:

Please Login or Register  to view this content.

Note however that a DATEDIF function using "y" will not give you partial years - ie you will never get 1.5 result.
On that basis you might wish to consider converting from "y" based test to "m" based test in the DATEDIF and work from the monthly figure (eg 12 for 1 year, 18 for 1.5 years etc...)

**baraktorvan** · 11-22-2010, 03:14 PM

Exactly what I came up with on my own. Thanks so much!

But I have run into an additional snag. I have to create a summary sheet that calculates the number of vacation leave days remaining based on the number of days originally granted.

So on my new worksheet, I have in the text 15 day in B4, 10 day in B5, 5 day in B6, and None in B7.

In E4-E7 I want to take the total days remaining for each of the allotments (the 15 day, 10 day, 5 day and 0 day) based on the individual use recorded on the Leave worksheet. In my head, I am saying "Sum the total number of days remaining on the Leave worksheet for each employee who earns 15 days per year and put it on the Summary table."

I came up with =IF(AND(Leave!E6:E110=15),SUM(Leave!G9:G110),0) but that is coming up with a #Value error and I cannot figure out why. The workbook is attached.

**baraktorvan** · 11-24-2010, 04:40 PM

So I guess everyone is kinda stuck like I am. :-(

**NBVC** · 11-24-2010, 05:01 PM

Have you looked at SUMIF:

e.g.

In E4: =SUMIF(Leave!E6:E110,15,Leave!G9:G110)

**Lifesigns** · 11-25-2010, 03:09 PM

Hi there,

You could use SUMIF but if you changed your formula a little it would work provided you check your cell references.

I've attached your original workbook so you can see what I've done. Basically changed the [I]days remaining[I] formula to an array formula:

{=SUM(IF(Leave!$E$6:$E$110=$A4,Leave!$G$6:$G$110,0))}

You need to use Ctrl+Shift+Enter when you enter it. I've put the allocations in cells A4:A7 so if they are changed the formulas can stay the same.

As mentioned above you need to check your cell references when dragging formulas, the ones in C5:C7 where mising data when you dragged it down the original range moved down as well.

=COUNTIF(Leave!E6:E107,15)

Use F4 to lock the references.

=COUNTIF(Leave!$E$6:$E$107,15)

**NBVC** · 11-25-2010, 03:12 PM

So why does the Sumif formula need to be changed to an Array formula:

Why not just?

=SUMIF(Leave!$E$6:$E$110,$A4,Leave!$G$6:$G$110)

**baraktorvan** · 11-26-2010, 01:53 PM

I tried that formula, and the result was a 0 days remaining when I used it for the first 15 day row.

When I used the Array formula, it came up with the correct amount of days when I counted them individually.

I am not sure why it did not work, I am still very new with nested IF logic.

Originally Posted by NBVC

So why does the Sumif formula need to be changed to an Array formula:

Why not just?

=SUMIF(Leave!$E$6:$E$110,$A4,Leave!$G$6:$G$110)

**baraktorvan** · 11-26-2010, 01:55 PM

Thanks so much Lifesigns! That worked much better than my own IF statement! You just saved my bacon as this report is due on Monday and I was not looking forward to banging my head all weekend long!

Originally Posted by Lifesigns

Hi there,

You could use SUMIF but if you changed your formula a little it would work provided you check your cell references.

I've attached your original workbook so you can see what I've done. Basically changed the [I]days remaining[I] formula to an array formula:

{=SUM(IF(Leave!$E$6:$E$110=$A4,Leave!$G$6:$G$110,0))}

You need to use Ctrl+Shift+Enter when you enter it. I've put the allocations in cells A4:A7 so if they are changed the formulas can stay the same.

As mentioned above you need to check your cell references when dragging formulas, the ones in C5:C7 where mising data when you dragged it down the original range moved down as well.

=COUNTIF(Leave!E6:E107,15)

Use F4 to lock the references.

=COUNTIF(Leave!$E$6:$E$107,15)

Excel 2008 : RESOLVED Configuring Nested IF Logic

LinkBack

Thread Tools

Rate This Thread

Display

RESOLVED Configuring Nested IF Logic

Re: Nested IF statement and I am confused.

Re: Configuring Nested IF Logic

Re: Configuring Nested IF Logic

Re: Configuring Nested IF Logic

Re: Configuring Nested IF Logic

Re: Configuring Nested IF Logic

Re: Configuring Nested IF Logic

Re: Configuring Nested IF Logic

Re: Configuring Nested IF Logic

Re: Configuring Nested IF Logic

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions