HELP

[JOSE V via OfficeKB.com]

Need a formula for this: Going crazy!
First come up with a cell stating (number 400) and reference it in the
calculation.
5 sellers
a bonus calculated as follows;
$20 for each boat over 150
an additional 20% of this amount (units over 150 X $20)added to amount of
years (10) the person has been a seller.

 

[Bob Phillips]

It may be just me who finds that confusing, but can you give some data
example and a result walkthrough?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[JOSE V via OfficeKB.com]

It may be just me who finds that confusing, but can you give some data
example and a result walkthrough?

Need a formula for this: Going crazy!
First come up with a cell stating (number 400) and reference it in the

[quoted text clipped - 4 lines]

an additional 20% of this amount (units over 150 X $20)added to amount of
years (10) the person has been a seller.

NAME BOATS SOLD YEARS SERVICE
BONUS 150
ANTHONY PETRINO 140 4

JOHN PETRICK 200 2 "
ANTHONY CLARK 221 15 "
DAVID JONES 110 3
RODNEY YOUNG 109 2



IMAGINE THE COLUMNS A1 THRU A4. NAME, BOATS SOLD, YEARS OF SERVICE, AND BONUS.
NEED TO COME UP WITH THE BONUS FOR EACH ONE. REFERENCING CELL A4 (BONUS 150.)
IN THE COMPUTATION. BONUS CACULATIONS ARE $20 for each boat over 150
an additional 20% of this amount (units over 150 X $20)added to amount of
years the person has been a seller.

THANKS IN ADVANCE.

 

[Bob Phillips]

Jose,

Here is the formula, based upon John Petrino's row

=MAX(0,B2-150)*20*(1+C2*20%)

what does 2" signify, is that months as against years. If so, will it be
entered as 2" or some other way?

--

HTH

RP
(remove nothere from the email address if mailing direct)

It may be just me who finds that confusing, but can you give some data
example and a result walkthrough?

Need a formula for this: Going crazy!
First come up with a cell stating (number 400) and reference it in the

[quoted text clipped - 4 lines]

an additional 20% of this amount (units over 150 X $20)added to amount of
years (10) the person has been a seller.

NAME BOATS SOLD YEARS SERVICE
BONUS 150
ANTHONY PETRINO 140 4

JOHN PETRICK 200 2 "
ANTHONY CLARK 221 15 "
DAVID JONES 110 3
RODNEY YOUNG 109 2



IMAGINE THE COLUMNS A1 THRU A4. NAME, BOATS SOLD, YEARS OF SERVICE, AND BONUS.
NEED TO COME UP WITH THE BONUS FOR EACH ONE. REFERENCING CELL A4 (BONUS 150.)
IN THE COMPUTATION. BONUS CACULATIONS ARE $20 for each boat over 150
an additional 20% of this amount (units over 150 X $20)added to amount of
years the person has been a seller.

THANKS IN ADVANCE.

 

[joeu2004]

JOSE V via OfficeKB.com wrote (modified for readability):

A B C D E
BONUS 150
BOATS YEARS
NAME SOLD SERVICE BONUS
ANTHONY PETRINO 140 4
JOHN PETRICK 200 2
ANTHONY CLARK 221 15
DAVID JONES 110 3
RODNEY YOUNG 109 2

IMAGINE THE COLUMNS A1 THRU A4. NAME, BOATS SOLD,
YEARS OF SERVICE, AND BONUS.

In my example, the first row of data (Petrick) are in A5:E5,
with A5 and B5 merged. The bonus threshold (150) is in $B$1.

NEED TO COME UP WITH THE BONUS FOR EACH ONE. REFERENCING CELL A4 (BONUS 150.)
IN THE COMPUTATION. BONUS CACULATIONS ARE $20 for each boat over 150
an additional 20% of this amount (units over 150 X $20)added to amount of
years the person has been a seller.

The natural language description of the arithmetic is
imprecise. It would have been helpful if you indicated
the expected result for Petrick, for example.

I think one of the following formulas will work for you,
put into column E (BONUS) is:

=(C5>$B$1)*ROUND(20*(C5-$B$1)+20%*(C5-$B$1)+D5,0) [Petrick=$1012]
=(C5>$B$1)*ROUND(20*(C5-$B$1)+20%*(C5-$B$1+D5),0) [Petrick=$1010]
=(C5>$B$1)*ROUND(20*(C5-$B$1+20%*(C5-$B$1)+D5),0) [Petrick=$1240]
=(C5>$B$1)*ROUND(20*(C5-$B$1+20%*(C5-$B$1+D5)),0) [Petrick=$1208]

If you prefer a blank instead of zero in the BONUS column,
or if you simply want something that is easier to understand,
you can use an IF() function. For example:

=IF(C5 <= $B$1, "", ROUND(20*(C5-$B$1)+20%*(C5-$B$1)+D5,0))

 

[joeu2004]

JOSE V via OfficeKB.com wrote (modified for readability):

Oops! I misread the description of the arithmetic.
That simplifies the number of interpretations and
changes the formula as follows:

=(C5>$B$1)*ROUND(20*(C5-$B$1)+20%*20*(C5-$B$1)+D5,0) [Petrick=$1202]
=(C5>$B$1)*ROUND(20*(C5-$B$1)+20%*(20*(C5-$B$1)+D5),0) [Petrick=$1200]

I wrote those so that you can see the difference in
interpretation easily. Of course, the first formula
can be written more simply as:

=(C5>$B$1)*ROUND((1+20%)*20*(C5-$B$1)+D5,0)

I hope that is closer to what you are looking for.

 

[JOSE V via OfficeKB.com]

JOSE V via OfficeKB.com wrote (modified for readability):

Oops! I misread the description of the arithmetic.
That simplifies the number of interpretations and
changes the formula as follows:

=(C5>$B$1)*ROUND(20*(C5-$B$1)+20%*20*(C5-$B$1)+D5,0) [Petrick=$1202]
=(C5>$B$1)*ROUND(20*(C5-$B$1)+20%*(20*(C5-$B$1)+D5),0) [Petrick=$1200]

I wrote those so that you can see the difference in
interpretation easily. Of course, the first formula
can be written more simply as:

=(C5>$B$1)*ROUND((1+20%)*20*(C5-$B$1)+D5,0)

I hope that is closer to what you are looking for.

I will give it a try. Thanks again for the help. Jose