Excel help

[Kier]

Afternoon!

After a bit of help, I'm trying to make an expenses form on excel, but a little bit stuck.

At the moment I have the following headers.

Date...Start...Finish...Hours worked...Miles...Meal allowance

I'm trying to find a way of making it when I put my start and finish time in, it works out the hours worked and adds the correct meal allowance. I've managed to get it to work out the hours worked, its just the rest I'm a bit stuck with.

For working between 4 to 5 hours we get x.xx.
5 to 8 we get x.xx
8 to 12 we get xx.xx
12 to 24 we get xx.xx

I think I'm going to need an if statement, but not used one of them for years since school! If it helps I can upload a screen print once I get home, should be able to upload it if need be.
Any help would be great. Sorry its not in the PC section, but on my phone and signal is crap and page wouldn't load! Ignore and spelling mistakes too, same reason!

Thanks.

 

[Stu DemonD]

You can use a nested IF function or LOOKUP.

So for the example above, let's say hours worked is in B4.

=IF(B4>12,xx.xx, IF(B4>8,xx.xx, IF(B4>5,x.xx, IF(B4>4,x.xx)))) or something like that. Work down from the highest number i.e. 12 in this case.

Feel free to send the spreadsheet over.

 

[TallTom]

Nested if loop

IF( condition1, value_if_true1, IF( condition2, value_if_true2, value_if_false2 ))

^^ If true, amount, 2nd if statement

You would want something like if hour worked = <4, meal value, "if not" do for larger value

just keep expanding the brackets till you get all of your criteria in.

Here is a very good example

Question: In Excel, I need a formula in cell C5 that does the following:

IF A1+B1 <= 4, return $20
IF A1+B1 > 4 but <= 9, return $35
IF A1+B1 > 9 but <= 14, return $50
IF A1+B1 > 15, return $75
​

Answer: In cell C5, you can write a nested IF statement that uses the AND function as follows:

=IF((A1+B1)<=4,20,IF(AND((A1+B1)>4,(A1+B1)<=9),35,IF(AND((A1+B1)>9,(A1+B1)<=14),50,75)))

It includes the And function for your between values
​

 

[Kier]

*wooosh*

That went straight over my head! Might as well be talking French lol .

Custard, I've uploaded it here, if your able to do that, I would love you forever and ever!

 

[jip0]

how much allowance do you get for each amount of hours worked?

ill do it for you now

edit: ignore that i just saw the bright red box

 

[jip0]

my brain has turned to **** and i havent used excel like this for a very long time, hence my failure

the basic bit is there but it returns true instead of the defined value

sorry

not sure if it saved for you but

https://docs.google.com/spreadsheet/ccc?key=0AmNEpYvgx4tPdE1jMDVHd1VlX05xWnZ6VWVXUGVYb3c

is how far i got

 

[TallTom]

If you are still having issues I will try and write you a formula on my lunch tomorrow so let me know

 

[Stu DemonD]

Custard, I've uploaded it here, if your able to do that, I would love you forever and ever!

Custard hasn't posted on this thread

Why are you only claiming 40p per mile? It has gone up to 45p now.

 

[Kier]

Custard hasn't posted on this thread

Why are you only claiming 40p per mile? It has gone up to 45p now.

Opps! Long day yesterday lol .

All we are allowed to claim for, don't know if its because I'm doing it voluntary. Don't really understand it though.

 

[Stu DemonD]

The rate was increased last year, from 40p to 45p [well authorised mileage rate, of course they can pay what they want...]

 

[ChrisUK]

Count yourself lucky I get 30p .

 

[Stu DemonD]

Count yourself lucky I get 30p .

Claim the balance via HMRC then [although you will only get the difference at your effective rate of tax!]

 

[ChrisUK]

Cool how do I claim?

 

[Stu DemonD]

Just spotted this.

I would suggest using form P87

http://www.hmrc.gov.uk/incometax/how-to-get.htm

and follow the links.

You can go back around 6 years. So put the total mileage on the form at the prevailing rate, enter in what you received for it - and get tax relief on the difference.

i.e. 5000 miles claimed @ 45p = £2,250; actually received @ 30p = £1,500. Difference of £750 at 20% basic rate tax = £150 Tax refund.

 

[ChrisUK]

Cool thanks. I'll see how far my records go .