Good afternoon everyone

I'll try and put this as simple as possible but have tried doing this myself
and cannot work out how to do this.

I have a new employee - summary as follows

He starts 01/01/2010 (c65)
He is a level 2 (b65)
He has a monthly sales target of say ‚¬10,000 (d65)

However his target does not start in month 1, instaead for the first 4
months (Jan - April his target will be ‚¬0.00, From months 5 - 8 (May - Aug)
his taget will be 75% of the ‚¬10,000 (‚¬7,500). From month 9 (Sept) it'll be
100% of the target = ‚¬10,000

Along the top of my spreadsheet I have months (all set to the last day in
the month)

AH3 AH4 AH5 AH6
Jan 09 Feb 09 March 09 Apr 09 etc.

I have had a crack at a couple of formulas (for example below) but nothing
is working. (B65=level, C65=Start date. AH3= Calander Month, D65=Target

=(SUMPRODUCT(($B$65=2)*($C$65<AH3),$D$65*0%))+(SUM PRODUCT(($B$65=2)*($C$65<AH3),$D$65*75%))))+(SUMPR ODUCT(($B$65=2)*($C$65<AH3),$D$65*100%))

I just cant get it to work out what month in the cycle my guy is at and
therefore what rule to apply

Thanks for taking the time to look and for any help you can give me. I hope
I provided enough information

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mark D" wrote in message
...
Good afternoon everyone

I'll try and put this as simple as possible but have tried doing this
myself
and cannot work out how to do this.

I have a new employee - summary as follows

He starts 01/01/2010 (c65)
He is a level 2 (b65)
He has a monthly sales target of say ‚¬10,000 (d65)

However his target does not start in month 1, instaead for the first 4
months (Jan - April his target will be ‚¬0.00, From months 5 - 8 (May -
Aug)
his taget will be 75% of the ‚¬10,000 (‚¬7,500). From month 9 (Sept) it'll
be
100% of the target = ‚¬10,000

Along the top of my spreadsheet I have months (all set to the last day in
the month)

AH3 AH4 AH5 AH6
Jan 09 Feb 09 March 09 Apr 09 etc.

I have had a crack at a couple of formulas (for example below) but nothing
is working. (B65=level, C65=Start date. AH3= Calander Month, D65=Target

=(SUMPRODUCT(($B$65=2)*($C$65<AH3),$D$65*0%))+(SUM PRODUCT(($B$65=2)*($C$65<AH3),$D$65*75%))))+(SUMPR ODUCT(($B$65=2)*($C$65<AH3),$D$65*100%))

I just cant get it to work out what month in the cycle my guy is at and
therefore what rule to apply

Thanks for taking the time to look and for any help you can give me. I
hope
I provided enough information

Does this ormula help?

=IF(--TEXT($C$65,"yyyymm")<=--TEXT(AH3-DAY(AH3)+1,"yyyymm"),
LOOKUP(DATEDIF($C$65,AH3+1,"M"),{0,4,8}, {0,0.75,1})*$D$65,0)

--

HTH

Bob

"Mark D" wrote in message
...
Good afternoon everyone

I'll try and put this as simple as possible but have tried doing this
myself
and cannot work out how to do this.

I have a new employee - summary as follows

He starts 01/01/2010 (c65)
He is a level 2 (b65)
He has a monthly sales target of say ?10,000 (d65)

However his target does not start in month 1, instaead for the first 4
months (Jan - April his target will be ?0.00, From months 5 - 8 (May -
Aug)
his taget will be 75% of the ?10,000 (?7,500). From month 9 (Sept) it'll
be
100% of the target = ?10,000

Along the top of my spreadsheet I have months (all set to the last day in
the month)

AH3 AH4 AH5 AH6
Jan 09 Feb 09 March 09 Apr 09 etc.

I have had a crack at a couple of formulas (for example below) but nothing
is working. (B65=level, C65=Start date. AH3= Calander Month, D65=Target

=(SUMPRODUCT(($B$65=2)*($C$65<AH3),$D$65*0%))+(SUM PRODUCT(($B$65=2)*($C$65<AH3),$D$65*75%))))+(SUMPR ODUCT(($B$65=2)*($C$65<AH3),$D$65*100%))

I just cant get it to work out what month in the cycle my guy is at and
therefore what rule to apply

Thanks for taking the time to look and for any help you can give me. I
hope
I provided enough information

Wow, that's incredible. No idea what the below is trying to tell me but it
works.

One slight adjustment if it can be added to the below.

The formula you have given me is for a new LEVEL 2 employee only. So I need
to say IF B65=2 (apply your forumla)

IF NOT then apply this ''=IF($E44<AK$3,$D64,"")

Again thanks - Man I'm a novice at this

"Bob Phillips" wrote:

Does this ormula help?

