Oct 30 2022 04:35 AM
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
Oct 30 2022 05:07 AM
@TimA-UK69 If I may, I would suggest a slight modification to your schedule and refrain from nested IF/AND/OR formulas.
File attached.
Oct 30 2022 12:05 PM
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.
Oct 31 2022 03:30 AM
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.