Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel 2007 VB or Formula For Dollar Payouts

Posted on 2014-07-10
3
Medium Priority
?
434 Views
Last Modified: 2014-07-15
Hello,

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!

Thanks
--AFSUSERS1-User1-rvb0508-EE-Example-Jul
0
Comment
Question by:Escanaba
3 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 40189191
Your sample has not been uploaded properly as an excel file, can you adjust?

Thomas
0
 
LVL 22

Accepted Solution

by:
Flyster earned 2000 total points
ID: 40189733
Please see attached. This IF statement is used to get you the value needed in column L:

=IF(J2*0.5>250,250,INT(J2*0.5))

Column M formula is:

=IF(K2=L2,"",L2-K2)

Flyster
DollarPayouts.xlsx
0
 
LVL 1

Author Closing Comment

by:Escanaba
ID: 40196949
Thanks!
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question