How to use to Iferror and Vlookup makes blank cell to pure true blank

[pankajgrover]

Here is example, how to make D16, D17 show TRUE instead of false. I want here true blank cells..

Shares Trading - Copy.xlsx
	A		C	D
14	Infy		300	FALSE
15	Natural Gas		500	FALSE
16				FALSE
17				FALSE
1

Cell Formulas
Range	Formula
C14:C17	C14	=IFERROR(VLOOKUP(A14,Prices!$A$2:$F$25,3,0),"")
D14:D17	D14	=ISBLANK([@Brok])

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

Cells with Data Validation
Cell	Allow	Criteria
A14:A17	List	=Shares

 

[pankajgrover]

H

How about

=if(o16="","",SUM(((M16*B16)*N16)-O16))

here i am stuck again, below data, how to solve this .. if O column some cells are blank, but i want P column should perform instead of #value because N column have data ...

Shares Trading - Copy.xlsx
	N	O	P
3	6315.00	250	315500
4	6344.00	250	316950
5
6
7	53700.00		#VALUE!
8	55977.00		#VALUE!
9	64972.50		#VALUE!
10	64825.00		#VALUE!
1

Cell Formulas
Range	Formula
O3:O10	O3	=IF(AND(N3<>"",H3<>""),C3*M3,"")
P3:P10	P3	=IF(N3="","",SUM(((M3*B3)*N3)-O3))

 

[Fluff]

What should it perform?

 

[pankajgrover]

What should it perform?

my P clomn formula is=IF(N3="","",SUM(((M3*B3)*N3)-O3)) . so if O3 is blank , it should ignore -O3 value or treat as 0, i want its perform like that =IF(N3="","",SUM(((M3*B3)*N3)-0)).

 

[Fluff]

How about

=IF(N7="","",SUM(((M7*B7)*N7)-IF(O7="",0,O7)))

 

[pankajgrover]

How about

=IF(N7="","",SUM(((M7*B7)*N7)-IF(O7="",0,O7)))

Perfect. Thanks working.
Also advice me generally people used for blank cell in IF condition like i put "" for blank cell . but as you see its not true blank, sometimes hv to calculate with another cells so #value error occurs sometime. What i doing before i do put 0 instead of "" . and afterwards to deal with column showing unnecessary 0 in all fields , i format cell with 0.00;-0.00;;@.
Now as your above examples, i learn today how to do deal with "" with if condition without formatting like cells 0.00;-0.00;;@. So at last what is the best method in your opinion to deals with blank cells calculation.

 

[Fluff]

In my opinion there is no "best" way. It's down to what you want to do & personal preference.

 

[pankajgrover]

In my opinion there is no "best" way. It's down to what you want to do & personal preference.

Hi, i am stuck in if formula:
IF(AH16="S",-I16+AL16,-AL16+P16-O16))
In above 1st formula IF(AH16="S",-I16+AL16,-AL16+P16-O16)) if i forgot to put B or S, this not work refresh properly
I want if AH16="S", then this formula apply -I16+AL16 and if AH16="B", then this formula apply -AL16+P16-O16, otherwise if not found S or B go blank, how to do this.. Thanks

 

[pankajgrover]

@Fluff hi in another i am stuck in below example , i want if G cell is blank then AH result should be "S", and if M cell is blank AH result should be "B", if both G & M cells are blank , AH result should be "" Blank.. if both G & M cells hv some data and both are not blank AH result should be "D" how to do that this also ?

Shares Trading.xlsx
	G						M																					AH
9							1																					S
10							0.5																					S
11	1																											B
12	1																											B
1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AH3:AH109	Cell Value	="B"	text	NO
AH3:AH109	Cell Value	="S"	text	NO
AP3:AX109,U3:AC109	Cell Value	<0	text	NO
AP3:AX109,U3:AC109	Cell	does not contain a blank value	text	NO

 

[Peter_SSs]

@pankajgrover
Just a comment that you are making some of your formulas look more complex that they are by including unnecessary parentheses and and unnecessary function (SUM).
For example the original P16 formula from post #7 as adjusted in post 8 could be changed as follows
Instead of

=IF(O16="","",SUM(((M16*B16)*N16)-O16))

it could be written more simply as

=IF(O16="","",M16*B16*N16-O16)

The formula from post 14 could be changed in a similar way

=IF(N7="","",SUM(((M7*B7)*N7)-IF(O7="",0,O7)))

=IF(N7="","",M7*B7*N7-IF(O7="",0,O7))

In fact, that one could be simplified further

=IF(N7="","",M7*B7*N7-N(O7))

 

[pankajgrover]

Hi @Peter_SSs thanks for guide and simplified my formulas. Can you guide me on post #17 or #18 .. Thanks