+ Reply to Thread
Results 1 to 5 of 5

Logical calculation of fees and currencies

  1. #1
    Registered User
    Join Date
    11-17-2009
    Location
    Cambodia
    MS-Off Ver
    Excel 2003
    Posts
    5

    Logical calculation of fees and currencies

    Hello everyone!

    So I have a pretty basic expenses sheet that looks like this:

    Payment method | Gross amount | Currency | Fees | Net amount | Total to date

    So my idea was to calculate the fees based on the "Payment method" field - eg. I'll input somewhere the values for Payment method and what fee they actually represent. For example if the Payment method is say "cash" then the fee is =0, if the payment method is "credit card" then the fee is 0.5% and inside the "Fees" column it would calculate the fee based on that value and the "Gross amount".

    Then for "Currencies" - I'm working with two currencies - USD and EUR. My basic/main currency is EUR so in the "Total to date" column I want everything to be in EUR - eg. it will have to re-calculate the "Net amount" based on the "Currency" column and add that to the "Total to date".

    Thanks in advance everyone!!!

  2. #2
    Forum Contributor
    Join Date
    12-02-2008
    Location
    India
    Posts
    118

    Re: Logical calculation of fees and currencies

    Hey!
    Assuming that only wot you have given in ur example would be used for this sheet, i've worked out a sample... Please have a look...

    However, if there are more options with Fees as per Payment Method ( i mean other than cash n credit card), Currency (other than USD and EUR) and Exchange/conversion rate (like a daily updated value), then This wont really be suitable...

    Hope it gives u some start atleast...

    Regards
    Mohit

    Btw, u wud have to enter the conversion value in F2 where i've written "conversion rate" to convert USD to EUR...
    Attached Files Attached Files
    Last edited by mohitspamz; 11-17-2009 at 03:03 PM.

  3. #3
    Registered User
    Join Date
    11-17-2009
    Location
    Cambodia
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Logical calculation of fees and currencies

    Thank you mohitspamz for such a FAST response!

    It works really nice but as you mentioned there might be more than these two Payment methods so is there a way to be able to work with an indefinite number of values or at least a few more than 2?

    Thanks!

    EDIT: OK. I got it how to make it with 7 values:

    =IF((A4="Cash");B4*0;IF((A4="Credit Card");B4*5;IF((A4="Wire");B4*10)))
    But... that still means I only have 7 values, and I'll have to change the formula in ALL fields each time I add new method...
    Last edited by bakuvi; 11-17-2009 at 03:30 PM.

  4. #4
    Forum Contributor
    Join Date
    12-02-2008
    Location
    India
    Posts
    118

    Re: Logical calculation of fees and currencies

    Hey!
    If its more than 2 then u shud use vlookup...

    I've attached a sample... please check...

    There has to be a reference table, u cud use a seperate sheet to make this table so it wont be visible in ur normal course of work, and u can add as many methods of payment as and when necessary....

    Regards
    Mohit
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-17-2009
    Location
    Cambodia
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Logical calculation of fees and currencies

    Quote Originally Posted by mohitspamz View Post
    Hey!
    If its more than 2 then u shud use vlookup...

    I've attached a sample... please check...

    There has to be a reference table, u cud use a seperate sheet to make this table so it wont be visible in ur normal course of work, and u can add as many methods of payment as and when necessary....

    Regards
    Mohit
    Thank you so much Mohit!

    That works exactly the way I'd imagined it!

    Thank you once again - you saved me hours of frustration!

    God bless you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1