Make a blank cell = 0

[Mcls2020]

Dear all,

I hope that you are all well. Happy New Year

I am using the below formula, which is working fine, but I need the blank cell to give a value:
=IF(K6="Set 1 Sounds Group A","1",IF(K6="Set 1 Sounds Group B","2",IF(K6="Set 1 Sounds Group C","3",IF(K6="Ditty Group","4",IF(K6="Red Group","5",IF(K6="Green Group","6",IF(K6="Purple Group","7",IF(K6="Pink Group","8",IF(K6="Orange Group","9",IF(K6="Completed","14",IF(K6="Yellow Group","10",IF(K6="Blue Group","11",IF(K6="Grey Group","12",IF(K6=0,"",IF(K6="Comprehension","13",IF(K6="","0",IF(K6="Literacy and Language","13")))))))))))))))))

If I add K6 as a blank cell it is still returning it to an empty cell. I checked the setting in Excel to see if it was showing view '0'. I tried to change the value to a different number and it still does not show. Is there any way I can fix this without putting any information in the K6 cell?

Thank you.

Kind regards,

Matt

 

[awoohaw]

Use the N() function

Mr Excel Questions.xlsx
	G	H	I
2
3	564	564
4		0
5	dafdsf	0
6	445fdf6546	0
7	1/20/2026	46042
8
Sheet2

Cell Formulas
Range	Formula
H3:H7	H3	=N(G3)

 

[Fluff]

It's is returning "" because of this part of the formula

