Prev: "manual page breaks error"
Next: Wonderware Active Factory Add In Problem
From: Tina Harrison on 8 Aug 2007 14:22
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


From: Toppers on 8 Aug 2007 14:44
=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
>
>
From: Peo Sjoblom on 8 Aug 2007 14:55
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" <TinaHarrison(a)discussions.microsoft.com> wrote in message
news:CAEAB0AD-6248-4AF9-B007-908F0D2C7DCB(a)microsoft.com...
> 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
>
>


From: Tina Harrison on 8 Aug 2007 14:56
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
> >
> >
From: Toppers on 8 Aug 2007 15:02
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
> > >
> > >
 |  Next  |  Last
Pages: 1 2
Prev: "manual page breaks error"
Next: Wonderware Active Factory Add In Problem