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

    Using a formula instead of Goal Seek

    Hi All,

    I have a scenario where I know the amount someone wishes to pay me but I need to calculate the net amount before VAT and other associated charges.

    As the other charges are variables but the VAT is always a % I need to work out the correct NET amount before applying the VAT @ 20% to it.

    NET £60,203.25
    Other 1 2,306.10
    OTHER 2 450.00
    VAT 12,040.65
    All in Price £75,000.00

    I can do this in Goal Seek but I need a formula to do it.

    Iím not sure it is possible but before I give up thought I would ask the community.

    Regards,

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    See attached file.

    This might help.
    If you require more info, please let me know

    zeddy
    Attached Files Attached Files

  3. #3
    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
    It seems to me based on your numbers that:
    Net = (Total-Other1-Other2)/(1+20%)

    Steve

  4. #4
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Hi Zeddy,

    Thanks for the file; unfortunately this doesnít do what I need it to do.

    NET £121,178.86
    Other 1 4,135.37 This amount here is a sliding scale % formula
    OTHER 2 450.00 This amount is just an input amount
    VAT 24,235.77 This amount is 20% on the NET amount.
    All in Price £150,000.00


    I need to calculate the net price but all I know if the All in price, as the Other 1 amount is a formula itself and is based on the net amount I canít calculate it.

    I cant upload a spreedsheet from my machine but if you replicated the above ini an ecxel sheet with Other 1 being :=IF(B3<=50000,B3*4%,IF(B3<=200000,(B3-50000)*3%+50000*4%,IF(B3<=350000,(50000*4%)+(15000 0*3%)+(B3-200000)*1%,IF(B3<=500000,(50000*4%)+(150000*3%)+(1 50000*1%)+(B3-350000)*0.5%,IF(B3>500000,(50000*4%)+(150000*3%)+( 150000*1%)+(150000*0.5%)+(B3-500000)*0.25%)))))

    And the VAT amount being 0.2*B3

    And B3 being the NET amount

    And All In Price being the sum of all the amounts

    In Goal seek i select cell B7 ALL IN PRICE, and say i want that to be 150,000 by changing cell B3 i.e. the input net amount it works it out.

    Hope this makes sense and thanks for helping.

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    In my previous file sent, if you set the VAT% to 0% for your OTHER items you get the answers you gave.
    I put in the flexibilty to have the VAT rate set for the OTHER items.
    So, as per the attached file, with your new example, and setting the VAT rate to 0% for your OTHER items, you get the values you require.

    zeddy
    Attached Files Attached Files

  6. #6
    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
    How about using iterative calculations? 10 is more than enough to converge to a solution.

    Steve
    Attached Files Attached Files

  7. #7
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Thanks Steve, i will look into this.

    I've been trying to do it by useing VB for Goal Seek instead:

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Row = 2 And Target.Column = 2 Then
    Range("B13").GoalSeek Goal:=Range("B2").Value, _
    ChangingCell:=Range("B2")
    End If
    End Sub

    So, what i was trying to do is when Cell B2 has a value entered I want B13 to become that number with the goal seek amount being B2 by changing B2. i think this may be easier by cant seem to get it to work correctly but will carry on until i do .

    Thanks

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    I believe the attached file will now give you what you want, just using formulas.
    (The rate tables are hidden).


    zeddy
    Attached Files Attached Files
    Last edited by zeddy; 2013-03-08 at 11:05.

  9. #9
    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
    I don't know how your setup is, but the code I created works with the setup in this workbook.

    Steve
    Attached Files Attached Files
    Last edited by sdckapr; 2013-03-08 at 16:23. Reason: Corrected macro in file (Thanks Zeddy)

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Steve

    I opened your xlsm file, changed the value in cell [B1] to 75,000, but didn't get the expected answer???

    My file uses no vba, just formulas in adjacent hidden columns, no iterations required.
    (My hidden column values allows easier change of %rates and trigger levels)

    zeddy

  11. #11
    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
    Zeddy,
    I corrected the code in the file and attached a new one. I had moved some of the cells and forgot to modify the ranges in the code...

    Thanks for bringing this to my attention!
    Steve

    PS your workbook does not calculate correctly for total values under 62,450. It seems that the values in your formulas in row 5 are negative, so the "Other1" value is miscalculated...

    PSS. I think the problem is the formula in J4. It should be: =MIN(H4/(1.2+M4),O4)
    Last edited by sdckapr; 2013-03-08 at 17:10.

  12. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Steve

    Top marks. My formula in cell [J4] missed the VAT element as spotted by you.
    I have attached the fixed file.

    zeddy
    Attached Files Attached Files

Posting Permissions

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