Excel 2007 : If, and, or

**TheBoyBest** · 01-19-2011, 10:52 AM

Hi there, I am currently doing some work where by I need to forecast projected salaries from 2011-15 (assuming nothing bad happens on 21/12/12 lol!)

We have 3 departments, each with 4 PayBands so in total there are 12 different paybands.

The pay goes up after so many years service, so less than 4 years is Band 1, 4-7 years Band 2, 8-12 years Band 3 and 13+ Band 4.

I can not seem to get my head around this (I have done this from scratch so I have not had anything to work from.

This is what I am trying to say

IF(AND(C2="Production", EMPLOYEES!M2="G1", IF(DATEDIF((D2,"31/12/2011","y")<=3), RATES!$D$10,
IF(AND(C2="Production",EMPLOYEES!M2="G2", IF(DATEDIF((D2,"31/12/2011","y")<=7), RATES!$D$11,
IF(AND(C2="Production", EMPLOYEES!M2="G3", IF(DATEDIF((D2,"31/12/2011,"y")<=12), RATES!$D$12,
IF(AND(C2="Production", EMPLOYEES!M2="G4", IF(DATEDIF((D2,"31/12/2011","y")>13), RATES!$D$13,

IF(AND(C2="Design", EMPLOYEES!M2="G1", IF(DATEDIF((D2,"31/12/2011","y")<=3), RATES!$D$6,
IF(AND(C2="Design",EMPLOYEES!M2="G2", IF(DATEDIF((D2,"31/12/2011","y")<=7), RATES!$D$7,
IF(AND(C2="Design", EMPLOYEES!M2="G3", IF(DATEDIF((D2,"31/12/2011,"y")<=12), RATES!$D$8,
IF(AND(C2="Design", EMPLOYEES!M2="G4", IF(DATEDIF((D2,"31/12/2011","y")>13), RATES!$D$9,

IF(AND(C2="Admin", EMPLOYEES!M2="G1", IF(DATEDIF((D2,"31/12/2011","y")<=3), RATES!$D$2,
IF(AND(C2="Admin",EMPLOYEES!M2="G2", IF(DATEDIF((D2,"31/12/2011","y")<=7), RATES!$D$3,
IF(AND(C2="Admin", EMPLOYEES!M2="G3", IF(DATEDIF((D2,"31/12/2011,"y")<=12), RATES!$D$4,
IF(AND(C2="Admin", EMPLOYEES!M2="G4", IF(DATEDIF((D2,"31/12/2011","y")>13), RATES!$D$5,
IF(EMPLOYEES!M2="G4", IF(DATEDIF((D2,"31/12/2001","y")=20), RATES!$D$5*1.008, "ERROR"

I have a working formula for working out what grades staff will be this year, next year any advice!!? Really really appreciated

Thanks

Chris

**Marcol** · 01-19-2011, 01:33 PM

You clearly have 3 departments.

Do you have
1/. 4 Grades, each sub-divided into 4 bands based on years of service for each department?
or
2/. is each Grade, in any department, attained by years of service.

**TheBoyBest** · 01-19-2011, 02:23 PM

Hi there, thanks for getting back to me.

Each Dept. has its own salary based on grade.

When I use this;

IF(AND(D2="Production", DATEDIF(E2,"31/12/2011","m")<48, F2="G1"),RATES!$D$10,
IF(AND(D2="Production", DATEDIF(E2,"31/12/2011","m")<96, F2="G2"),RATES!$D$11,
IF(AND(D2="Production", DATEDIF(E2,"31/12/2011","m")<156, F2="G3"),RATES!$D$12,
IF(AND(D2="Production", DATEDIF(E2,"31/12/2011","m")>156, F2="G4"),RATES!$D$13,
IF(AND(D2="Production", DATEDIF(E2,"31/12/2011","m")>=240, F2="G4"),RATES!$D$13*1.008,

IF(AND(D2="Design", DATEDIF(E2,"31/12/2011","m")<48, F2="G1"),RATES!$D$6,
IF(AND(D2="Design", DATEDIF(E2,"31/12/2011","m")<96, F2="G2"),RATES!$D$7,
IF(AND(D2="Design", DATEDIF(E2,"31/12/2011","m")<156, F2="G3"),RATES!$D$8,
IF(AND(D2="Design", DATEDIF(E2,"31/12/2011","m")>156, F2="G4"),RATES!$D$9,
IF(AND(D2="Design", DATEDIF(E2,"31/12/2011","m")>=240, F2="G4"),RATES!$D$9*1.008,

IF(AND(D2="Admin", DATEDIF(E2,"31/12/2011","m")<48, F2="G1"),RATES!$D$2,
IF(AND(D2="Admin", DATEDIF(E2,"31/12/2011","m")<96, F2="G2"),RATES!$D$3,
IF(AND(D2="Admin", DATEDIF(E2,"31/12/2011","m")<156, F2="G3"),RATES!$D$4,
IF(AND(D2="Admin", DATEDIF(E2,"31/12/2011","m")>156, F2="G4"),RATES!$D$5,
IF(AND(D2="Admin", DATEDIF(E2,"31/12/2011","m")>=240, F2="G4"),RATES!$D$5*1.008, "ERROR!")))))))))))))))

I seem to get the right payments for that year based on the data I have.

Trouble is - those who have 20 years service are getting a 0.8% increase on their annual salary (tight people!) but this part;

IF(AND(D2="Admin", DATEDIF(E2,"31/12/2011","m")>=240, F2="G4"),RATES!$D$5*1.008, "ERROR!")))))))))))))))

Doesn't work - please accept my apologises, I am a complete N0013 to Excel and Access.

Thanks for your time and advice in advance

Chris

**TheBoyBest** · 01-19-2011, 02:24 PM

Originally Posted by Marcol

You clearly have 3 departments.

Do you have
1/. 4 Grades, each sub-divided into 4 bands based on years of service for each department?
or
2/. is each Grade, in any department, attained by years of service.

Yes to Number 1 mate

**DonkeyOte** · 01-19-2011, 02:48 PM

From what I can tell you'd be far better off splitting the calculation.

For sake of ex.

G2: 
=DATEDIF(E2,"31/12/2011","y")

At which point something along the lines of:

H2:
=IF(AND((RIGHT($F2)+0)=MATCH($G2,{0,4,7,13}),OR($D2={"Admin","Design","Production"})),INDEX(Rates!$D$2:$D$13,RIGHT($F2)+4*(MATCH($D2,{"Admin","Design","Production"},0)-1))*(1+0.008*($G2>=20)),"Error")

that said a basic rate table would help simplify things somewhat...

The complexity of the above stems from the fact that you seemingly need to first validate the legitimacy of the Dept and the Band when compared to Years of Service (ie to catch "Error")

**arthurbr** · 01-19-2011, 04:19 PM

As far as I can understand, even when the DATEDIF is > 240, the formula always stops at f.i.

IF(AND(D2="Production", DATEDIF(E2,"31/12/2011","m")>156, F2="G4"),RATES!$D$13

, in one of the three groups,any value larger than 240 would de facto be larger than 156, so this test returns TRUE and the ,8% is never calculated

Maybe inverting the last two IF's ( for the three groups) would solve the problem?

Naturally DO's formula would be more efficient

**TheBoyBest** · 01-19-2011, 05:24 PM

Whoa - its blown me right out of the water there like!

I do apologise for being so simple over these matters, if I upload the test file could you have a look at it and give me some pointers?

I do honestly really appreciate any help - I just can not get it to work

**DonkeyOte** · 01-19-2011, 05:25 PM

Originally Posted by TheBoyBest

if I upload the test file could you have a look at it and give me some pointers?

Yes, that would be a good idea.

**TheBoyBest** · 01-19-2011, 05:52 PM

OK........... Again, kudos to all you people who can help - I am hoping to learn a lot from this so please accept my thanks.

Attached sheet for work;

Overview

There are 4 grades of pay based on length of service

G1 = less than 4 years
G2 = 4-7 years
G3 = 8-12 years
G4 = 13+ years

Staff grade progression starts in the month they meet the above criteria from their start date

Salaries are standard and increase 1% each year in January

From this August we will be celebrating the 20th Anniversary and we are going to include a 20 year long service award to all staff when they reach 20 years service. This "bonus" is going to be 0.8% of their salary in that year.

We need the spreadsheet to forecast costs till 2020

There! Sigh (bitten off more than I can chew, but they will insist on making self-learn this stuff!)

Many many thanks

Chris

**DonkeyOte** · 01-19-2011, 06:03 PM

So quickly.. using your example

My first suggestion was to store the Years Service separately, eg:

K2:
=DATEDIF($C2,"31/12/2011","y")
copied down to K31

Then, to generate rates:

F2:
=IF(AND((RIGHT($D2)+0)=MATCH($K2,{0,4,7,13}),OR($B2={"Admin","Design","Production"})),INDEX(RATES!D$2:D$13,RIGHT($D2)+4*(MATCH($B2,{"Admin","Design","Production"},0)-1))*(1+0.008*($K2+(COLUMNS($F2:F2)-1)>=20)),"Error")
applied to matrix

which would for F2 generate 32,259.90 given 20 years service
(ie 1.008 of 32,003.87)

The above is adapted slightly from before in so far as reference to Rates becomes relative such that copied to the right in picks up the correct column... similarly the years service is incremented by one each year such that the increase occurs where appropriate.
(we only conduct the Gn = years service test for 2011 - thereafter the calculation is not viable)

You will note that rows 16:18 return Error - because years service (13) exceeds the G3 band (same issue re: 24:26 [should be G3] and 28 [should be G2])

**TheBoyBest** · 01-19-2011, 06:19 PM

Hi thanks for the quick reply - I am not sure I follow entirely. To paraphrase Doc McCoy - "I'm a bricklayer, not a Doctor, Jim"!

Someone at work was banging on about lookup tables? Are they any good? I'm really stressed hence doing this in my own time, at 2220hrs!

Cheers

**DonkeyOte** · 01-19-2011, 06:31 PM

All you need to do is copy the formulae I gave you into the cells specified and you have the results. I'm not sure what else I can add at this stage.

**Marcol** · 01-20-2011, 01:42 PM

I had to go and have perhaps lost the current status of this thread.

FWIW
I had started a sample workbook

The result uses named ranges and I think perhaps a simpler and more flexible rates table.

**DonkeyOte** · 01-20-2011, 01:59 PM

@Marcol, re: your model, I'm probably wrong but the below:

Originally Posted by TheBoyBeast

From this August we will be celebrating the 20th Anniversary and we are going to include a 20 year long service award to all staff when they reach 20 years service. This "bonus" is going to be 0.8% of their salary in that year.

would imply that the one off multiplier could occur at any point within the forecast.

For the above reason I had used:

*(1+0.008*($K2+(COLUMNS($F2:F2)-1)>=20))

however that too is incorrect given it's a one off & should be =20 rather than >=20

Attached is the OP's last file with my suggestions

**TheBoyBest** · 01-21-2011, 07:34 AM

Hi there - thanks for your help - I seem to have sorted it using your advice so its really appreciated thanks

Chris

Excel 2007 : If, and, or

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

If, and, or

Re: Problems with IF, AND, OR

Re: Problems with IF, AND, OR

Re: Problems with IF, AND, OR

Re: Problems with IF, AND, OR

Re: Problems with IF, AND, OR

Re: Problems with IF, AND, OR

Re: Problems with IF, AND, OR

Re: If, and, or

Re: If, and, or

Re: If, and, or

Re: If, and, or

Re: If, and, or

Re: If, and, or

Re: If, and, or

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions