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

judderman62

OK muppet person

have PM'd you with my gmail address -s end me the sheet as an attachment.

Hope you get OH's finger sorted :eek:

Catch ya later

Judderperson

malamute

The way i would do it is to create another column and with a yes/no input (y or n) then where you calculate the vat the calculation would be like this =IF(G18="y",C18/100*17.5," ")
which would be yes vat is applicable g18=y , c18 is the amount that vat is to be applied to and finally the vat calc divide c18 by 100 and multiply by 17.5 which would show the vat .

I'm working this weekend suggest you have a go yourself as this is how you learn if still got problems send me a PM along with your personal email address and i'll get back to you with my details so that you can send the spreadsheet.

judderman62

malamute wrote: »

The way i would do it is to create another column and with a yes/no input (y or n) then where you calculate the vat the calculation would be like this =IF(G18="y",C18/100*17.5," ")
which would be yes vat is applicable g18=y , c18 is the amount that vat is to be applied to and finally the vat calc divide c18 by 100 and multiply by 17.5 which would show the vat .

I'm working this weekend suggest you have a go yourself as this is how you learn if still got problems send me a PM along with your personal email address and i'll get back to you with my details so that you can send the spreadsheet.

I get the impression, and correct me if I'm wrong in this muppet81, that she needs variable rates of vat - in which case a Y/N wouldn't seem to be suitable.

Useful knowledge you're imparted there though Mal - I wouldn't have known that one :beer::beer::T

tomstickland

I'd set a cell for the default tax rate, with 17.5 in it.

I'd add a column where you leave a 0 in for standard tax rate and type another number to override it.

The tax calculation would then be:

tax = amount_spent*(if(cell=0,default_cell,cell)/100

Something like that. There are a million ways you could do it.

ManAtHome

If it's potentially for worldwide rates, I'd just leave a second column open and get them to enter the Vat rate (should be shown on the receipts), then adjust your formula to use this column instead of the 17.5.

rmg1

Assuming your initial value is in Column A, Standard VAT is in Column B (i.e. 17.5%) and your final value is in Column D, you could use the following if you wanted to put a different VAT rate in Column C.
Then use this formula in Column -
=if(C1="",A1*B1,A1*C1)

That would give you the value of VAT to be added.

If you wanted a nett total of expense + VAT, then change the formulas to :-
=if(C1="",(A1*B1)+A1,(A1*C1)+A1)

judderman62

ManAtHome wrote: »

If it's potentially for worldwide rates, I'd just leave a second column open and get them to enter the Vat rate (should be shown on the receipts), then adjust your formula to use this column instead of the 17.5.

that's the kind of tack I was going to take

malamute

I have been down this road before when the initial specification of what is required is not made crystal clear.
If the OP wanted to have any variable from 0 to 99.99 as a calculation of vat or whatever then the approach would be totally different. If that is what is required as other posters have stated then the vat element would be better alone within a column and dependant on how many variations would potentially be used (drop down if say no more than 10) or direct entry if more.

Wombat

malamute wrote: »

I have been down this road before when the initial specification of what is required is not made crystal clear.
If the OP wanted to have any variable from 0 to 99.99 as a calculation of vat or whatever then the approach would be totally different. If that is what is required as other posters have stated then the vat element would be better alone within a column and dependant on how many variations would potentially be used (drop down if say no more than 10) or direct entry if more.

Yes, I agree, malamute. There HAS to be a cell within each record holding the VAT amount to be applied to that record for the reasons I stated earlier in the event that a VAT rate changes during the lifetime of the spreadsheet.So even though there may be many records which need the domestic, UK VAT rate to be applied to them, this could be different for different records depending on whether they were an expense claimed pre-VAT rise or post-VAT rise. A table of rates would in this case not be possible, likewise a drop-down box or look-up table.

Muppet81

Sorry but I had to go off line to try to track down an open chemist to stop OH bleeding to death. It was a nasty gash.

My conclusion is that it is much easier to get Excel advice on a Saturday afternoon than it is to find an open chemist.

Thank you to all of you who have been kind enough to respond. There is so much information here it will take me a while to work through it....but I will.
thanks for sharing your knowledge. It is a great way for me to learn.
Judderman has kindly offered to have a look at my spreadsheet when he gets a spare moment and I greatly appreciate that.

Will let you know how i get on and thanks again all of you.