What to show blank when nothing there

[Robert Wyatt]

I trying to show a blank cell when there is no information I using a formula in F12:F30 but it still adds total all the way down to F30 even when there is nothing in cells A17:E17. Please help

Book1
	J
12
Sheet1

 

[Joe4]

Your attempt to post your image failed. Make sure that you select the whole range you want to show before trying to post it here.

 

[awoohaw]

Your mini workbook did not post correctly.
But, to give a guess at an answer to your question, wrap your formula in F12:F30 with and IF statement that check for blanks in the component calculation, IF(OR(cellA="", cellB = "", cellC ="", then "", THE-FORMULA).

 

[mikerickson]

What is your formula?
If its a VLOOKUP you could try changing it to =""&VLOOKUP(....)

 

[Robert Wyatt]

Your mini workbook did not post correctly.
But, to give a guess at an answer to your question, wrap your formula in F12:F30 with and IF statement that check for blanks in the component calculation, IF(OR(cellA="", cellB = "", cellC ="", then "", THE-FORMULA).

going to try and up load it again hope it works this time.

Work in Prograss.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Original Date of Hire	Years of Service	Annual Accrual Days	Maximum Accrual	Accral Per Hours	Accrual Per-Bi Weekly PP	Available PTO Hours											Years of Service	Annual Accrual Days	Maximum Accrual	Accrual Per Hours	Accrual Per Bi-Weekly PP
2	04 Feb 2008	15	25	200	0.0962	7.69	89.30											0	10	80	0.0385	3.08
3																		1	12	96	0.0462	3.69
4																		2	15	120	0.0577	4.62
5		Description				Count												5	20	160	0.0769	6.15
6		Total Hours Accrual				23.51												10	25	200	0.0962	7.69
7		Total Paid Time Off Hours Used				56.00
8
9
10
11	Pay Period Number	Pay Date	Total Hours	PTO Used	Hours Accrual	Balance
12	26	12/01/2022	Balance Farward			121.79
13	1	01/05/2023	60	-	5.77	127.56
14	2	01/19/2023	59.55	8.00	5.73	125.29
15	3	02/02/2023	65	48.00	6.25	83.54
16	4	02/16/2023	60	-	5.77	89.30
17					-	89.30
18					-	89.30
19					-	89.30
20					-	89.30
21					-	89.30
22					-	89.30
23					-	89.30
24					-	89.30
25					-	89.30
26					-	89.30
27					-	89.30
28					-	89.30
29					-	89.30
30					-	89.30
31
Sheet1

Cell Formulas
Range	Formula
B2	B2	=DATEDIF(A2,TODAY(),("Y"))
C2	C2	=LOOKUP(B2,R2:R6,S2:S6)
D2	D2	=LOOKUP(B2,R2:R6,T2:T6)
E2	E2	=LOOKUP(B2,R2:R6,S2:S6/260)
F2	F2	=LOOKUP(B2,R2:R6,V2:V6)
G2	G2	=VLOOKUP(F12+100,F12:F38,1)
F6	F6	=SUM(E13:E38)
F7	F7	=SUM(D13:D38)
E13:E30	E13	=LOOKUP($B$2,$R$2:$R$6,$S$2:$S$6/260*C13)
F13:F30	F13	=IF(AND(ISBLANK(A13:D13)), " - ",IF(F12+E13-D13<$D$2,F12+E13-D13,200))

 

[awoohaw]

thanks for gettign the min workbook. See my formulas in E & F 13. I don't know why the formatting is off now. but you can work on that I think.

Mr Excel Questions4.xlsm
	A	B	C	D	E	F	G											R	S	T	U	V	W
1	Original Date of Hire	Years of Service	Annual Accrual Days	Maximum Accrual	Accral Per Hours	Accrual Per-Bi Weekly PP	Available PTO Hours											Years of Service	Annual Accrual Days	Maximum Accrual	Accrual Per Hours	Accrual Per Bi-Weekly PP
2	39482	15	25	200	0.096153846	7.692307692	89.3044											0	10	80	0.03846	3.07692
3																		1	12	96	0.04615	3.69231
4																		2	15	120	0.05769	4.61538
5		Description				Count												5	20	160	0.07692	6.15385
6		Total Hours Accrual				23.51442308												10	25	200	0.09615	7.69231
7		Total Paid Time Off Hours Used				56
8
9
10
11	Pay Period Number	Pay Date	Total Hours	PTO Used	Hours Accrual	Balance
12	26	44896	Balance Farward			121.79
13	1	44931	60	0	5.769230769	127.5592308
14	2	44945	59.55	8	5.725961538	125.2851923
15	3	44959	65	48	6.25	83.53519231
16	4	44973	60	0	5.769230769	89.30442308
17
18
19
20
21
22
23
24
25
Sheet1

Cell Formulas
Range	Formula
B2	B2	=DATEDIF(A2,TODAY(),("Y"))
C2	C2	=LOOKUP(B2,R2:R6,S2:S6)
D2	D2	=LOOKUP(B2,R2:R6,T2:T6)
E2	E2	=LOOKUP(B2,R2:R6,S2:S6/260)
F2	F2	=LOOKUP(B2,R2:R6,V2:V6)
G2	G2	=VLOOKUP(F12+100,F12:F38,1)
F6	F6	=SUM(E13:E38)
F7	F7	=SUM(D13:D38)
E13:E25	E13	=IF(C13="","",LOOKUP($B$2,$R$2:$R$6,$S$2:$S$6/260*C13))
F13:F25	F13	=IF(C13="","",IF(AND(ISBLANK(A13:D13)), " - ",IF(F12+E13-D13<$D$2,F12+E13-D13,200)))

 

[awoohaw]

Your attempt to post your image failed. Make sure that you select the whole range you want to show before trying to post it here.

Robert, i don't think this post answered your question. Can you mark the correct post?

 

[Fluff]

@Robert Wyatt In future please do not mark a post as the Solution when it does not contain one. Thanks
I have removed the "solution" from post#2. If any other post does what you need then you can mark that post instead.

 

[Robert Wyatt]

is there anyway to get a black space when using and index formula I have hopefully furnished the spread sheets that has the information on them for you to look at.

2024 Blank Time Keeper.xlsm
	B	C	D	E	F	G	H	I	J
16	Day	Date	Description	Time In	Time Out	Worked	Regular	Overtime	PTO
18						-	-	-	-
19						-	-	-	-
20						-	-	-	-
21						-	-	-	-
22						-	-	-	-
23						-	-	-	-
24						-	-	-	-
25	TOTAL HOURS:					-	-	-	-
26
27	Day	Date	Description	Time In	Time Out	Worked	Regular	Overtime	PTO
29						-	-	-	-
30						-	-	-	-
31						-	-	-	-
32						-	-	-	-
33						-	-	-	-
34						-	-	-	-
35						-	-	-	-
36	TOTAL HOURS:					-	-	-	-
37
38	TOTAL HOURS		-
39
40	Description		Rate	Hours	This Period		Social Security Information
41	Regular		$ 11.75	-	-		Allowed Monthly Gross		$ 1,770.00
42	Stand In		$ 13.75	-	-		Estimated Monthly Gross		$ -
43	PTO		$ 11.75	-	-		Difference		$ -
44	Overtime		$ 17.63	-	-		Allowed Annually Gross		$21,240.00
45	Tip Appliance		$ 0.98	-	-		Estimated Annually Gross		$ -
46	Trainning		$ 11.75	-	-		Difference		$ -
47	TOTAL GROSS WAGES:				$ -		YTD Wages, Tips		$ -
Payroll Keeper

Cell Formulas
Range	Formula
B29:B35,B18:B24	B18	=IF(C18="","",C18)
C18	C18	=IF(C10="","",C10)
C19	C19	=IF(C10="","",C10+1)
C20	C20	=IF(C10="","",C10+2)
C21	C21	=IF(C10="","",C10+3)
C22	C22	=IF(C10="","",C10+4)
C23	C23	=IF(C10="","",C10+5)
C24	C24	=IF(C10="","",C10+6)
G29:G35,G18:G24	G18	=ROUND(IF((OR(E18="",F18="")),0,IF((F18<E18),((F18-E18)*24)+24,(F18-E18)*24)),2)
H29:H35,H18:H24	H18	=G18-I18
I18	I18	=ROUND(MAX(IF($R$14,MAX(0,SUM(H17:H$17)+G18-$Q$16),0),IF($R$14,IF(G18>$Q$16,G18-$Q$16,0),0)),2)
I19:I24	I19	=ROUND(MAX(IF($R$14,MAX(0,SUM(H$17:H18)+G19-$Q$16),0),IF($R$14,IF(G19>$Q$16,G19-$Q$16,0),0)),2)
G36:J36,G25:J25	G25	=SUM(G18:G24)
C29	C29	=IF(C10="","",C10+7)
C30	C30	=IF(C10="","",C10+8)
C31	C31	=IF(C10="","",C10+9)
C32	C32	=IF(C10="","",C10+10)
C33	C33	=IF(C10="","",C10+11)
C34	C34	=IF(C10="","",C10+12)
C35	C35	=IF(C10="","",C10+13)
I29:I35	I29	=ROUND(MAX(IF($R$14,MAX(0,SUM(H$28:H28)+G29-$Q$16),0),IF($R$14,IF(G29>$Q$16,G29-$Q$16,0),0)),2)
D38	D38	=SUMIFS(H18:H35,D18:D35,"Regular")+SUMIFS(H18:H35,D18:D35,"Stand In")+SUMIFS(H18:H35,D18:D35,"Trainning")+SUMIFS(J18:J35,D18:D35,"PTO")+SUMIFS(J18:J35,D18:D35,"FMLA")
J41	J41	=ROUND(SUM(J44/12),2)
J42	J42	=ROUND(SUM(F47*2),2)
J43,J46	J43	=IF(OR(J42=0,J41=0),0,ROUND(IF(J42>J41,0,J41-J42),+ROUND(IF(J42<J41,0,J41-J42),2)))
E41	E41	=SUMIFS(H18:H35,D18:D35,"Regular")
E42	E42	=SUMIFS(H18:H35,D18:D35,"Stand in")
E43	E43	=SUMIFS(J18:J35,D18:D35,"PTO")+SUMIFS(J18:J35,D18:D35,"Vacation")+SUMIFS(J18:J35,D18:D35,"FMLA")
E44	E44	=SUMIFS(I18:I35,D18:D35,"Overtime")
E45	E45	=SUMIFS(H18:H35,D18:D35,"Regular")
E46	E46	=SUMIFS(H18:H35,D18:D35,"Trainning")
F41:F45	F41	=ROUND(D41*E41,2)
F46	F46	=ROUND(SUM(D46*E46),2)
F47	F47	=SUM(F41:F46)
J45	J45	=ROUND(SUM(F47*26),2)
J47	J47	=ROUND(SUM(YTD!W4),2)

Cells with Data Validation
Cell	Allow	Criteria
D18:D24	List	-,Regular,Stand In,Off,PTO,Vactaion,FMLA,Trainning
D29:D35	List	-,Regular,Stand In,Off,PTO,Vactaion,FMLA,Trainning
J18:J24	List	0,1,2,3,4,5,6,7,8
J29:J35	List	0,1,2,3,4,5,6,7,8

 

[Robert Wyatt]

sorry the L2BB didn't load right well try and redo it.