formula to work out VAT from Total not subtotal

[n1kk1]

Hello, is there a formula I can use to work out what the subtotal would be after the VAT if I only ave the Total

For instance the subtotal is 79.17
VAT - 15.83
Total - 95

In some instances I only have the total, as I cannot minus 20% from this as the VAT is calculated from the subtotal does anyone know how I can use a formula to calculate what the subtotal and VAT is from the Total?

What I was doing is =IF(M72,0.2*M72,"") column M being the TOTAL but obviously this is giving me 20% of the total

Thanks

[Richard Buttrey]

Hi
=M2-M2/1.2

[daddylonglegs]

You could also just divide by 6 for the VAT, i.e.

=IF(M72,M72/6,"")

[n1kk1]

perfect thanks Richard! Is there a way do you know that I can automatically have the subtotal appear in my total column (ie the total minus VAT) without pasting as values this new VAT column and and subtracting it from the total?

I hope that makes sense!

[Richard Buttrey]

Hi,

I'm not sure I understand what you mean.

If your Gross total is in M73, the VAT formula in M72, then why not use (in the M71 cell presumably) =M73-M72
If this isn't what you mean, please upload a workbook

Regards

[n1kk1]

I was hoping there was some other way to do this as I have lots of columns that I need to apply this to and inserting a new column between each one then hardcoding the VAT to subtract from the total to get the subtotal is gong to be a bit of a nightmare - if it is the only way though!

Even if I hardcode the VAT column is there an automatic way I can highlight the M column insert an =before the current total to automatically minus the VAT column?

[Richard Buttrey]

I can't visualise your layout.

Upload your workbook as requested and explain where you are trying to insert new columns, what you expect to happen, what you mean by hardcoding, and a before and after situation showing the results that you expect to see.

Regards

[n1kk1]

hopefully this is clearer

column L = VAT (=M341-M341/1.2)
Column M - Total

I would ideally like to convert column M to be sub total without adding a separate column (ie column O to create the subtotal by =M-L)

I have lots of these columns so it would be a bit of a nightmare to insert columns in between each one to create the subtotal.

I as hoping I could copy and paste special values column L and somehow convert column M to automatically subtract (=M-L)

I hope that makes sense, sorry for the confusion

[Richard Buttrey]

Sorry, still don't understand. If M is the total and you're wanting to convert it to subtotal from where is your subtotal column L going to get it's information?
As I said upload a workbook.

[royUK]

This formula will calculatee the vat amount from the total

=ROUND(B2/(1+C2)*C2,2)

Total in B2
VAT rate in C2

Subtract tax from gross to get the net.

Pete_UK provided most of the formula on Code Cage Forum