IF FORMULA GONE WRONG

[EXCELTRBLD]

Hello, can someone assist with the below formula? I'm using this formula, but it's not recognizing the "unit cnt" if it's 0.5 it doesn't give me half of the "payable" amount or it it's a "0" it doesn't change the "payable to $0.00. What can I add or remove from the formula below so it recognizes the "unit cnt"?

=CEILING(IF(G8=0,0,IF($G$34<10,$L$37,IF($G$34<14,$L$38,IF($G$34<17,$L$39,IF($G$34>16.5,L$40))))),0.01)

 

[Asbestos_Jen]

Your screenshot doesn't tell us where the data is located (row and column references) or which cell is supposed to contain the formula. Could you share the data using XL2BB or as a table with column and row identifiers? It would also help if you provide some cells with expected results shown.

 

[EXCELTRBLD]

I was able to paste the table below. Hope you can assist. Thank you!!!

DATE	STOCK #	DEAL #	NAME	UNIT CNT	SPLIT/PERSON	GROSS	GROSS	PAYABLE
1/1/2024​	654321	123456​	CUSTOMER NAME	0.5​	($1,732.28)​	$250.00​	$250.00
1/1/2024​	654321	123456​	CUSTOMER NAME	1.0​	($1,085.54)​	$250.00​	$250.00
1/1/2024​	654321	123456​	CUSTOMER NAME	1.0​	($885.96)​	$250.00​	$250.00
1/1/2024​	654321	123456​	CUSTOMER NAME	1.0​	($2,362.84)​	$1,810.00​	$250.00
1/1/2024​	654321	123456​	CUSTOMER NAME	0.5​	HALF DEAL	($353.81)​	$125.00​	$250.00
1/1/2024​	654321	123456​	CUSTOMER NAME	1.0​	($3,078.99)​	$300.00​	$250.00
1/1/2024​	654321	123456​	CUSTOMER NAME	1.0​	($322.17)​	$102.00​	$250.00
1/1/2024​	654321	123456​	CUSTOMER NAME	1.0​	$514.09​	$0.00​	$250.00
1/1/2024​	654321	123456​	CUSTOMER NAME	1.0​	($322.17)​	$102.00​	$250.00
1/1/2024​	654321	123456​	CUSTOMER NAME	1.0​	$514.09​	$0.00​	$250.00
TOTAL NEW	9.0​	($9,115.58)	$3,189.00	$2,500.00
USED

 

[Asbestos_Jen]

Where on the sheet are the cells? For instance, Is the cell labelled "DATE" in cell A1 or somewhere else? And where is the formula from your first post located?

 

[EXCELTRBLD]

This is a bigger picture, the formula =IF($G$34<=10,$N$13,IF($G$34<=14,$N$14,IF($G$34<=17,$N$15,IF($G$34>=16.5,N$16)))) is in column "K"... I need it to show if column "G" shows a "0" or "0.5" unit count to make column "K" change the payable amount to either $0 or if it's 0.5 unit count to show half the payable amount. See image below it shows formula, and selected cells. I really appreciate any input. Thank you!!!

 

[Asbestos_Jen]

You can just multiply the result of your full IF statement by the unit count cell.

=IF($G$39<=10,$N$13,IF($G$34<=14,$N$14,IF($G$34<=17,$N$15,IF($G$34>=16.5,$N$16))))*$G8

2024-02-22.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N
6	DATE	STOCK #	DEAL #	NAME	UNIT CNT	SPLIT/PERSON	GROSS	GROSS	PAYABLE
7									$0.00
8	01-01-24	654321	123456	CUSTOMER NAME	0.5		$1,732.28	$250.00	$125.00
9	01-01-24	654321	123456	CUSTOMER NAME	1		$1,085.54	$250.00	$250.00
10	01-01-24	654321	123456	CUSTOMER NAME	1		$885.96	$250.00	$250.00
11	01-01-24	654321	123456	CUSTOMER NAME	1		$2,362.84	$1,810.00	$250.00
12	01-01-24	654321	123456	CUSTOMER NAME	0.5	Half Deal	$353.81	$125.00	$125.00		COMMISSION RATES/CAR**
13	01-01-24	654321	123456	CUSTOMER NAME	1		$3,078.99	$300.00	$250.00		0-9.5 DEALS	$250.00
14	01-01-24	654321	123456	CUSTOMER NAME	1		$322.17	$102.00	$250.00		10-13.5 DEALS	$300.00
15	01-01-24	654321	123456	CUSTOMER NAME	1		$514.09	$0.00	$250.00		14-16.5 DEALS	$350.00
16	01-01-24	654321	123456	CUSTOMER NAME	1		$322.17	$102.00	$250.00		17+ DEALS	$400.00
17	01-01-24	654321	123456	CUSTOMER NAME	1		$514.09	$0.00	$250.00
18									$0.00
19									$0.00
20									$0.00
21	TOTAL NEW				9.0		$8,471.24	$3,189.00	$2,250.00
22	USED								$0.00
23									$0.00
24	01-01-24	654321	123456	CUSTOMER NAME	1.0		$2,943.52	$1,835.00	$250.00
25									$0.00
31									$0.00
32	TOTAL USED				1.0		$2,943.52	$1,835.00	$250.00
33
34	COMBINED NEW & USED = TOTAL COMMISSIONS				10.0		$11,414.76	$5,024.00	$2,500.00
35
Sheet1

