Thread: HELP
View Single Post
  #5   Report Post  
 
Posts: n/a
Default

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))