Calculate VAT across a range of cells

**footsoldier** · 06-13-2013, 12:11 PM

Hi

I am creating a budgeting spreadsheet.

Users will enter data (net of VAT) in various columns eg Purchase of Hardware, Purchase of software , Consultancy Fees , etc etc. At the far right end of the sheet I have a VAT column and a total column.

I need a formula in the VAT column that will look at each of the budget columns and check to see if there is an entry in a cell and if there is calculate 20% of that figure. If there is no entry in the budget column then obviously the VAT column should also be blank. Have looked at various functions but i am not getting anywhere. Help please!

**AndyLitch** · 06-13-2013, 12:16 PM

Assuming your net prices are logged in columns B:J then your VAT calculation would be =MAX(B1:J1)*0.2

Note that also assumes you will only have one price listed per row

BUT... If you want to total the gross + vat for all prices per row it would take the form =B1*VatRate + C1*VatRate + D1*VatRate
Where VatRate is a named range containing the VAT rate

**aydeegee** · 06-13-2013, 04:22 PM

To cover either of the above scenarios you could use the elegantly simpler (in K1)

Please Login or Register  to view this content.

and then in (say) L1

Please Login or Register  to view this content.

to get the overall total

Regards
Alastair

**footsoldier** · 06-14-2013, 04:48 AM

Many thanks for the help guys. I have used the Max function as this seems to fit in best with my plans for this sheet. First time I have used this function. All seems to be ok now.