Results 1 to 12 of 12

20130308, 06:18 #1
 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,

20130308, 07:25 #2
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,386
 Thanks
 164
 Thanked 633 Times in 601 Posts
Hi
See attached file.
This might help.
If you require more info, please let me know
zeddy

20130308, 08:01 #3
 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 = (TotalOther1Other2)/(1+20%)
Steve

20130308, 08:47 #4
 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,(B350000)*3%+50000*4%,IF(B3<=350000,(50000*4%)+(15000 0*3%)+(B3200000)*1%,IF(B3<=500000,(50000*4%)+(150000*3%)+(1 50000*1%)+(B3350000)*0.5%,IF(B3>500000,(50000*4%)+(150000*3%)+( 150000*1%)+(150000*0.5%)+(B3500000)*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.

20130308, 09:07 #5
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,386
 Thanks
 164
 Thanked 633 Times in 601 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

20130308, 10:07 #6
 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

20130308, 10:45 #7
 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

20130308, 11:00 #8
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,386
 Thanks
 164
 Thanked 633 Times in 601 Posts
Hi
I believe the attached file will now give you what you want, just using formulas.
(The rate tables are hidden).
zeddyLast edited by zeddy; 20130308 at 11:05.

20130308, 12:00 #9
 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.
SteveLast edited by sdckapr; 20130308 at 16:23. Reason: Corrected macro in file (Thanks Zeddy)

20130308, 12:22 #10
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,386
 Thanks
 164
 Thanked 633 Times in 601 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

20130308, 16:25 #11
 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; 20130308 at 17:10.

20130308, 17:39 #12
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,386
 Thanks
 164
 Thanked 633 Times in 601 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