Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

# Help with IF, AND, OR formula

Copper Contributor

# Help with IF, AND, OR formula

I hope that someone might be able to assist me with a formula

(I'm sure this is probably fairly straightforward - I am a bit of an Excel novice and I just cannot get my head around it !!)

I have been trying to work out an IF / AND / OR formula, as follows, but cannot get the results cell to report as I would like.

I have the following parameters for hours worked, when calculating what a daily meal / subsistence entitlement could be when working away from the office / home.

For simplicity,

Daily hours worked is entered into cell A1

The allowance result is then to be reported into cell A2 and based on the following:

Allowances

0 to 5 hours = £0 (cell B1)

5 hours or more = £5 (cell B2)

10 hours or more = £10 (cell B3)

15 hours or more = £15 (cell B4)

24 hours = £25 (cell B5)

EXAMPLE

So, if 4 hours worked, the entitlement is £0

if 12 hours then it's £10

if away overnight then it's £25

So far what I have got (as a formula for cell A2):

=IF(AND(A1>0,A1<5),B1,

OR(AND(A1>5,A1<10),B2,

OR(AND(A1>10,A1<15),B3,

OR(AND(A1>15,A1<24),B4,

OR((A1>24),B5)))))

However the result is simply being returned as "£ -" when A1 is entered between 0 and 5 and "TRUE" for anything more than 5.

I am looking for it to report in £, so either £0 / £5 / £10 / £15 or £25, so that I can then automatically calculate monthly figures from that.

I hope that this makes sense.

Thanks in advance for any assistance anyone can give me.

TimA

3 Replies

# Re: Help with IF, AND, OR formula

@TimA-UK69 If I may, I would suggest a slight modification to your schedule and refrain from nested IF/AND/OR formulas.

File attached.

# Re: Help with IF, AND, OR formula

You have already received an easier solution with a lookup formula. However if you want to use IF or IFS:

``=IFS(AND(A1>0,A1<5),B1,AND(A1>=5,A1<10),B2,AND(A1>=10,A1<15),B3,AND(A1>=15,A1<24),B4,A1>=24,B5)``

With Excel 2019 or later you can apply IFS.

``=IF(AND(A1>0,A1<5),B1,IF(AND(A1>=5,A1<10),B2,IF(AND(A1>=10,A1<15),B3,IF(AND(A1>=15,A1<24),B4,IF(A1>=24,B5)))))``

With older versions you can use nested IF.

# Re: Help with IF, AND, OR formula

Thank you for all of your assistance - a great help.