Multi =IF formula with ROUNDDOWN and separate QTY's

[PuntingJawa]

I would like to try a couple of functions along the lines of the following but no matter what combination I can't seem to get it to work due to lack of knowledge on patterns and restrictions of =IF formula combinations. So I am open to suggestions and changes.

1. If C4 is positive subtract that amount from 1272. The remaining gets divided by G4 which should leave the quantity or less than H4 has.
If the final is even then half would show in I4 and half in J4.
If odd then the even half to I4 and the odd to J4.
If the item has a decimal, I would like to round down to the nearest whole.

2. If C4 is negative it should show zero in I4/J4.

Are my criteria viable or are there other formulas that would be better suited to what I am trying t accomplish?

Book2
	B	C	D	E	F	G	H	I	J	K	L	M	N
2	REQ Total					Req Per	QTY
3
4	252	1020	X	X		42	6	3	3	X	X	X	X
5	0	1020	X	X		44	0	0	0	X	X	X	X
6	70	950	1272	1272		70	1	0.5	0.5	0	0
7	1560	-610	662	1272		65	24	7.307692	7.307692	-4.83077
8	96	-706	-44	1228		96	1	0	0
Sheet1

Cell Formulas
Range	Formula
I4:I5	I4	=IF(C4<0,0,B4/G4/2)
J4:J5	J4	=IF(C4<0,0,B4/G4/2)
C4	C4	=1272-B4
C5:C8	C5	=C4-B5
D6:E6	D6	=IF(C6>0,0,C6)+1272
D7:E8	D7	=IF(C7>0,0,C7)+D6
I6	I6	=IF(C5<0,0,B6/G6/2)
J6	J6	=IF(C5<0,0,B6/G6/2)
K6	K6	=IF(D6>0,0,D6/G7/2)
L6	L6	=IF(D6>0,0,D6/G7/2)
I7	I7	=IF(C6<0,0,C6/G7/2)*ROUNDDOWN(1,1)
J7:J8	J7	=IF(C6<0,0,C6/G7/2)
K7	K7	=IF(D6<0,0,D6/G7/2)-(I7+J7)
I8	I8	=IF(C7<0,0,C7/G7/2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E4:E8	Cell Value	<0	text	NO
E4:E8	Cell Value	between 0 and 9999	text	NO
D4:D8	Cell Value	<0	text	NO
C4:C8	Cell Value	<0	text	NO
D4:D8	Cell Value	between 0 and 9999	text	NO
C4:C8	Cell Value	between 0 and 9999	text	NO

 

[PuntingJawa]

I've gotten a lot closer on my own. I'm now presented with 2 issues.
The first, as the title says, is utilizing the "ROUNDDOWN" function to round down to the nearest whole. I cannot fathom where to place it to make this work, But I am closer.
The second, is fixing my string formula to not allow negatives in the I4-I8 through K4-K8 ranges. If they are negative then it should be 0.

Truck load practice formula.xlsx
	B	C	D	E	F	G	H	I	J	K
2	REQ Total					Req Per	QTY
3
4	252	1020	X	X		42	6	6	X	X
5	0	1020	X	X		44	0	0	X	X
6	70	950	1272	1272		70	1	1	0	1
7	1560	-610	662	1272		65	24	5.230769	13.53846	5.230769
8	96	-706	-44	1228		96	1	-13.7083	20.14583	-5.4375
9
10
11
12	1272
Sheet1

Cell Formulas
Range	Formula
C4	C4	=1272-B4
C5:C8	C5	=C4-B5
I4	I4	=IF(C4>0,H4,IF(C4>0,(B12+C4)/G4))
I5:I8	I5	=IF(C5>0,H5,IF(C5<0,(C4+C5)/G5))
D6:E6	D6	=IF(C6>0,0,C6)+1272
D7:E8	D7	=IF(C7>0,0,C7)+D6
J6	J6	=IF(C6>0,H6,IF(C5>0,(B12+C6)/G6))-I6
K6	K6	=IF(E6>0,H6,IF(E6>0,B12+E6/G6))
J7:J8	J7	=IF(D7>0,H7-I7,IF(D7<0,(D6+D7)/G7))-I7
K7	K7	=IF(E7>0,H7-J7-I7,IF(E7>0,B12+E7/G7))
K8	K8	=IF(E8>0,H8-J8-I8,IF(E8>0,B12+E8/G8))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E4:E8	Cell Value	<0	text	NO
E4:E8	Cell Value	between 0 and 9999	text	NO
D4:D8	Cell Value	<0	text	NO
C4:C8	Cell Value	<0	text	NO
D4:D8	Cell Value	between 0 and 9999	text	NO
C4:C8	Cell Value	between 0 and 9999	text	NO

 

[PuntingJawa]

I've worked a little more on this. Instead of simply saving space, I decided to move the string data to one set of cells and make "Actual remaining" cells as the Rounddown and quantity check. Everything looks like it worked.
Can someone please check over my work and let me know if I did this correctly and if there are any improvements that can be made? I feel like the string data section is where there may be some errors, but as for the data I am working with for an example, the formulas seem OK.

Book1
	A	B	C	D	E	F	G
1
2					Available
3		Req Per	QTY	REQ Total	1272
4		42	6	252	1020	X	X
5		44	0	0	1020	X	X
6		70	1	70	950	1272	1272
7		65	24	1560	-610	662	1272
8		96	1	96	-706	-44	1228
9
10		Actual remaining				QTY Check
11		Row 1	Row 2	Row 3		G/B
12		6	X	X		Good
13		0	X	X		Good
14		1	0	0		Good
15		14	10	0		Good
16		0	0	1		Good
17
18
19		String Data
20		Row 1	Row 2	Row 3
21		6	X	X
22		0	X	X
23		1	0	0
24		14.6153846	10.1846154	0
25		0	0	1
26
Sheet1

Cell Formulas
Range	Formula
E4	E4	=1272-D4
E5:E8	E5	=E4-D5
F6	F6	=IF(E6>0,0,E6)+E3
G6	G6	=IF(F6>0,0,F6)+E3
F7:G8	F7	=IF(E7>0,0,E7)+F6
B12:B13,B14:D16	B12	=ROUNDDOWN(B21,0)
F12:F14	F12	=IF(B12=C4,"Good","Bad")
F15:F16	F15	=IF(B15+C15+D15=C7,"Good","Bad")
B21:B24	B21	=IF(D4<E3,C4,IF(D4>E3,E3/B4))
C23	C23	=IF(B23=C6,0,IF(D6<E3,C6,IF(D6>E3,E3/B6)))
D23	D23	=IF(C23<C6,0,IF(D6<E3,C6,IF(D6>E3,E3/B6)))
C24	C24	=IF(E7<0,F7/B7,0)
D24	D24	=IF(G7<0,G7/B7,0)
B25	B25	=IF(D8<E7,C8,IF(D8>E7,0))
C25	C25	=IF(E8>F8,F8/B8,0)
D25	D25	=IF(F8<G7,C8,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F12:F16	Cell Value	contains "Bad"	text	NO
F12:F16	Cell Value	contains "Good"	text	NO