working VAT in worksheet

[Tina Harrison]

Hi all

I have a worksheet like below, It might be easy for some but i'm a bit rusty.

F8 F9 F10
Net VAT Total

What i need is a function that will allow me to enter a amount in either Net
or Total and it would work out Vat and either Net or Total depending where i
have put the amount. Hope this makes sense!

Many thanks

Tina

 

[Toppers]

=IF($F$10="",rond(F8*vat,2),round(($F$10/(1+vat)*vat),2))

Vat is named range containing VAT%

 

[Peo Sjoblom]

You can't have a function in a cell then enter something in that same cell

So if you create a 4th and 5th column

to get the Net if you enter something in the total use

=F10/(1+17.5%)

to get the VAT

=F10-(F10/(1+17.5%))

If you put in the Net in F8


then use

=F8*17.5%

to get the VAT

and

=F8*(1+17.5%)

to get the total

 

[Tina Harrison]

Thank you

do you mean where you wrote vat you input 17.5% ?
Sorry if this is simple but i am very very rusty!!

Many thanks

Tina

 

[Toppers]

You can replace with 17.5% or better still with a cell containing the VAT
value.

=IF($F$10="",round(F8*X1,2),round(($F$10/(1+X1)*X1),2))

X1 contains your VAT %.

 

[Toppers]

See Peo's comments: I had overlooked the fact you wanted to calculate the NET
or TOTAl as well as VAT! I just calculated VAT from either.

 

[Tina Harrison]

I don't think i explained myself very well.
I need to enter data in either F9 or H9
so if i enter data in F9 the answer will be in H9 and vise versa
and then maybe enter a formula in G10 to show the VAT.
Can this be done?

Thank you for your help

Tina

 

[Peo Sjoblom]

No, as I said

"You can't have a function in a cell then enter something in that same cell"

Meaning that if you have a function in F12 to calculate the total then you
can't type in the total in the same cell, once you do you clear out the
function.


--
Regards,

Peo Sjoblom

 

[Sandy Mann]

To do what you want you would need VBA. Right-click on the sheet tab and
select View Code then enter this Macro:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim VAT As Double
VAT = 0.175

If Intersect(Target, Range("F8:F10")) Is Nothing Then Exit Sub
If Target.Value = "" Then Exit Sub

Application.EnableEvents = False

If Target.Row = 8 Then
Cells(9, 6).Value = Application.Round(Target.Value * VAT, 2)
Cells(10, 6).Value = Target.Value + Cells(9, 6).Value
End If

If Target.Row = 9 Then
Cells(8, 6).Value = Application.Round(Target.Value / VAT, 2)
Cells(10, 6).Value = Target.Value + Cells(8, 6).Value
End If

If Target.Row = 10 Then
Cells(8, 6).Value = Application.Round(Target.Value / (1 + VAT), 2)
Cells(9, 6).Value = Target.Value - Cells(8, 6).Value
End If

Application.EnableEvents = True


End Sub

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk