| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
working VAT in worksheet
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
working VAT in worksheet
=IF($F$10="",rond(F8*vat,2),round(($F$10/(1+vat)*vat),2))
Vat is named range containing VAT% "Tina Harrison" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
working VAT in worksheet
=IF($F$10="",rond(F8*vat,2),round(($F$10/(1+vat)*vat),2))
Vat is named range containing VAT% "Tina Harrison" wrote: 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
working VAT in worksheet
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 -- Regards, Peo Sjoblom "Tina Harrison" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
working VAT in worksheet
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 -- Regards, Peo Sjoblom "Tina Harrison" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
working VAT in worksheet
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" wrote: =IF($F$10="",rond(F8*vat,2),round(($F$10/(1+vat)*vat),2)) Vat is named range containing VAT% "Tina Harrison" wrote: 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
working VAT in worksheet
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" wrote: =IF($F$10="",rond(F8*vat,2),round(($F$10/(1+vat)*vat),2)) Vat is named range containing VAT% "Tina Harrison" wrote: 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
working VAT in worksheet
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 %. "Tina Harrison" wrote: 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" wrote: =IF($F$10="",rond(F8*vat,2),round(($F$10/(1+vat)*vat),2)) Vat is named range containing VAT% "Tina Harrison" wrote: 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
working VAT in worksheet
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 %. "Tina Harrison" wrote: 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" wrote: =IF($F$10="",rond(F8*vat,2),round(($F$10/(1+vat)*vat),2)) Vat is named range containing VAT% "Tina Harrison" wrote: 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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
working VAT in worksheet
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. "Toppers" wrote: 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 %. "Tina Harrison" wrote: 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" wrote: =IF($F$10="",rond(F8*vat,2),round(($F$10/(1+vat)*vat),2)) Vat is named range containing VAT% "Tina Harrison" wrote: 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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
working VAT in worksheet
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. "Toppers" wrote: 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 %. "Tina Harrison" wrote: 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" wrote: =IF($F$10="",rond(F8*vat,2),round(($F$10/(1+vat)*vat),2)) Vat is named range containing VAT% "Tina Harrison" wrote: 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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
working VAT in worksheet
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" wrote: 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 -- Regards, Peo Sjoblom "Tina Harrison" wrote in message ... 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 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
working VAT in worksheet
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" wrote: 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 -- Regards, Peo Sjoblom "Tina Harrison" wrote in message ... 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 |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
working VAT in worksheet
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 "Tina Harrison" wrote in message ... 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" wrote: 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 -- Regards, Peo Sjoblom "Tina Harrison" wrote in message ... 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 |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
working VAT in worksheet
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 "Tina Harrison" wrote in message ... 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" wrote: 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 -- Regards, Peo Sjoblom "Tina Harrison" wrote in message ... 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 |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
working VAT in worksheet
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 Replace @mailinator.com with @tiscali.co.uk "Tina Harrison" wrote in message ... 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" wrote: 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 -- Regards, Peo Sjoblom "Tina Harrison" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copied worksheet not working | Excel Discussion (Misc queries) | |||
| Tab feature no longer working on worksheet | Excel Worksheet Functions | |||
| My worksheet has turned grey and is not working | Excel Worksheet Functions | |||
| Working in a Worksheet that has protection on | Excel Worksheet Functions | |||
| how to prevent worksheet scroll bar to be bigger than working are. | Excel Worksheet Functions | |||
Linear Mode
