help with simple formula

[Smithb]

Hi there, my spreadsheet has 5 columns, with headings.. A-date, B-enter long, C-enter short, D-exit, e-%. I would like to create a formula to calculate % in column e. If column a-date is blank, e remains blank. If a date is listed in column a, and a number is listed in b&D, e = ((d-b)/d), if B is blank and a number is listed in C&D, e=((d-c/d). I would like to create a formula that will combine these if/and scenarios and give me one result in column e. There will always be only one number in B or C. Thanks.

 

[awoohaw]

Try this:

=IF(A3="","",(D3-IF(ISBLANK(B3),C3,B3))/D3)

 

[Dave Patton]

T202302.xlsm
	A	B	C	D	E	F
1	Date	Long	Short	Exit	Percentage
2	7-Feb-23		40	100	60%	60%
1b

Cell Formulas
Range	Formula
A2	A2	=TODAY()
E2	E2	=IF(A2="","",(D2-MAX(B2:C2))/D2)
F2	F2	=IF(A2="","",IF(AND(B2<>"",D2<>""),(D2-B2)/D2,IF(AND(C2<>"",D2<>""),(D2-C2)/D2)))

 

[Smithb]

T202302.xlsm
	A	B	C	D	E	F
1	Date	Long	Short	Exit	Percentage
2	7-Feb-23		40	100	60%	60%
1b

Cell Formulas
Range	Formula
A2	A2	=TODAY()
E2	E2	=IF(A2="","",(D2-MAX(B2:C2))/D2)
F2	F2	=IF(A2="","",IF(AND(B2<>"",D2<>""),(D2-B2)/D2,IF(AND(C2<>"",D2<>""),(D2-C2)/D2)))

thanks very much.

 

[Dave Patton]

Thanks for the feedback.