Link to home
Create AccountLog in
Avatar of Naresh Patel
Naresh PatelFlag for India

asked on

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
Avatar of Rob
Rob
Flag of Australia image

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

New file attached with formula for HIT changed
Avatar of Naresh Patel

ASKER

Dint see any attachment...
yeah it's been doing that a bit lately
Maths-2.xlsm
Sir there is some problem in this...User generated imageUser generated image

Thanks
it is good in your end?
I am not in hurry just need to know.... did you got my point?
yes and i do get the error as well.... won't be long
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
Any luck?
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
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.
ASKER CERTIFIED SOLUTION
Avatar of Rob
Rob
Flag of Australia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Awesome
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