=IF(--TEXT($C$65,"yyyymm")<=--TEXT(AH3-DAY(AH3)+1,"yyyymm"),
LOOKUP(DATEDIF($C$65,AH3+1,"M"),{0,4,8}, {0,0.75,1})*$D$65,0)

--

HTH

Bob

"Mark D" wrote in message
...
Good afternoon everyone

I'll try and put this as simple as possible but have tried doing this
myself
and cannot work out how to do this.

I have a new employee - summary as follows

He starts 01/01/2010 (c65)
He is a level 2 (b65)
He has a monthly sales target of say ?10,000 (d65)

However his target does not start in month 1, instaead for the first 4
months (Jan - April his target will be ?0.00, From months 5 - 8 (May -
Aug)
his taget will be 75% of the ?10,000 (?7,500). From month 9 (Sept) it'll
be
100% of the target = ?10,000

Along the top of my spreadsheet I have months (all set to the last day in
the month)

AH3 AH4 AH5 AH6
Jan 09 Feb 09 March 09 Apr 09 etc.

I have had a crack at a couple of formulas (for example below) but nothing
is working. (B65=level, C65=Start date. AH3= Calander Month, D65=Target

=(SUMPRODUCT(($B$65=2)*($C$65<AH3),$D$65*0%))+(SUM PRODUCT(($B$65=2)*($C$65<AH3),$D$65*75%))))+(SUMPR ODUCT(($B$65=2)*($C$65<AH3),$D$65*100%))

I just cant get it to work out what month in the cycle my guy is at and
therefore what rule to apply

Thanks for taking the time to look and for any help you can give me. I
hope
I provided enough information






.

I think this should do it

=IF(AND($B$65=2,--TEXT($C$65,"yyyymm")<=--TEXT(AH3-DAY(AH3)+1,"yyyymm")),
LOOKUP(DATEDIF($C$65,AH3+1,"M"),{0,4,8}, {0,0.75,1})*$D$65,0)


--

HTH

Bob

"Mark D" wrote in message
...
Wow, that's incredible. No idea what the below is trying to tell me but it
works.

One slight adjustment if it can be added to the below.

The formula you have given me is for a new LEVEL 2 employee only. So I
need
to say IF B65=2 (apply your forumla)

IF NOT then apply this ''=IF($E44<AK$3,$D64,"")

Again thanks - Man I'm a novice at this

"Bob Phillips" wrote:

Does this ormula help?

=IF(--TEXT($C$65,"yyyymm")<=--TEXT(AH3-DAY(AH3)+1,"yyyymm"),
LOOKUP(DATEDIF($C$65,AH3+1,"M"),{0,4,8}, {0,0.75,1})*$D$65,0)

--

HTH

Bob

"Mark D" wrote in message
...
Good afternoon everyone

I'll try and put this as simple as possible but have tried doing this
myself
and cannot work out how to do this.

I have a new employee - summary as follows

He starts 01/01/2010 (c65)
He is a level 2 (b65)
He has a monthly sales target of say ?10,000 (d65)

However his target does not start in month 1, instaead for the first 4
months (Jan - April his target will be ?0.00, From months 5 - 8 (May -
Aug)
his taget will be 75% of the ?10,000 (?7,500). From month 9 (Sept)
it'll
be
100% of the target = ?10,000

Along the top of my spreadsheet I have months (all set to the last day
in
the month)

AH3 AH4 AH5 AH6
Jan 09 Feb 09 March 09 Apr 09 etc.

I have had a crack at a couple of formulas (for example below) but
nothing
is working. (B65=level, C65=Start date. AH3= Calander Month, D65=Target

=(SUMPRODUCT(($B$65=2)*($C$65<AH3),$D$65*0%))+(SUM PRODUCT(($B$65=2)*($C$65<AH3),$D$65*75%))))+(SUMPR ODUCT(($B$65=2)*($C$65<AH3),$D$65*100%))

I just cant get it to work out what month in the cycle my guy is at and
therefore what rule to apply

Thanks for taking the time to look and for any help you can give me. I
hope
I provided enough information






.

Hi Bob

That worked great. But there was still 1 last element to the forumula below
that I need your help on.

The formula now reads if C66 = 2 apply your formula

=IF(AND($C$66=2,--TEXT($D$66,"yyyymm")<=--TEXT(N3-DAY(N3)+1,"yyyymm")),LOOKUP(DATEDIF($D$66,N3+1,"M" ),{0,4,8}, {0,0.7,1})*$F$66,0)

BUT if C66 doesnt = 2 apply this
=IF($E44<AK$3,$D64,"")

This is basically taking the employee start date, asking if it's before the
month date, apply their target in D64.

Can this be added??

Thanks again

"Bob Phillips" wrote:

I think this should do it

