  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",""),""))
``````

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",""),""))
``````

Thanks You
Maths-2.xlsm
Microsoft ExcelMicrosoft OfficeMicrosoft Applications Last Comment
Naresh Patel

8/22/2022 - Mon
Rob

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]>=RC[-1],R3C13-(R[-1]C[-1]*61.8%)<R[-1]C[-6],(R[-1]C[-8]-R3C14)>3,AVERAGE((RC[-6]-RC[-5]),(RC[-6]-RC[-5]),(RC[-6]-RC[-5]))>(R[-1]C[-6]-R[-1]C[-5])),""Hit"",""""),IF(R3C12=""Low"",IF(AND(R[-1]C[-1]>=RC[-1],R3C13+(R[-1]C[-1]*61.8%)>R[-1]C[-5],(R[-1]C[-8]-R3C14)>3,AVERAGE((RC[-6]-RC[-5]),(RC[-6]-RC[-5]),(RC" & _
"]C[-5]))<(R[-1]C[-6]-R[-1]C[-5])),""Hit"",""""),""""))"
``````
Rob

New file attached with formula for HIT changed
Naresh Patel

Dint see any attachment...
Rob

yeah it's been doing that a bit lately
Maths-2.xlsm
Naresh Patel

Sir there is some problem in this...

Thanks
Naresh Patel

it is good in your end?
Naresh Patel

I am not in hurry just need to know.... did you got my point?
Rob

yes and i do get the error as well.... won't be long
Naresh Patel

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
Naresh Patel

Any luck?
Rob

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
Rob

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.
Rob

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Naresh Patel