Results 1 to 9 of 9

Thread: Formula issue.

  1. #1
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Formula issue.

    Hi All,

    I have a formula issue Iím hoping can be written without the need for VBA. No-one I have spoken to yet has been able to resolve it butÖ.. To date someone on here has always managed to find a solution to my problems 

    Net Amount 121,613.82
    Other Variable Charge 4,063.41
    VAT 24,322.76
    Total 150,000.00


    I know the amount someone wants to pay me, i.e. 150,000, and in this instance VAT is 20%, of the net amount and the other variable charge is exactly that, each day the value can change and is a sliding scale amount based on the net amount also.

    Is there a way that a formula can work out the net amount based on the 150,000 amount taking into account the other variable amount? The above is what should happen.

    Thanks

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If I understand you have:
    Net + Other + Vat = Total

    Vat = 20%*Net so you have:
    Net + Other + 20%*Net = Total

    So you want to know, given total and other, what the Net is.

    Since Net + Net *20% is the same as Net * (1+ 20%) = Net*1.20, you can rewrite:
    Net*1.20 + Other = Total
    Net*1.20 = Total - other
    Net = (Total - Other) / 1.20

    Steve
    Last edited by sdckapr; 2014-01-23 at 07:38.

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    and in this instance VAT is 20%
    Since it sounds as though the percentage for the VAT may change. The formula you would need is:

    =IF(OR(B2=0,B3=0,B4=0),"",(B4-B2)/(1+(B3/100))) or similar if you set your sheet up like in the image. The Net Value will not be calculated unless all of the needed information has been entered. The format for VAT value in this example is "General"

    Maud

    Net1.png

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 985 Times in 915 Posts
    If you don't know the net amount and the variable charge is based on the net amount, you have 2 variables and 2 fixed. Is the variable linked directly to the net amount?

    cheers, Paul

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    My impression was the VAT was based on the net amount (20% of the Net). The "other variable charge" would be a given (along with the total).

    Steve

  6. #6
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 985 Times in 915 Posts
    the other variable charge is exactly that, each day the value can change and is a sliding scale amount based on the net amount also.
    This is the bit that makes me think there is more to it than we suspect.

    cheers, Paul

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Perhaps. But if we get that percentage it can be included as well (we must either have info on the number or the percentage):
    Instead of:
    Net = (Total - Other) / 1.20

    we can use something like:
    Net = Total / (1+ vat% + other%)

    We will have to wait until stimpsondi clarifies.

    Steve

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Is this related to your post from Aug 2013 at http://windowssecrets.com/forums/sho...d-of-Goal-Seek ? That has several solutions with variable "other values" if it is more complex than your indicate in your original post.

    Steve

  9. #9
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 985 Times in 915 Posts
    Must be an echo.

    cheers, Paul

Posting Permissions

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