=IF(AND($B$65=2,--TEXT($C$65,"yyyymm")<=--TEXT(AH3-DAY(AH3)+1,"yyyymm")),
LOOKUP(DATEDIF($C$65,AH3+1,"M"),{0,4,8}, {0,0.75,1})*$D$65,0)


--

HTH

Bob

"Mark D" wrote in message
...
Wow, that's incredible. No idea what the below is trying to tell me but it
works.

One slight adjustment if it can be added to the below.

The formula you have given me is for a new LEVEL 2 employee only. So I
need
to say IF B65=2 (apply your forumla)

IF NOT then apply this ''=IF($E44<AK$3,$D64,"")

Again thanks - Man I'm a novice at this

"Bob Phillips" wrote:

Does this ormula help?

=IF(--TEXT($C$65,"yyyymm")<=--TEXT(AH3-DAY(AH3)+1,"yyyymm"),
LOOKUP(DATEDIF($C$65,AH3+1,"M"),{0,4,8}, {0,0.75,1})*$D$65,0)

--

HTH

Bob

"Mark D" wrote in message
...
Good afternoon everyone

I'll try and put this as simple as possible but have tried doing this
myself
and cannot work out how to do this.

I have a new employee - summary as follows

He starts 01/01/2010 (c65)
He is a level 2 (b65)
He has a monthly sales target of say ?10,000 (d65)

However his target does not start in month 1, instaead for the first 4
months (Jan - April his target will be ?0.00, From months 5 - 8 (May -
Aug)
his taget will be 75% of the ?10,000 (?7,500). From month 9 (Sept)
it'll
be
100% of the target = ?10,000

Along the top of my spreadsheet I have months (all set to the last day
in
the month)

AH3 AH4 AH5 AH6
Jan 09 Feb 09 March 09 Apr 09 etc.

I have had a crack at a couple of formulas (for example below) but
nothing
is working. (B65=level, C65=Start date. AH3= Calander Month, D65=Target

=(SUMPRODUCT(($B$65=2)*($C$65<AH3),$D$65*0%))+(SUM PRODUCT(($B$65=2)*($C$65<AH3),$D$65*75%))))+(SUMPR ODUCT(($B$65=2)*($C$65<AH3),$D$65*100%))

I just cant get it to work out what month in the cycle my guy is at and
therefore what rule to apply

Thanks for taking the time to look and for any help you can give me. I
hope
I provided enough information






.



.

I think so

=IF($C$66<2,IF($E44<AK$3,$D64,""),
IF(--TEXT($D$66,"yyyymm")<=--TEXT(N3-DAY(N3)+1,"yyyymm"),LOOKUP(DATEDIF($D$66,N3+1,"M") ,{0,4,8},
{0,0.7,1})*$F$66,0))

The new bit might need adapting to the actual cells.

--

HTH

Bob

"Mark D" wrote in message
...
Hi Bob

That worked great. But there was still 1 last element to the forumula
below
that I need your help on.

The formula now reads if C66 = 2 apply your formula

=IF(AND($C$66=2,--TEXT($D$66,"yyyymm")<=--TEXT(N3-DAY(N3)+1,"yyyymm")),LOOKUP(DATEDIF($D$66,N3+1,"M" ),{0,4,8},
{0,0.7,1})*$F$66,0)

BUT if C66 doesnt = 2 apply this
=IF($E44<AK$3,$D64,"")

This is basically taking the employee start date, asking if it's before
the
month date, apply their target in D64.

Can this be added??

Thanks again

"Bob Phillips" wrote:

I think this should do it

=IF(AND($B$65=2,--TEXT($C$65,"yyyymm")<=--TEXT(AH3-DAY(AH3)+1,"yyyymm")),
LOOKUP(DATEDIF($C$65,AH3+1,"M"),{0,4,8}, {0,0.75,1})*$D$65,0)


--

HTH

Bob

"Mark D" wrote in message
...
Wow, that's incredible. No idea what the below is trying to tell me but
it
works.

One slight adjustment if it can be added to the below.

The formula you have given me is for a new LEVEL 2 employee only. So I
need
to say IF B65=2 (apply your forumla)

IF NOT then apply this ''=IF($E44<AK$3,$D64,"")

Again thanks - Man I'm a novice at this

"Bob Phillips" wrote:

Does this ormula help?

=IF(--TEXT($C$65,"yyyymm")<=--TEXT(AH3-DAY(AH3)+1,"yyyymm"),
LOOKUP(DATEDIF($C$65,AH3+1,"M"),{0,4,8}, {0,0.75,1})*$D$65,0)

--

HTH

Bob

"Mark D" wrote in message
...
Good afternoon everyone

I'll try and put this as simple as possible but have tried doing
this
myself
and cannot work out how to do this.

I have a new employee - summary as follows

He starts 01/01/2010 (c65)
He is a level 2 (b65)
He has a monthly sales target of say ?10,000 (d65)

