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

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:



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)



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):








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.




3 Replies

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


File attached.


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


With Excel 2019 or later you can apply IFS.



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 @OliverScheurich 

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.