Help with multiple IF statement

[Kayslover]

Hi all,

I am using Excel 2013.

I have the following formula in cell N3

=IF(AND(M3="Weekday",L3<=5),200,L3*B3*24)

How can I expand it so that if M3 contains “Weekend” and L3<=5 I would like the cell to be populated with 300 otherwise populate it with L3*B3*24

I have tried

=IF(AND(M3="Weekday",L3<=5),200,L3*B3*24),IF(AND(M3="Weekend",L3<=5),300,L3*B3*24)

and I get #VALUE! displayed in N3

Your assistance will be appreciated.

 

[Fluff]

How about

=IF(AND(M3="Weekday",L3<=5),200,IF(AND(M3="Weekend",L3<=5),300,L3*B3*24))

 

[Kayslover]

Fluff,

Thank you for the prompt response and apologies for my delay in responding (have been tied up with other things)

Having used your revised formula I have run into a problem as I did not explain myself correctly.

Basically you have said use the following in CellNn:-

=IF(AND(I3="Weekday",M3<=5),200,IF(AND(I3="Weekend",M3<=5),300,M3*B3*24))

What I would like is the following in Cell Nn

=IF(AND(I2="Weekday",M2<=5),200,M2*B2*24),(IF(AND(I2="Weekend",M2<=5),300,M2*B2*24)).

However, when I use my formula it returns #VALUE! In cell. Is there a way to modify my formula to achieve what I want?

I attach XL2BB which should hopefully be more helpful by looking at the comments in Cell Rn

Hall Hire Calculations.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Customer Name	Hourly Rate	Table Cloth Cost	Deposit Amount	Kitchen Required	Kitchen Hire	Soft Drinks Corkage	Day of the week	Weekend/Weekday	Date Of Event	Time In	Time Out	Total no of hours	Hall Hire Cost	No of Table Cloths	Table Cloth Total	Total Amount	Comments
2	Customer 1	£50.00	£3.00	£100.00	N	£0.00	£0.00	Mon	Weekday	10/01/2022	16:30	20:30	4:00	#VALUE!	0	£0.00	#VALUE!	As I2 is Weekday and M2 is less than =5, N2 is correct as £200
3	Customer 2	£50.00	£3.00	£100.00	N	£0.00	£0.00	Tue	Weekday	11/01/2022	18:30	23:30	5:00	£200.00	0	£0.00	£200.00	As I3 is Weekday and M3 less than =5, then N3 is correct as £200
4	Customer 3	£50.00	£3.00	£100.00	N	£0.00	£0.00	Wed	Weekday	12/01/2022	18:00	23:30	5:30	£200.00	0	£0.00	£200.00	As I4 is Weekday and M4 greater than =5, then N4 should be M4*B4*24=(£275)
5	Customer 4	£50.00	£3.00	£100.00	N	£0.00	£0.00	Thu	Weekday	13/01/2022	15:00	00:00	9:00	£200.00	0	£0.00	£200.00	As I5 is Weekday and M5 greater than =5, then N5 should be M5*B5*24=(£450.00)
6
7	Customer 5	£50.00	£3.00	£100.00	N	£0.00	£0.00	Fri	Weekend	14/01/2022	20:00	23:00	3:00	£300.00	0	£0.00	£300.00	As I7 is Weekend and M7 is less than =5, N7 is correct as £300
8	Customer 6	£50.00	£3.00	£100.00	N	£0.00	£0.00	Sat	Weekend	15/01/2022	18:30	23:30	5:00	£300.00	0	£0.00	£300.00	As I8 is Weekend and M8 is less than =5, N8 is correct as £300
9	Customer 7	£50.00	£3.00	£100.00	N	£0.00	£0.00	Sun	Weekend	16/01/2022	18:00	23:30	5:30	£300.00	0	£0.00	£300.00	As I9 is Weekend and M9 greater than =5, then N9 should be M9*B9*24=(£275)
10	Customer 8	£50.00	£3.00	£100.00	N	£0.00	£0.00	Sun	Weekend	16/01/2022	15:00	00:00	9:00	£300.00	0	£0.00	£300.00	As I10 is Weekend and M10 greater than =5, then N10 should be M10*B10*24=(£450.00)
Master

Cell Formulas
Range	Formula
B7:B10,B2:B5	B2	=Lookup!$B$2
C7:C10,C2:C5	C2	=Lookup!$B$4
H7:H10,H2:H5	H2	=TEXT(J2,"ddd")
I7:I10,I2:I5	I2	=IF(OR(H2="Fri",H2="Sat",H2="Sun"),"Weekend","Weekday")
M8,M2:M4	M2	=L2-K2
N2	N2	=IF(AND(I2="Weekday",M2<=5),200,M2*B2*24),(IF(AND(I2="Weekend",M2<=5),300,M2*B2*24))
N7:N10,N3:N5	N3	=IF(AND(I3="Weekday",M3<=5),200,IF(AND(I3="Weekend",M3<=5),300,M3*B3*24))
M5,M9:M10,M7	M5	=(L5-K5)
P7:P10,P2:P5	P2	=O2*C2
Q7:Q10,Q2:Q5	Q2	=N2+P2+F2+G2
F7:F10,F2:F5	F2	=IF(E2="Y",Lookup!$B$6,0)

 

[Fluff]

What is wrong with the formula I suggested?

 

[Kayslover]

Fluff,

I will come back to you to tomorrow? I have just got a call from my Mrs to go and pick her up as it raining.

 

[Fluff]

Ok

 

[Kayslover]

Fluff.

What is working is:-

If In = "Weekend" and Mn <= 5, then the formula Nn is being populated with 200 as anticipated.

If In = "Weekday" and Mn <= 5, then the formula Nn is being populated with 300 as anticipated.

What isn’t working is:-

If In = "Weekend" and Mn > 5, then the formula Mn*Bn*24 is not being applied.

If In = "Weekday" and Mn > 5, then the formula Mn*Bn*24 is not being applied.

I have never been able to do nested If statements in Excel, in my old programming days of programming mainframes, I would have used something like this to achieve what I am requesting:-

If In = “Weekday” Then
    If Mn <= 5 Then
        Nn= 200
    Else
        Nn= Mn*Bn3*24
    Endif
Else
    If Mn <= 5 Then
        Nn = 300
    Else
        Nn= Mn*Bn3*24
    Endif
Endif

I hope you don't take any offence with the coding I have supplied to achieve what I want the end result to be.

Thanks for your assistance so far.

 

[Fluff]

The problem is that col M are time values & will therefore always be less than 5.
Try

=IF(AND(I2="Weekday",M2<=TIME(5,0,0)),200,IF(AND(I2="Weekend",M2<=TIME(5,0,0)),300,M2*B2*24))

 

[Kayslover]

Fluff,

Your revised formula worked a treat.

I really appreciate your kind assistance.

 

[Fluff]

You're welcome & thanks for the feedback.