Excel 2007 VB or Formula For Dollar Payouts


Hoping someone can assist with either a VB code or formula.  In the attached sample file you'll see a dollar amounts in columns J & K.  I need something that will look at column J and calculate/pay an amount based on $0.50 per $1.00 in each row, up to a maximum amount of $250.00, and place the figure in column L.  If the calculated amount does not match what is in column K then place the difference between the two totals in column M.  The concept is for the end user to look at column M and know how much remaining needs to be paid out.

For example, in the attached workbook:
L2 would equal $250.00 ($2110 * .50 = $1055 but max is $250.00).     M2 would equal $100 (difference between L2 ($250) and K2 ($150)
L3 would equal $6.00 ($12.23 *.50 (dollar only) = $6.00.  M3 would equal $6.00 since $0 in K3

Any assistance would be greatly appreciated!

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Your sample has not been uploaded properly as an excel file, can you adjust?

Please see attached. This IF statement is used to get you the value needed in column L:


Column M formula is:



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EscanabaAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.