Excel - Asking a lot I know .....

Muppet81

Hi,

I know this is asking alot but I have spent hours trying to produce an Excel spreadsheet to calculate our expenses at work. It is now working fine but only where the calculation is based on the standard rate of 17.5% VAT.

Where expenses are not standard VAT or are incurred travelling abroad, I need to be able to override the 17.5% calculation. BUT there is the snag, I am not very computer literate and I have been tearing my hair out for hours and hours.

Is there any brilliant mind out there who would be willing to look at my spreadsheet and if possible provide me with the formula I need? As I said, I know this is a lot to ask but if anyone is feeling up the the challenge I would love to PM you with the spreadsheet and you could really make my weekend if I could get this sorted.

judderman62

Just a question

If you, by your own admission, are not very computer literate ... why have you ended up doing this

Am I oversimplifying here but wherever in your formula you are putting 17.5 would you not just put the prevailing rate of tax ?

Hope you get it sorted - sure someone here will have the answer.

Good luck

J

Muppet81 wrote: »

Hi,

I know this is asking alot but I have spent hours trying to produce an Excel spreadsheet to calculate our expenses at work. It is now working fine but only where the calculation is based on the standard rate of 17.5% VAT.

Where expenses are not standard VAT or are incurred travelling abroad, I need to be able to override the 17.5% calculation. BUT there is the snag, I am not very computer literate and I have been tearing my hair out for hours and hours.

Is there any brilliant mind out there who would be willing to look at my spreadsheet and if possible provide me with the formula I need? As I said, I know this is a lot to ask but if anyone is feeling up the the challenge I would love to PM you with the spreadsheet and you could really make my weekend if I could get this sorted.

Muppet81

That is a very god question. Only started this job (office admin) recently and my previous boss told them I was a whizz with computers. Compared to him anyone would be ....

They asked me to sort out the spreadsheet and i didn't like to say no.

As to typing in the %. The way I have set it up, you type in your gross amount for each expense and the sheet calculates the net and vat and puts them into the correct column (defined by if statements).

I have had to protect all these formula so the guys can't mess them up. This means that the % can't be overwritten or chaged. Probably some obvious way of doing it but it is evading me.

Wombat

I would say that for every expense record you would need an indicator for the VAT rate.

So, when you insert an expense record have a cell set up in that record which can be a number,say, from 1 to 4 for 4 different rates of VAT. Those rates can be held in a table or series of cells elsewhere on the sheet and fetched for the calculation of the expense including VAT. Or simply have one cell per record which holds the VAT rate for that record.

If you need further help on how to do that, just ask.

judderman62

If statements and your not computer literate :eek::eek: I think you are a tad modest there mate.

Aye go on PM it to me - though sounds to me like you're at least as PC literate as myself - but I#ll take a peek if you like - hope no rush as I'm shortly going to go away from mpy PC and have other stuff to do today/tomorrow - is by close of weekend LATEST soon enough ?

That's IF I can help - happy to take a peek though

Failing that there must be things like knowledgebases, forums, and also groups (Google/yahoo etc) that cater for help on MS software/office/excel

waiting to hear from you

J

Muppet81 wrote: »

That is a very god question. Only started this job (office admin) recently and my previous boss told them I was a whizz with computers. Compared to him anyone would be ....

They asked me to sort out the spreadsheet and i didn't like to say no.

As to typing in the %. The way I have set it up, you type in your gross amount for each expense and the sheet calculates the net and vat and puts them into the correct column (defined by if statements).

I have had to protect all these formula so the guys can't mess them up. This means that the % can't be overwritten or chaged. Probably some obvious way of doing it but it is evading me.

judderman62

oh yes and of course other folk on here Sorry I missed that one out folks :o

Wombat

One thing to bear in mind is that the VAT rate(s) may change during the lifetime of the spreadsheet.

In this case it may be necessary to have 1 cell in every record be the actual VAT rate to be applied to that record. The reason for this is that, if you have 20 records which need a rate of 17.5% to be applied, and then the rate changes and subsequent records need to be calculated at, say, 17%, if you held the domestic VAT rate in a table or other cell, when you updated that rate it would change all the old records to a VAT rate of 17%, when in fact they need to have the old rate of 17.5% applied.

Muppet81

Thanks Judderman .. I'm so computer literate i can't work out how to PM you ... good start isn't it? How do I do it ?

judderman62

Muppet81 wrote: »

Thanks Judderman .. I'm so computer literate i can't work out how to PM you ... good start isn't it? How do I do it ?

hang on a tick just need to go check if one can do attachments in PM back in a tick

J

Mobeer

I've done something like what Wombat suggests in:
http://www.rtwarren.com/vat/vat.xls
or Excel 97 compatible:
http://www.rtwarren.com/vat/vat97.xls

The spreadsheet has a Expenses tab with a simple table where the user would have to enter the expense value and the VAT rate (high, low, none). Then column C uses a VLOOKUP formula onto the other sheet to find the number value that corresponds to the VAT rate entered, and then calculates the VAT. Then column D subtracts vat from gross to give net.


--

I rember having a bit of nightmare in a recruitment agency, who wanted to know if i was an Excel expert. The problem is, what defines an expert? I eventually said no and then had to do an online test at which I did poor, because no right clicking was allowed. I though up some excuses but was then told that I had done better than anyone else the agency had seen. Makes me wonder what they thought an Excel expert was?

Muppet81

Mobeer looks useful. Judderman has also made the kind offer to look at my sheet if I can send an atttachement with a PM.
Sadly OH has just attacked his finger with a chisel and there is blood everywhere, dashing off to the chemist for a bandage. Will be back on line and looking at this in about 1 hour. Thanks all , please do not leave me at this point.