Solved

Math3

Posted on 2014-01-12
15
170 Views
Last Modified: 2014-01-13
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
Comment
Question by:itjockey
  • 8
  • 7
15 Comments
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39775791
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
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39775794
New file attached with formula for HIT changed
0
 
LVL 8

Author Comment

by:itjockey
ID: 39775800
Dint see any attachment...
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39775811
yeah it's been doing that a bit lately
Maths-2.xlsm
0
 
LVL 8

Author Comment

by:itjockey
ID: 39775814
Sir there is some problem in this...MessageCode

Thanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 39775840
it is good in your end?
0
 
LVL 8

Author Comment

by:itjockey
ID: 39775914
I am not in hurry just need to know.... did you got my point?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39775917
yes and i do get the error as well.... won't be long
0
 
LVL 8

Author Comment

by:itjockey
ID: 39776410
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
 
LVL 8

Author Comment

by:itjockey
ID: 39776911
Any luck?
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39777983
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
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39777989
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
 
LVL 42

Accepted Solution

by:
Rob Jurd, EE MVE earned 500 total points
ID: 39778033
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
 
LVL 8

Author Closing Comment

by:itjockey
ID: 39778323
Awesome
0
 
LVL 8

Author Comment

by:itjockey
ID: 39778332
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

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now