Cell Formulas
Range	Formula
G21,J21:K21	G21	=SUM(G8:G20)
I21	I21	=SUM(I7:I20)
K31,K22:K25,K7:K20	K7	=IF($G$39<=10,$N$13,IF($G$34<=14,$N$14,IF($G$34<=17,$N$15,IF($G$34>=16.5,$N$16))))*$G7
G32,I32:K32	G32	=SUM(G22:G31)
G34,I34:K34	G34	=SUM(G21,G32)

 

[Asbestos_Jen]

Also, consider changing your commission table, and then modify your commission formula to reference it as below. The way you currently have your formula, it isn't using the rates in the table.

2024-02-22.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P
6	DATE	STOCK #	DEAL #	NAME	UNIT CNT	SPLIT/PERSON	GROSS	GROSS	PAYABLE
7									$0.00
8	01-01-24	654321	123456	CUSTOMER NAME	0.5		$1,732.28	$250.00	$150.00
9	01-01-24	654321	123456	CUSTOMER NAME	1		$1,085.54	$250.00	$300.00
10	01-01-24	654321	123456	CUSTOMER NAME	1		$885.96	$250.00	$300.00
11	01-01-24	654321	123456	CUSTOMER NAME	1		$2,362.84	$1,810.00	$300.00
12	01-01-24	654321	123456	CUSTOMER NAME	0.5	Half Deal	$353.81	$125.00	$150.00		COMMISSION RATES/CAR**		Lower	Upper
13	01-01-24	654321	123456	CUSTOMER NAME	1		$3,078.99	$300.00	$300.00		0-9.5 DEALS	$250.00	0	9.5
14	01-01-24	654321	123456	CUSTOMER NAME	1		$322.17	$102.00	$300.00		10-13.5 DEALS	$300.00	10	13.5
15	01-01-24	654321	123456	CUSTOMER NAME	1		$514.09	$0.00	$300.00		14-16.5 DEALS	$350.00	14	16.5
16	01-01-24	654321	123456	CUSTOMER NAME	1		$322.17	$102.00	$300.00		17+ DEALS	$400.00	17
17	01-01-24	654321	123456	CUSTOMER NAME	1		$514.09	$0.00	$300.00
18									$0.00
19									$0.00
20									$0.00
21	TOTAL NEW				9.0		$8,471.24	$3,189.00	$2,700.00
22	USED								$0.00
23									$0.00
24	01-01-24	654321	123456	CUSTOMER NAME	1.0		$2,943.52	$1,835.00	$300.00
25									$0.00
31									$0.00
32	TOTAL USED				1.0		$2,943.52	$1,835.00	$300.00
33
34	COMBINED NEW & USED = TOTAL COMMISSIONS				10.0		$11,414.76	$5,024.00	$3,000.00
Sheet1

Cell Formulas
Range	Formula
G21,J21:K21	G21	=SUM(G8:G20)
I21	I21	=SUM(I7:I20)
K31,K22:K25,K7:K20	K7	=IF($G$34<=$P$13,$N$13,IF($G$34<=$P$14,$N$14,IF($G$34<=$P$15,$N$15,IF($G$34>=O16,$N$16))))*$G7
G32,I32:K32	G32	=SUM(G22:G31)
G34,I34:K34	G34	=SUM(G21,G32)

 

[EXCELTRBLD]

This worked perfectly!! Thank you so much, appreciate your assistance!!!

 

[Asbestos_Jen]

Glad to have helped. Thank you for the feedback.