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

 

[Peter_SSs]

You're welcome. Glad we got there in the end.

 

[pankajgrover]

You're welcome. Glad we got there in the end.

I am doing another column working but not been able to solve Q3 formula, so i enter manually results in Q3. Again want help for this example below

Shares Trading (1).xlsx
	A	B				F	G					L	M	N		P	Q	R
2	Name	Units				Qty	Price					S.Qty	S.Price	Brok.		Diff.	P&L	I want this result in Q3
3	Silver	30.00				1	69191.00					1	69750.00	1000.00		559	15770	P3*(L3 OR F3)*B3-N3
4	Silver	30.00										1	64972.50			-4060.5	121815
5	Silver	30.00										0.5	64845.00			-4188.0	62820
6	Natural Gas	1250.00				1	433.00									-3	-3750
kk

Cell Formulas
Range	Formula
B3:B6	B3	=IFERROR(VLOOKUP(A3,Prices!$A$2:$C$25,2,0),0)
P3:P6	P3	=IF(M3="",C3,M3)-IF(G3="",C3,G3)

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

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

 

[pankajgrover]

@Peter_SSs need help to solve this above example formula

 

[Peter_SSs]

Using what you wrote in cell R3 it seems this is what you want. .. but it does not match the values you wrote in some of the other rows where you gave no indication of any different calculation.

22 12 25.xlsm
	B				F	G					L	M	N		P	Q	R
2	Units				Qty	Price					S.Qty	S.Price	Brok.		Diff.	P&L	I want this result in Q3
3	30				1	69191					1	69750	1000		559	15770	P3*(L3 OR F3)*B3-N3
4	30										1	64972.5			-4060.5	-121815
5	30										0.5	64845			-4188	-62820
6	1250				1	433									-3	-3750
Formula (2)

Cell Formulas
Range	Formula
Q3:Q6	Q3	=P3*IF(L3="",F3,L3)*B3-N3

 

[pankajgrover]

Using what you wrote in cell R3 it seems this is what you want. .. but it does not match the values you wrote in some of the other rows where you gave no indication of any different calculation.

22 12 25.xlsm
	B				F	G					L	M	N		P	Q	R
2	Units				Qty	Price					S.Qty	S.Price	Brok.		Diff.	P&L	I want this result in Q3
3	30				1	69191					1	69750	1000		559	15770	P3*(L3 OR F3)*B3-N3
4	30										1	64972.5			-4060.5	-121815
5	30										0.5	64845			-4188	-62820
6	1250				1	433									-3	-3750
Formula (2)

Cell Formulas
Range	Formula
Q3:Q6	Q3	=P3*IF(L3="",F3,L3)*B3-N3

thanks this solve my formula

 

[pankajgrover]

Here also i am doing 1 more some complex calculation . In below example AK3 i want actual result 15770. Actually i am trying to do use if formula but i am unable to do. In example my formula works if G4 or M6 have cell blank, but issue arise when G3 or M3 have some both values . So how to make formula to solve this in single AK Result Column,
How to create multiple conditions like i want
1st condition: =(F3-L3)*B3*C3+IF(G3="",-H3+AJ3,-AJ3+O3-N3) it works if G3 and M3 atleast hv 1 empty cell ,
2nd condition: If G3 and M3 no have empty cell then i want this calculation (((F3-L3)*B3)*C3)-H3+AJ3. how i make formula for this I want both conditions in AK3 column. Thanks

Shares Trading sat result.xlsx
	A	B	C			F	G	H				L	M	N	O																			AH	AI	AJ	AK	AL
2	Name	Units	LP			Qty	Price	Amount				S.Qty	S.Price	Brok.	S.Amount																			Qty	Price	Amount	RESULT	RESULT SHOULD BE in AK Col.
3	Silver	30.00	69033.00			1	69191.00	2075730				1	69750.00	1000.00	2091500																			1	69750.00	2091500	-1000	15770 FOR (((F3-L3)*B3)*C3)-H3+AJ3
4	Silver	30.00	69033.00									1	64972.50		1949175																			1	68000.00	2040000	-30990	-30990
5	Silver	30.00	69033.00									0.5	64845.00		972675																			0.5	68000.00	1020000	-15495	-15495
6	Natural Gas	1250.00	430.40			1	433.00	541250																										1	410.00	512500	25500	25500
kk

Cell Formulas
Range	Formula
B3:B6	B3	=IFERROR(VLOOKUP(A3,Prices!$A$2:$C$25,2,0),0)
C3:C6	C3	=IFERROR(VLOOKUP(A3,Prices!$A$2:$C$25,3,0),0)
H3:H6	H3	=((F3*B3)*G3)
O3:O6	O3	=((L3*B3)*M3)-(N3)
AJ3:AJ6	AJ3	=((AH3*B3)*AI3)-(N3)
AK3:AK6	AK3	=(F3-L3)*B3*C3+IF(G3="",-H3+AJ3,-AJ3+O3-N3)

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

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

 

[Sufiyan97]

Try in AK3

=IF(AND(G3<>"",M3<>""),(((F3-L3)*B3)*C3)-H3+AJ3,(F3-L3)*B3*C3+IF(G3="",-H3+AJ3,-AJ3+O3-N3))

 

[pankajgrover]

Try in AK3

=IF(AND(G3<>"",M3<>""),(((F3-L3)*B3)*C3)-H3+AJ3,(F3-L3)*B3*C3+IF(G3="",-H3+AJ3,-AJ3+O3-N3))

Thanks its working perfect .

 

[pankajgrover]

@Sufiyan97 here 1 example below i want if E2 AND D2 both cells hv data then only F2 Col. pick value from B col. if one of blank between E2 and D2 then corresponding F col. cell should be empty. how to write formula in F column. Thanks

New Microsoft Excel Worksheet.xlsx
	A	B	C	D	E	F
1	ITEM	BROK		B.PRICE	S.PRICE	BROK
2	GOLD	500		100	200	500
3	SILVER	250		90
Sheet1

 

[Sufiyan97]

Try in F2

=IF(AND(D2<>"",E2<>""),B2,"")