I need to calculate a number in a cell based on IF formula. If salary is $20,000,000 over allowed baseline - then penalty is $4.75. At each additional $5,000,000 penalty increases by factor of $.50. So in perfect world a formula to calculate these incremental values.
$20,000,000 over allowed baseline salary times $4.75 penalty
$25,000,000 times $5.25 penalty
$30,000,000 times $5.75 penalty
$35,000,000 times $6.25 penalty
Include every $5,000,000 increment to $60,000,000
Lastly, if salary is $21,000,000 for example that penalty should be approx. $4.85? This indicates 20% of .50 as an increment. $22,000,000 would be 40% of .50 or approx. $4.95 x $22,000,000. And so on and on. Is this correct? How may I write this formula that will calculate number based on figure in another cell? I will have one cell for total salary, another cell for final penalty given rate above. Thanks!
Not nimble enough with Excel I've given up after several days.
A cell that automatically calculates value based on number in another cell.
$20,000,000 over allowed baseline salary times $4.75 penalty
$25,000,000 times $5.25 penalty
$30,000,000 times $5.75 penalty
$35,000,000 times $6.25 penalty
Include every $5,000,000 increment to $60,000,000
Lastly, if salary is $21,000,000 for example that penalty should be approx. $4.85? This indicates 20% of .50 as an increment. $22,000,000 would be 40% of .50 or approx. $4.95 x $22,000,000. And so on and on. Is this correct? How may I write this formula that will calculate number based on figure in another cell? I will have one cell for total salary, another cell for final penalty given rate above. Thanks!
Not nimble enough with Excel I've given up after several days.
A cell that automatically calculates value based on number in another cell.