Adding another condition to IF(AND) formula

[wynandbecker]

The sheet is in my master quote spreadsheet, the line in question calculates the price I'm renting out a lowbed truck for.
The truck has a minimum dayrate (column SHIFTS), and then also columns for the amount of kilometers the client will need to use it for.
The formula in the TOTAL cell compares (SHIFT x dayrate) total, to the (Kilometers x kilometer rate) total, and fills in whichever one is higher.
My problem is that if the user does not enter a value into SHIFTS cell, the TOTAL cell gets the Kilometer calculation, even if that value is lower than the minimum rate would have been.

Is there a way to force users to have something in SHIFTS cell? Or even better if the formula in TOTAL could have another condition to make sure it can never be less than the minimum rate?

The line in question is 24

COSTMASTERv9.0_MAY2024_COPY1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
9	PASTEL CODE	DESCRIPTION	NR OF VEHICLES	TOTAL SHIFTS	TOTAL NO-FREIGHT KM	TOTAL FREIGHT KM		RATE						LINE
10							DAY		KM		FREIGHT KM			TOTAL
11							COST	SELLING	COST	SELLING	COST	SELLING	COST	SELLING
12		LDV DC KATHU / SISHEN	1	1				R1,242.09		R4.84			R0.00	R1,242.09
13		LDV DC BEESHOEK						R1,605.09		R4.84			R0.00	R0.00
14		LDV SC BEESHOEK						R1,025.23		R4.84			R0.00	R0.00
15		LDV DC KOLOMELA						R1,863.14		R4.84			R0.00	R0.00
16		LDV DC SOUTH32						#N/A		R9.28			R0.00	#N/A
17		LDV LONG DISTANCE	1	1				R1,242.09		R4.84			R0.00	R1,242.09
18	REC021	3.2T TATA WITH HIGH-UP						R2,341.96		R9.28			R0.00	R0.00
19		LIVING OUT ALLOWANCE				SET MARKUP	R297.00	R424.71					R0.00	R0.00
20		ACCOMODATION				1.3	R935.00	R1,337.05					R0.00	R0.00
21		COURIER COST							PACKAGE SIZE		N/A			R0.00
22	TOTAL MISC												R0.00	#N/A
23		TRUCKS (DRIVER INCLUDED)	PERMIT	TOTAL SHIFTS	TOTAL NO-FREIGHT KM	TOTAL FREIGHT KM	TOTAL KM
24	REC001	LOWBED 44T DAY RATE INCLUDES 250KM			100	100	200	R8,542.88	R23.76	R31.68	R26.40	R33.00	R5,016.00	R6,468.00
25	REC033	LOWBED 44T DAY RATE INCLUDES 250KM			100	100	200	R8,542.88	R23.76	R31.68	R26.40	R33.00	R5,016.00	R6,468.00
26	REC043	RENTAL OF FAW TRUCK 15TON 6.2T HI-AB			100	100	200	R5,250.00	R20.99	R25.17	R22.90	R31.46	R4,389.00	R5,663.00
27	REC063	RENTAL OF ISUZU TRUCK 14.2T, DROP SIDE			100	100	200	R4,545.00	R20.99	R25.17	R22.90	R31.46	R4,389.00	R5,663.00
28	REC109	RENTAL OF ISUZU TRUCK 14.2T, DROP SIDE			100	100	200	R4,545.00	R20.99	R25.17	R22.90	R31.46	R4,389.00	R5,663.00
29		FREIGHT INSURANCE (COST)												R0.00
30	TOTAL LOWBED												R23,199.00	R29,925.00
TRANSPORT

Cell Formulas
Range	Formula
H16,H18	H16	=INDEX(RENTAL_PRICELIST!$C$51:$AU$62,MATCH(A16,RENTAL_PRICELIST!$A$51:$A$62,0),MATCH(LABOUR!$A$1,RENTAL_PRICELIST!$C$1:$AU$1,0))
M12:M18	M12	=SUM(C12*D12*G12)+C12*D12*E12*I12
M19:M20	M19	=SUM(C19*D19*G19)
N12:N18	N12	=SUM(C12*D12*H12)+(C12*D12*E12*J12)
N19:N20	N19	=SUM(C19*D19*H19)
N21	N21	=INDEX('CLIENT DATA'!D2:D9,MATCH(K21,'CLIENT DATA'!C2:C9,0))
M22	M22	=SUM(M12:M20)
N22	N22	=SUM(N12:N21)
G24:G28	G24	=SUM(E24:F24)
M24:M28	M24	=IF(AND((E24*I24+F24*K24)<D24*G24,(E24*I24+F24*K24)>0),(D24*G24)+C24,(E24*I24+F24*K24+C24))
N24:N28	N24	=IF(AND((E24*J24+F24*L24)<D24*H24,(E24*J24+F24*L24)>0),(D24*H24)+(C24*1.1),(E24*J24+F24*L24)+(C24*1.1))
N29	N29	=M29*1.1
M30:N30	M30	=SUM(M24:M29)

Cells with Data Validation
Cell	Allow	Criteria
D24	Whole number	between 1 and 100
F20:F21	List	='CLIENT DATA'!$C$65:$C$74
K21:L21	List	='CLIENT DATA'!$C$2:$C$9

 

[etaf]

(E24*J24+F24*L24)+(C24*1.1)
is that the formula which calculates the cost ?
if so , just change that to a MAX() and add the minimum cost
=MAX((E24*J24+F24*L24)+(C24*1.1),minimum value)

i wasnt sure where you minimum rate was in the spreadsheet

so if a cell , you can add that - or a fixed amount

 

[wynandbecker]

(E24*J24+F24*L24)+(C24*1.1) is TOTAL KM + TOTAL FREIGHT KM, and the value in TOTAL must be that, IF it is higher than the value of SHIFTS x DAYRATE

The complete formula is
=IF(AND((E24*J24+F24*L24)<D24*H24,(E24*J24+F24*L24)>0),(D24*H24)+(C24*1.1),(E24*J24+F24*L24)+(C24*1.1)) , can I still add MAX in there?

 

[etaf]

yes,
BUT in row 24 , I dont see any shift entered , so cannot use shift * dayrate

thats the test
=AND((E24*J24+F24*L24)<D24*H24,(E24*J24+F24*L24)>0)
and if true , then
(D24*H24)+(C24*1.1)
if false
(E24*J24+F24*L24)+(C24*1.1))
you can add the MAX to both of those , so it can never be below whatever you set as the minimum

so

=IF( AND((E24*J24+F24*L24)<D24*H24,(E24*J24+F24*L24)>0) , MAX( (D24*H24)+(C24*1.1) , minimum cost ) , MAX ( (E24*J24+F24*L24)+(C24*1.1) , minimum cost ) )

 

[wynandbecker]

Thanks man, that works it never goes below minimum. Gives me a new problem though, if there are no lowbeds on the quote, and that section is just left blank - i still end up with the minimum amount in the TOTAL cell now.

 

[wynandbecker]

Is there a way to just give an error in TOTAL if SHIFTS cell has no value?

 

[etaf]

just add at the beginning
=IF( cell with shifts in = 0 , "Error Enter Shifts", IF ( your formula .... )
the 0 will pickup cells that are blank or have a zero entry

or if never going to be entered as zero in error then
=IF( cell with shifts in = "" , "Error Enter Shifts", IF ( your formula .... )