• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 182
  • Last Modified:

Math3

Hi Expert,


just need to change formula for Hit in Code.
previous
=IF($L$3="High",IF(AND(H4>=H9,$M$3-(H4*61.8%)<C4),"Hit",""),IF($L$3="Low",IF(AND(H4>=H9,$M$3+(H4*61.8%)>D4),"Hit",""),""))

Open in new window


Change to
=IF($L$3="High",IF(AND(H4>=H9,$M$3-(H4*61.8%)<C4,(A4-$N$3)>3,AVERAGE((C5-D5),(C6-D6),(C7-D7))>(C4-D4)),"Hit",""),IF($L$3="Low",IF(AND(H4>=H9,$M$3+(H4*61.8%)>D4,(A4-$N$3)>3,AVERAGE((C5-D5),(C6-D6),(C7-D7))<(C4-D4)),"Hit",""),""))

Open in new window



Thanks You
Maths-2.xlsm
0
Naresh Patel
Asked:
Naresh Patel
  • 8
  • 7
1 Solution
 
RobOwner (Aidellio)Commented:
This is the formula you need to change it to in the code, in the WriteFormulas function:

ActiveCell.FormulaR1C1 = _
        "=IF(R3C12=""High"",IF(AND(R[-1]C[-1]>=R[4]C[-1],R3C13-(R[-1]C[-1]*61.8%)<R[-1]C[-6],(R[-1]C[-8]-R3C14)>3,AVERAGE((RC[-6]-RC[-5]),(R[1]C[-6]-R[1]C[-5]),(R[2]C[-6]-R[2]C[-5]))>(R[-1]C[-6]-R[-1]C[-5])),""Hit"",""""),IF(R3C12=""Low"",IF(AND(R[-1]C[-1]>=R[4]C[-1],R3C13+(R[-1]C[-1]*61.8%)>R[-1]C[-5],(R[-1]C[-8]-R3C14)>3,AVERAGE((RC[-6]-RC[-5]),(R[1]C[-6]-R[1]C[-5]),(R[2]C" & _
        "]C[-5]))<(R[-1]C[-6]-R[-1]C[-5])),""Hit"",""""),""""))"

Open in new window

0
 
RobOwner (Aidellio)Commented:
New file attached with formula for HIT changed
0
 
Naresh PatelTraderAuthor Commented:
Dint see any attachment...
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
RobOwner (Aidellio)Commented:
yeah it's been doing that a bit lately
Maths-2.xlsm
0
 
Naresh PatelTraderAuthor Commented:
Sir there is some problem in this...MessageCode

Thanks
0
 
Naresh PatelTraderAuthor Commented:
it is good in your end?
0
 
Naresh PatelTraderAuthor Commented:
I am not in hurry just need to know.... did you got my point?
0
 
RobOwner (Aidellio)Commented:
yes and i do get the error as well.... won't be long
0
 
Naresh PatelTraderAuthor Commented:
Do we able to calculation columns hardcoded in code? I.e. If there is any data in gap or hit column code doesn't affect. Just asking if it takes too much coding then leave it.

Thanks
0
 
Naresh PatelTraderAuthor Commented:
Any luck?
0
 
RobOwner (Aidellio)Commented:
No it's not the coding that's the issue. Sorry I have been sleeping.
The best way to put the formula into the code is to record a macro and then type the formula into the cell you want.  The recorded code shows the formula as R1C1 meaning it referenced cells relatively from the cell it's in R[-1]C14 rather than the absolute way you're used to eg A1
0
 
RobOwner (Aidellio)Commented:
The error we're getting is related to an illegal character in the formula. Just having an issue working that out but it's far easier than trying to hardcode the formula as you suggest.
0
 
RobOwner (Aidellio)Commented:
ok when the macro recorded it didn't fill in all the formulas!  I manually went through the formula and added the missing parts
Maths-2.xlsm
0
 
Naresh PatelTraderAuthor Commented:
Awesome
0
 
Naresh PatelTraderAuthor Commented:
Sir rob,

Last Question  in this line  Math Final -  as I had allotted specific time on this topic i.e. ZigZag & it is over. Now I have to move next topic. So if just in case if I need to change formula for Gap & Hit , I ll better be prepaid. if you fill so.... requesting this favor.


Thank You
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now