IF(K6=0,"",

 

[awoohaw]

@Mcls2020. Sorry, about my erroneous suggestion earlier. I did not test against your formula, as it required creating the spreadsheet.
I could not figure out why excel does that. But, I did get a formula that maps the blank cell to a "0". I did by using an ISBLANK in the first part of the main formula. I also did using the SWITCH function as well as the nested IFs. I don't see any dependency upon order of the edit (other than performance) so the ISBLANK at the start shouldn't affect the result. In the sheet below I have ISBLANK at the top of both, the switch and the nested ifs in the first two columns of formulas. The 3rd formula column has an ISBLANK check in the original location.

WorkBook1.xlsx
	K	L	M	N	O	P
5				SWITCH	NESTED IF w ISBLANK at Top	ISBLANK in Orig Location
6	Set 1 Sounds Group A			1	1	1
7	Set 1 Sounds Group B			2	2	2
8	Set 1 Sounds Group C			3	3	3
9	Ditty Group			4	4	4
10	Red Group			5	5	5
11	Green Group			6	6	6
12	Purple Group			7	7	7
13	Pink Group			8	8	8
14	Orange Group			9	9	9
15	Completed			14	14	14
16	Yellow Group			10	10	10
17	Blue Group			11	11	11
18	Grey Group			12	12	12
19	0
20	0
21	Comprehension			13	13	13
22				0	0
23	Literacy and Language			13	13	13
Sheet2

Cell Formulas
Range	Formula
N6:N23	N6	=IF(ISBLANK(K6),"0",SWITCH( K6,"Set 1 Sounds Group A","1", "Set 1 Sounds Group B","2", "Set 1 Sounds Group C","3", "Ditty Group","4", "Red Group","5", "Green Group","6", "Purple Group","7", "Pink Group","8", "Orange Group","9", "Completed","14", "Yellow Group","10", "Blue Group","11", "Grey Group","12", "0","", 0,"", "Comprehension","13", "Literacy and Language","13"))
O6:O23	O6	=IF(ISBLANK(K6),"0",IF(K6="Set 1 Sounds Group A","1", IF(K6="Set 1 Sounds Group B","2", IF(K6="Set 1 Sounds Group C","3", IF(K6="Ditty Group","4", IF(K6="Red Group","5", IF(K6="Green Group","6", IF(K6="Purple Group","7", IF(K6="Pink Group","8", IF(K6="Orange Group","9", IF(K6="Completed","14", IF(K6="Yellow Group","10", IF(K6="Blue Group","11", IF(K6="Grey Group","12", IF(K6=0,"", IF(K6="0","", IF(K6="Comprehension","13", IF(K6="Literacy and Language","13","END" ))))))))))))))))))
P6:P23	P6	=IF(K6="Set 1 Sounds Group A","1", IF(K6="Set 1 Sounds Group B","2", IF(K6="Set 1 Sounds Group C","3", IF(K6="Ditty Group","4", IF(K6="Red Group","5", IF(K6="Green Group","6", IF(K6="Purple Group","7", IF(K6="Pink Group","8", IF(K6="Orange Group","9", IF(K6="Completed","14", IF(K6="Yellow Group","10", IF(K6="Blue Group","11", IF(K6="Grey Group","12", IF(K6=0,"", IF(K6="0","", IF(K6="Comprehension","13", IF(ISBLANK(K6),"0", IF(K6="Literacy and Language","13","END" ))))))))))))))))))

 

[Mcls2020]

@Mcls2020. Sorry, about my erroneous suggestion earlier. I did not test against your formula, as it required creating the spreadsheet.
I could not figure out why excel does that. But, I did get a formula that maps the blank cell to a "0". I did by using an ISBLANK in the first part of the main formula. I also did using the SWITCH function as well as the nested IFs. I don't see any dependency upon order of the edit (other than performance) so the ISBLANK at the start shouldn't affect the result. In the sheet below I have ISBLANK at the top of both, the switch and the nested ifs in the first two columns of formulas. The 3rd formula column has an ISBLANK check in the original location.

WorkBook1.xlsx
	K	L	M	N	O	P
5				SWITCH	NESTED IF w ISBLANK at Top	ISBLANK in Orig Location
6	Set 1 Sounds Group A			1	1	1
7	Set 1 Sounds Group B			2	2	2
8	Set 1 Sounds Group C			3	3	3
9	Ditty Group			4	4	4
10	Red Group			5	5	5
11	Green Group			6	6	6
12	Purple Group			7	7	7
13	Pink Group			8	8	8
14	Orange Group			9	9	9
15	Completed			14	14	14
16	Yellow Group			10	10	10
17	Blue Group			11	11	11
18	Grey Group			12	12	12
19	0
20	0
21	Comprehension			13	13	13
22				0	0
23	Literacy and Language			13	13	13
Sheet2

Cell Formulas
Range	Formula
N6:N23	N6	=IF(ISBLANK(K6),"0",SWITCH( K6,"Set 1 Sounds Group A","1", "Set 1 Sounds Group B","2", "Set 1 Sounds Group C","3", "Ditty Group","4", "Red Group","5", "Green Group","6", "Purple Group","7", "Pink Group","8", "Orange Group","9", "Completed","14", "Yellow Group","10", "Blue Group","11", "Grey Group","12", "0","", 0,"", "Comprehension","13", "Literacy and Language","13"))
O6:O23	O6	=IF(ISBLANK(K6),"0",IF(K6="Set 1 Sounds Group A","1", IF(K6="Set 1 Sounds Group B","2", IF(K6="Set 1 Sounds Group C","3", IF(K6="Ditty Group","4", IF(K6="Red Group","5", IF(K6="Green Group","6", IF(K6="Purple Group","7", IF(K6="Pink Group","8", IF(K6="Orange Group","9", IF(K6="Completed","14", IF(K6="Yellow Group","10", IF(K6="Blue Group","11", IF(K6="Grey Group","12", IF(K6=0,"", IF(K6="0","", IF(K6="Comprehension","13", IF(K6="Literacy and Language","13","END" ))))))))))))))))))
P6:P23	P6	=IF(K6="Set 1 Sounds Group A","1", IF(K6="Set 1 Sounds Group B","2", IF(K6="Set 1 Sounds Group C","3", IF(K6="Ditty Group","4", IF(K6="Red Group","5", IF(K6="Green Group","6", IF(K6="Purple Group","7", IF(K6="Pink Group","8", IF(K6="Orange Group","9", IF(K6="Completed","14", IF(K6="Yellow Group","10", IF(K6="Blue Group","11", IF(K6="Grey Group","12", IF(K6=0,"", IF(K6="0","", IF(K6="Comprehension","13", IF(ISBLANK(K6),"0", IF(K6="Literacy and Language","13","END" ))))))))))))))))))

Thank you so much. That worked very well.

 

[awoohaw]

You're welcome. Please remember to mark the solutions you find helpful as the answer.
Also, you will get better responses if you get the xl2bb add in to post min spreadsheets of questions: https://www.mrexcel.com/board/mrexcel-xl2bb.svg.