Spreadsheet Cell Needs To Remain Blank

Question

I have created a number of automated calculations for my trading business. However, I need to be able to reflect blank cells with no value at all.

You will notice that line 47 is completely filled and properly calculated. However, line 48 has no date or entry price. I need to have column P, Column Q, and Column S reflect blank. The calculation for each column is below, and the results can be seen in the picture.

Calc under P48 is:

=IF($D48="","",IF(OR(AND($D48="buy",$N48>=$C48),AND($D48="sell",$N48<=$C48)),"Win","Loss"))

Calc under Q48 is:

=IF(AND($P48="Win",$D48="buy"),ABS($N48-$C48)*10000,IF(AND($P48="Win",$D48="sell"),($C48-$N48)*10000,IF(AND($P48="Loss",$D48="buy"),($N48-$C48)*10000,IF(AND($P48="Loss",$D48="sell"),($C48-$N48)*10000))))

Lastly calc for s48 is:

=IF($P48="Loss",$Q48*1,"")

This is a very complicated and detailed spreadsheet. Can someone help me process these calculations to make them work properly?

Answer 1

Use the ISBLANK function. Column D is not empty since it contains a zero on rows that do not have a date or entry price (like row 48), so I would use either the date column, the entry price column, or both. In cell P48:

=IF(ISBLANK(B48),"",IF(OR(AND($D48="buy",$N48>=$C48),AND($D48="sell",$N48<=$C48)),"Win","Loss"))

Or - If either the date or the entry price is not filled in, your formula will return a blank cell with this in cell P48:

=IF(OR(ISBLANK(B48),ISBLANK(C48)),"",IF(OR(AND($D48="buy",$N48>=$C48),AND($D48="sell",$N48<=$C48)),"Win","Loss"))

Add ISBLANK to the beginning of columns Q & S formulas too. S48:

=IF(ISBLANK(B48),"",IF($P48="Loss",$Q48*1,""))

Answer 2

The answer came through trial/error from the suggestions shown above. I also used File/Option/Advanced to remove all zero entries on the worksheet. In addition placed a blank condition in Q48.