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]

It may not be possible with your worksheet but if any cell(s) had a non-numerical value that "blank" check may result in an error as in D3. Using COUNT, as in col E, would ensure that both cells were numbers before the subtraction was attempted.

The COUNT idea also makes it easier to extend if the number of cells involved is greater. For example, in the bottom section, I only want a total if all 6 cells in the row are filled with numbers.

22 12 25.xlsm
	A	B	C	D	E	F	G
1	Item	A	B	Total	Total
2	pen	2500	3500	1000	1000
3	pencil	x	5000	#VALUE!
4	rubber	4000
5
6							Total
7	3	1		5	5	6
8	4	7	9	2	4	4	30
COUNT

Cell Formulas
Range	Formula
D2:D4	D2	=IF(OR(B2="",C2=""),"",C2-B2)
E2:E4	E2	=IF(COUNT(B2:C2)=2,C2-B2,"")
G7:G8	G7	=IF(COUNT(A7:F7)=COLUMNS(A7:F7),SUM(A7:F7),"")

 

[pankajgrover]

It may not be possible with your worksheet but if any cell(s) had a non-numerical value that "blank" check may result in an error as in D3. Using COUNT, as in col E, would ensure that both cells were numbers before the subtraction was attempted.

The COUNT idea also makes it easier to extend if the number of cells involved is greater. For example, in the bottom section, I only want a total if all 6 cells in the row are filled with numbers.

22 12 25.xlsm
	A	B	C	D	E	F	G
1	Item	A	B	Total	Total
2	pen	2500	3500	1000	1000
3	pencil	x	5000	#VALUE!
4	rubber	4000
5
6							Total
7	3	1		5	5	6
8	4	7	9	2	4	4	30
COUNT

Cell Formulas
Range	Formula
D2:D4	D2	=IF(OR(B2="",C2=""),"",C2-B2)
E2:E4	E2	=IF(COUNT(B2:C2)=2,C2-B2,"")
G7:G8	G7	=IF(COUNT(A7:F7)=COLUMNS(A7:F7),SUM(A7:F7),"")

Thanks for reply. I also want to ask until B4 or C4 values did not come , i want to replace this values with suppose with G column temporary values until B4 or C4 values not come. how to do that ?

 

[Peter_SSs]

I also want to ask until B4 or C4 values did not come , i want to replace this values with suppose with G column temporary values until B4 or C4 values not come. how to do that ?

I don't understand that. Can you show 6 to 8 sample dummy rows of data and the expected results with XL2BB?

 

[pankajgrover]

I don't understand that. Can you show 6 to 8 sample dummy rows of data and the expected results with XL2BB?

I little bit manage replace "" with another condition. Here actual example below i used this if and or formula in M column. if f or j column have no price then it will take price c column until actual i fill price in f or j column. But in this situation i face another issue M18 cell which gives wrong value due to -neg entry doing + . here result should be 3 instead of 863 ? how to fix that ?

Shares Trading (1).xlsx
	A	B	C	D	E	F		H	I	J	K		M	N
15	Silver	30.00	69033.00	22/12/2022	1	69191.00		Silver	1	69750.00	1000.00		559	15770
16	Silver	30.00	69033.00					Silver	1	64972.50			-4061	-121815
17	Silver	30.00	69033.00					Silver	0.5	64845.00			-4188	-62820
18	Natural Gas	1250.00	430.40	22/12/2022	1	433.00		Natural Gas					-863	-3250
19
hh

Cell Formulas
Range	Formula
B15:B19	B15	=IFERROR(VLOOKUP(A15,Prices!$A$2:$C$25,2,0),0)
C15:C19	C15	=IFERROR(VLOOKUP(A15,Prices!$A$2:$C$25,3,0),0)
H15:H19	H15	=hh!$A15
M15:M19	M15	=IF(OR(F15="",J15=""),J15-F15-C15,J15-F15)
N15:N19	N15	=(((E15-I15)*B15)*C15)-G15+L15

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

Cells with Data Validation
Cell	Allow	Criteria
A15:A19	List	=Shares

 

[Peter_SSs]

In that sample, are all the column M results what you want except cell M18?

If not, then please manually enter a formula in each individual column M cell to give the result that you want.

 

[pankajgrover]

In that sample, are all the column M results what you want except cell M18?

If not, then please manually enter a formula in each individual column M cell to give the result that you want.

C col. means last price close, F col. means buy price and j Col means sale price. M col. result is diff between sale and buy price. If item still not hv buy or sale price it will calculate last close price that is C col. based price. in M18 i am using this =IF(OR(F18="",J18=""),J18-F18-C18,J18-F18) for result 433-430 = 3 but what happening that sale still no hv price so it taking 0-433-last price 430 = 863 instead of 3 , how to fix that ? if i hv sale price and buy price blank then no issue, this formula work but if i have no sale price or blank and i have buy price then it gives -- to +. That's the issue..

 

[Peter_SSs]

I can't see that you specifically answered my question.

In that sample, are all the column M results what you want except cell M18?

If not, then please manually enter a formula in each individual column M cell to give the result that you want.

 

[pankajgrover]

I can't see that you specifically answered my question.

Shares Trading (1).xlsx
	A		C			F				J			M		O	P
2	Name		LAST PRICE			BUY				SALE			Diff.		I WANT THIS RESULT	MANUALY FORMULA IN M ROW
3	Silver		69033.00			69191.00				69750.00			559		559	IF(OR(F3="",J3=""),J3-F3-C3,J3-F3)
4	Silver		69033.00							64972.50			-4061		-4061	IF(OR(F4="",J4=""),J4-F4-C4,J4-F4)
5	Silver		69033.00							64845.00			-4188		-4188	IF(OR(F5="",J5=""),J5-F5-C5,J5-F5)
6	Natural Gas		430.00			433.00							-863		-3	IF(OR(F6="",J6=""),J6-F6-C6,J6-F6)
KK

Cell Formulas
Range	Formula
M3:M6	M3	=IF(OR(F3="",J3=""),J3-F3-C3,J3-F3)

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

M6 giving result -863 instead of -3, how to fix this, I post now new example above for easy understanding ? Thanks

 

[Peter_SSs]

Thanks for confirming that the other 3 results are what you want. You have now swapped wanting M6 to -3 whereas you said 3 before, but hopefully that will be easy if I get it the wrong way around.

22 12 25.xlsm
	C			F				J			M		O
2	LAST PRICE			BUY				SALE			Diff.		I WANT THIS RESULT
3	69033			69191				69750			559		559
4	69033							64972.5			-4060.5		-4061
5	69033							64845			-4188		-4188
6	430			433							-3		-3
Formula

Cell Formulas
Range	Formula
M3:M6	M3	=IF(J3="",C3,J3)-IF(F3="",C3,F3)

 

[pankajgrover]

Thanks for confirming that the other 3 results are what you want. You have now swapped wanting M6 to -3 whereas you said 3 before, but hopefully that will be easy if I get it the wrong way around.

22 12 25.xlsm
	C			F				J			M		O
2	LAST PRICE			BUY				SALE			Diff.		I WANT THIS RESULT
3	69033			69191				69750			559		559
4	69033							64972.5			-4060.5		-4061
5	69033							64845			-4188		-4188
6	430			433							-3		-3
Formula

Cell Formulas
Range	Formula
M3:M6	M3	=IF(J3="",C3,J3)-IF(F3="",C3,F3)

Its Awesome, that is exactly i want. Thank you @Peter_SSs