If Formula with text and numbers

[dilshod_k]

Hi there,
I'm trying to write code for backtesting of trading strategy.
Given:
Active Worksheet with:
Range C2:C200 with range of Text values: Buy, Sell or empty cell
Range B2:B200 with Closing Price
Lets assume B2= 7.30 is a price per share


Reference Worksheet ("Control") with:
Range.B("10").Value= 1000 is a Buying power in USD
Range.B("11").Value=5 is commission per trade in USD

How to write formula with conditions as it follows:

if C2 = "Buy" Then (1000-5)/7.30 write down result in D2 - whole number!
if C2 = "Sell" Then (D2*7.30)-5 write down result in E2
if C2 = empty cell then do nothing

Thank you in advance

 

[jproffer]

See if that does what you want.

Book1
	B	C	D	E
2	$7.30	Sell		992.8
Sheet2

Cell Formulas
Range	Formula
D2	=IF(C2="Buy",ROUNDDOWN((Control!B10-Control!B11)/Sheet2!B2,0),"")
E2	=IF(C2="Sell",(ROUNDDOWN((Control!B10-Control!B11)/B2,0))*B2,"")

 

[dilshod_k]

See if that does what you want.

	B	C	D	E

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: center"]$7.30[/TD]
[TD="align: center"]Sell[/TD]

[TD="align: right"]992.8[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=IF(C2="Buy",ROUNDDOWN((Control!B10-Control!B11)/Sheet2!B2,0),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=IF(C2="Sell",(ROUNDDOWN((Control!B10-Control!B11)/B2,0))*B2,"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks a lot for a prompt response.
I'm afraid I was not able to use this formula as I need to use it as VBA code.
Apparently I did not described properly the whole story.
I've got excel file with VBA code that allows download of historical prices for hundreds if not thousands of shares. For each share code creates separate sheet with name=symbol of the share. After that code makes calculations (for each/and on each sheet separately) based on several indicators and gives signals: Buy, Sell or empty cell.
I tried to write code But as I'm just doing first steps in VBA coding I can't write with proper syntax, so I use combination of Macros recording with manual adjustments.
Here is the sample of the code I tried to write:

Dim ws As Worksheet


For Each ws In Sheets

If ws.Name <> "Control" And ws.Name <> "Response" Then

ws.Activate


Range("Q1").Select
Selection.NumberFormat = "?#,##0.00"
ActiveCell.FormulaR1C1 = "EQUITY"
Range("Q2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-4].value = ""BUY"",RoundDown((Control!B10-Control!B11)/ActiveSheet!B2,0),""""))"
Range("Q2").Select
Selection.AutoFill Destination:=Range("Q2:Q150")
Range("R1").Select
Selection.NumberFormat = "?#,##0.00"
ActiveCell.FormulaR1C1 = "(USD)"
Range("R2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-4].value = ""SELL"",Roundown((ActiveSheet!D2*ActiveSheet!B2) - Control!B11,0),""""))"
Range("R2").Select
Selection.AutoFill Destination:=Range("R2:R150")

Range("R2").Select

End If