How to make cell Result as Blank, if one of sum of cells have no value

[pankajgrover]

How to make cell Result as Blank, if one of sum of cells have no value. In below example i want D3 and D4 should be result as blank until a3 and b4 have some values. Thanks

Shares Trading.xlsx
	A	B	C	D
1	Item	A	B	Total
2	pen	2500	3500	1000
3	pencil		5000	5000
4	rubber	4000		-4000
Sheet1

Cell Formulas
Range	Formula
D2:D4	D2	=C2-B2

 

[pankajgrover]

@Sufiyan97 Hi, In below example how to use IFERROR or any other function to resolve issue #VALUE! in R Column below:

Shares Trading.xlsx
	A	B					G	H					M	N	O		Q	R
10	Crude Oil	100.00					0.5	6162.00					0.5	6209.00	250		47.00	2100
11	Crude Oil	100.00					0.5	6197.00					0.5	6610.00	250		413.00	20400
12																		#VALUE!
13	Natural Gas	1250.00					1	433.00					1	437.10	500		4.10	4625
14	Natural Gas	1250.00					1	425.00									5.40	#VALUE!
15																		#VALUE!
16																		#VALUE!
KK

Cell Formulas
Range	Formula
B10:B16	B10	=IFERROR(VLOOKUP(A10,Prices!$A$2:$D$25,2,0),0)
O10:O16	O10	=IF(AND(N10<>"",H10<>""),C10*M10,"")
Q10:Q16	Q10	=IF(N10="",D10,N10)-IF(H10="",D10,H10)
R10:R16	R10	=Q10*IF(M10="",G10,M10)*B10-O10

Named Ranges
Name	Refers To	Cells
Shares	=OFFSET(Prices!$A$2,0,0,COUNTA(Prices!$A$2:$A$25))	B10:B16

Cells with Data Validation
Cell	Allow	Criteria
A10:A16	List	=Shares

 

[Sufiyan97]

Try in R10

=IFERROR(Q10*IF(M10="",G10,M10)*B10-O10,"")

 

[pankajgrover]

Try in R10

=IFERROR(Q10*IF(M10="",G10,M10)*B10-O10,"")

Wow so quick support, Its working great. Thanks so much @Sufiyan97 and @Peter_SSs
. I so much appreciate this forum. Thanks to all respected and great members for helping me .

 

[pankajgrover]

Try in R10

=IFERROR(Q10*IF(M10="",G10,M10)*B10-O10,"")

After apply this formula R14 is also going blank instead of calculation. But i did not want this cell blank ? How to do this

 

[Sufiyan97]

After apply this formula R14 is also going blank instead of calculation. But i did not want this cell blank ? How to do this

So in R14 there is no error and it's showing blank?

 

[pankajgrover]

So in R14 there is no error and it's showing blank?

Here hv data and calculation in R14. Now what i did, this R colmn. depends on O10. I changed O10 formula from =IF(AND(N10<>"",H10<>""),C10*M10,"") to =IF(AND(N10<>"",H10<>""),C10*M10,0) so that this column represent 0 in all blank ones and after that i did not use iferror option in R column. And after that R14 also showing calculated data. But after this R Col. Blank cell shoes 0. In order to tackle 0 i format Col. R as 0;-0;;@,
This way i manage this.. But i am thinking that is there was any alternate way also to tackle this issue ?

 

[Sufiyan97]

You can use this in column R but this will double your formula

=IF("YourFormula"=0,"","YourFormula")

 

[pankajgrover]

You can use this in column R but this will double your formula

=IF("YourFormula"=0,"","YourFormula")

"YourFormula" what will i put in this ? Can you little more describe from above excel sheet example .

 

[Sufiyan97]

Try

=IF(Q10*IF(M10="",G10,M10)*B10-O10=0,"",Q10*IF(M10="",G10,M10)*B10-O10)

 

[pankajgrover]

Try

=IF(Q10*IF(M10="",G10,M10)*B10-O10=0,"",Q10*IF(M10="",G10,M10)*B10-O10)

hi @Sufiyan97 and @Peter_SSs , I just checked this, still not worked for me.. here excel now. i want S column result in R column exactly. how to fix this

Shares Trading.xlsx
	A	B	C	D			G	H					M	N	O		Q	R	S
2	Name	Units	Brok	LP			Qty	Price					S.Qty	S.Price	Brok.		Diff.	P&L	Result should be
10	Crude Oil	100.00	500.00	6621.00			0.5	6162.00					0.5	6209.00	250.00		47.00	2100	2100
11	Crude Oil	100.00	500.00	6621.00			0.5	6197.00					0.5	6610.00	250.00		413.00	20400	20400
16																		#VALUE!
22	Natural Gas	1250.00	500.00	430.40			1	433.00					1	437.10	500.00		4.10	4625	4625
23	Natural Gas	1250.00	500.00	430.40			1	425.00									5.40	#VALUE!	6750
kk

Cell Formulas
Range	Formula
B10:B11,B16,B22:B23	B10	=IFERROR(VLOOKUP(A10,Prices!$A$2:$D$25,2,0),0)
C10:C11,C16,C22:C23	C10	=IFERROR(VLOOKUP(A10,Prices!$A$2:$D$25,3,0),0)
D10:D11,D16,D22:D23	D10	=IFERROR(VLOOKUP(A10,Prices!$A$2:$D$25,4,0),0)
O10:O11,O16,O22:O23	O10	=IF(AND(N10<>"",H10<>""),C10*M10,"")
Q10:Q11,Q16,Q22:Q23	Q10	=IF(N10="",D10,N10)-IF(H10="",D10,H10)
R10:R11,R16,R22:R23	R10	=Q10*IF(M10="",G10,M10)*B10-O10

Named Ranges
Name	Refers To	Cells
Shares	=OFFSET(Prices!$A$2,0,0,COUNTA(Prices!$A$2:$A$25))	B10:D11, B16:D16, B22:D23

Cells with Data Validation
Cell	Allow	Criteria
A3:A23	List	=Shares