Any Excel wizards here?

sHo0sH · 29 Jul 2011 at 13:49

Need help with a quick formula,

Basically I need to work out the Vat on some items, but the original cost without vat for other items.

It would be a two way formula, so if I knew the net price, it would work out the vat into the column next to it, then the gross into the final column, if I only knew the gross price it would work out the vat and the net into the previous columns.

NET | VAT | GROSS

Is this possible? am I making any sense?

Thanks for any help in advance!

div0 · 29 Jul 2011 at 14:59

You could just create a circular reference between the NET and GROSS columns:

NET: =GROSS/(1+VAT)
GROSS: =NET*(1+VAT)

Then when you type the value in the right column, the other is automatically calculated.

It's not normally something I'd recommend, but might suit your need.

Otherwise, you'd either have to do this with a Macro or add some extra columns, to avoid a circular reference.

It's a bit clunky but you could do something like:

NET PAID | GROSS PAID | VAT | NET | GROSS

The formula in the NET column would then be:

=IF(A2<>"",A2,IF(B2<>"",B2/(1+C2), "Both cells blank"))

And for GROSS, it would be:

=IF(A2<>"",A2*(1+C2),IF(B2<>"",B2, "Both cells blank"))

This would do what you want, assuming you put the value paid in the right column (A or B).

Sin_Chase · 29 Jul 2011 at 15:16

The calcs are easy enough, but how do you want to implement it? Personally I would do it in VBA.

div0 · 29 Jul 2011 at 16:16

I'd do it with a Macro/VBA too, but some people aren't keen if its not something they'll need to do often.

Just depends on what he's trying to do, which is why I tried to give a few different options.

Sin_Chase · 29 Jul 2011 at 16:30

Just knocked something quick up for you. Not sure how much error catching you want or condition handling.

Code
Layout
Result
Example WorkBook

This works from a strict layout of Headings > Rows of products. It then flies through all rows till no more are found, if the layout is not strict then it will go pretty crazy!

You could code it to work from a selection instead however. Just an example of how you might do it.

sHo0sH · 30 Jul 2011 at 09:46

Thanks for the replies guys, I will take a look and see if this works - really appreciate the help!