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
Medium Priority
434 Views
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
Question by:Escanaba

LVL 39

Expert Comment

ID: 40189191

Thomas
0

LVL 22

Accepted Solution

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

ID: 40196949
Thanks!
0

## Featured Post

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.
###### Suggested Courses
Course of the Month21 days, 6 hours left to enroll