Help with IF, AND, OR formula

New Contributor

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

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

Riny_van_Eekelen_0-1667131644468.png

File attached.

@TimA-UK69 

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.

IFS.JPG

=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.

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

My spreadsheet is operating perfectly.

For ease I have used the embedded IF formula @Quadruple_Pawn 

I was unsure how to implement the other solution provided @Riny_van_Eekelen - As I said I am a bit of an Excel novice!

Again thank you.