However his target does not start in month 1, instaead for the first
4
months (Jan - April his target will be ?0.00, From months 5 - 8
(May -
Aug)
his taget will be 75% of the ?10,000 (?7,500). From month 9 (Sept)
it'll
be
100% of the target = ?10,000

Along the top of my spreadsheet I have months (all set to the last
day
in
the month)

AH3 AH4 AH5 AH6
Jan 09 Feb 09 March 09 Apr 09 etc.

I have had a crack at a couple of formulas (for example below) but
nothing
is working. (B65=level, C65=Start date. AH3= Calander Month,
D65=Target

=(SUMPRODUCT(($B$65=2)*($C$65<AH3),$D$65*0%))+(SUM PRODUCT(($B$65=2)*($C$65<AH3),$D$65*75%))))+(SUMPR ODUCT(($B$65=2)*($C$65<AH3),$D$65*100%))

I just cant get it to work out what month in the cycle my guy is at
and
therefore what rule to apply

Thanks for taking the time to look and for any help you can give me.
I
hope
I provided enough information






.



.

I'm thinking of changing the name of this spreadsheet to the ''Couldn't have
done this with without Bob's help'' :-)

Thank again. Works great

"Bob Phillips" wrote:

I think so

=IF($C$66<2,IF($E44<AK$3,$D64,""),
IF(--TEXT($D$66,"yyyymm")<=--TEXT(N3-DAY(N3)+1,"yyyymm"),LOOKUP(DATEDIF($D$66,N3+1,"M") ,{0,4,8},
{0,0.7,1})*$F$66,0))

The new bit might need adapting to the actual cells.

--

HTH

Bob

"Mark D" wrote in message
...
Hi Bob

That worked great. But there was still 1 last element to the forumula
below
that I need your help on.

The formula now reads if C66 = 2 apply your formula

=IF(AND($C$66=2,--TEXT($D$66,"yyyymm")<=--TEXT(N3-DAY(N3)+1,"yyyymm")),LOOKUP(DATEDIF($D$66,N3+1,"M" ),{0,4,8},
{0,0.7,1})*$F$66,0)

BUT if C66 doesnt = 2 apply this
=IF($E44<AK$3,$D64,"")

This is basically taking the employee start date, asking if it's before
the
month date, apply their target in D64.

Can this be added??

Thanks again

"Bob Phillips" wrote:

I think this should do it

=IF(AND($B$65=2,--TEXT($C$65,"yyyymm")<=--TEXT(AH3-DAY(AH3)+1,"yyyymm")),
LOOKUP(DATEDIF($C$65,AH3+1,"M"),{0,4,8}, {0,0.75,1})*$D$65,0)


--

HTH

Bob

"Mark D" wrote in message
...
Wow, that's incredible. No idea what the below is trying to tell me but
it
works.

One slight adjustment if it can be added to the below.

The formula you have given me is for a new LEVEL 2 employee only. So I
need
to say IF B65=2 (apply your forumla)

IF NOT then apply this ''=IF($E44<AK$3,$D64,"")

Again thanks - Man I'm a novice at this

"Bob Phillips" wrote:

Does this ormula help?

=IF(--TEXT($C$65,"yyyymm")<=--TEXT(AH3-DAY(AH3)+1,"yyyymm"),
LOOKUP(DATEDIF($C$65,AH3+1,"M"),{0,4,8}, {0,0.75,1})*$D$65,0)

--

HTH

Bob

"Mark D" wrote in message
...
Good afternoon everyone

I'll try and put this as simple as possible but have tried doing
this
myself
and cannot work out how to do this.

I have a new employee - summary as follows

He starts 01/01/2010 (c65)
He is a level 2 (b65)
He has a monthly sales target of say ?10,000 (d65)

However his target does not start in month 1, instaead for the first
4
months (Jan - April his target will be ?0.00, From months 5 - 8
(May -
Aug)
his taget will be 75% of the ?10,000 (?7,500). From month 9 (Sept)
it'll
be
100% of the target = ?10,000

Along the top of my spreadsheet I have months (all set to the last
day
in
the month)

AH3 AH4 AH5 AH6
Jan 09 Feb 09 March 09 Apr 09 etc.

I have had a crack at a couple of formulas (for example below) but
nothing
is working. (B65=level, C65=Start date. AH3= Calander Month,
D65=Target

=(SUMPRODUCT(($B$65=2)*($C$65<AH3),$D$65*0%))+(SUM PRODUCT(($B$65=2)*($C$65<AH3),$D$65*75%))))+(SUMPR ODUCT(($B$65=2)*($C$65<AH3),$D$65*100%))

I just cant get it to work out what month in the cycle my guy is at
and
therefore what rule to apply

Thanks for taking the time to look and for any help you can give me.
I
hope
I provided enough information






.



.



.