Excel 2007 VB or Formula For Dollar Payouts

Posted on 2014-07-10
Last Modified: 2014-07-15

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!

Question by:Escanaba
    LVL 39

    Expert Comment

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

    LVL 22

    Accepted Solution

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


    Column M formula is:


    LVL 1

    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now