Need help in If formula with multiple conditions

[pankajgrover]

Hi, I want AQ6 should be blank cell if value is less than 1 in below example . How to configure this with existing formula
=IF(OR(AK3="",AN3=""),"",(H3-O3)*B3*D3+IF(AK3="S",-K3+AO3-Q3-J3+AS3,-AO3+R3-Q3-J3+AS3))

Shares Trading test.xlsx
	AQ
2	P&L
3	3650.00
4	5480.00
5	591.47
6	0.26
Vishal

Cell Formulas
Range	Formula
AQ3:AQ6	AQ3	=IF(OR(AK3="",AN3=""),"",(H3-O3)*B3*D3+IF(AK3="S",-K3+AO3-Q3-J3+AS3,-AO3+R3-Q3-J3+AS3))

 

[arthurbr]

Perhaps

=IF(OR(AK3="",AN3=""),"",MAX((H3-O3)*B3*D3+IF(AK3="S",-K3+AO3-Q3-J3+AS3,-AO3+R3-Q3-J3+AS3),1))

OTOH your formula does not say what happens if AK3<>"S"...

 

[pankajgrover]

Perhaps

=IF(OR(AK3="",AN3=""),"",MAX((H3-O3)*B3*D3+IF(AK3="S",-K3+AO3-Q3-J3+AS3,-AO3+R3-Q3-J3+AS3),1))

OTOH your formula does not say what happens if AK3<>"S"...

Your formula giving 1 result instead of blank in AQ6. I want this cell should be blank.

 

[pankajgrover]

Hi @Peter_SSs any guidance to solve formula #1 post. Thanks

 

[arthurbr]

Better ?

=IF(OR(AK3="",AN3="",((H3-O3)*B3*D3+IF(AK3="S",-K3+AO3-Q3-J3+AS3,-AO3+R3-Q3-J3+AS3))<1),"",(H3-O3)*B3*D3+IF(AK3="S",-K3+AO3-Q3-J3+AS3,-AO3+R3-Q3-J3+AS3))

 

[pankajgrover]

Hi Thanks, its now doing 0 to Blank, but new issue arises it giving all -negative values to blank also. i did not think before <1 will give all negative values to Blank also . Actually i am calculating profit loss. Before i was doing set format 0;-0;;@ for convert 0 to blank then there was no issue . But one of my cell have figure now in AQ6 is 0.258, if i decrease decimal to single, it shows 0 but format 0;-0;;@ is not working here in the AQ6 cell. I also do not know how can i forcefully round 0.258 to this 0.00 so that this format 0;-0;;@ will work. Can we use round function to make cell AQ6=0 instead of decimals and then we can set format format 0;-0;;@. Or another alternate as you already provided formula can we change condition from <1 to (-1 to +1 range) then it should blank AQ6. As you master of excel please give me better advise. Thanks for support.

 

[arthurbr]

Perhaps

=IF(OR(AK3="",AN3="",ABS(((H3-O3)*B3*D3+IF(AK3="S",-K3+AO3-Q3-J3+AS3,-AO3+R3-Q3-J3+AS3)))<1),"",(H3-O3)*B3*D3+IF(AK3="S",-K3+AO3-Q3-J3+AS3,-AO3+R3-Q3-J3+AS3))

 

[pankajgrover]

Perhaps

=IF(OR(AK3="",AN3="",ABS(((H3-O3)*B3*D3+IF(AK3="S",-K3+AO3-Q3-J3+AS3,-AO3+R3-Q3-J3+AS3)))<1),"",(H3-O3)*B3*D3+IF(AK3="S",-K3+AO3-Q3-J3+AS3,-AO3+R3-Q3-J3+AS3))

Thanks its working now. Great support.

 

[arthurbr]

You're welcome