HELP! Formula to compare 2 Ranges + List reference from a different sheet

[datacentralized]

Hi guys,

I am hoping someone can help me out here because I cannot for the life of me figure out what to do or if this is even possible.I have attached to this thread the workbook. I am using excel 2007.

In my "Template" sheet I would like to analyze range combinations against a reference list I created in a second sheet named "Data". The reference list is called "StrategiesList" and comprises of the complete "A" column (if you click on A you should see it). What I want to do is compare A2:A32 to B2:B32 to see if those combinations together have anything listed in the "StrategiesList".

For example the following combined fields together that contain anything in the "StrategiesList". Note that some days contain more then one entry.
A2 +B2
A2 + B2:B4
A5+B5

This would return how many "days" have atleast 1 matching input and show the result in the data field B33 where it says 0 DAYS TRADED.
This should currently reflect 17 days as per the sheet.

I apologize in advance for this long and perhaps confusing thread. I hope it makes sense to someone. Thanks to all in advance for any help

 

[vletm]

datacentralized
Do You need value '17' for B38?
... Is this something?
Some of You references ... could be something else...

 

[datacentralized]

datacentralized
Do You need value '17' for B38?
... Is this something?
Some of You references ... could be something else...

Hi vletm,

Thanks for your kind help I appreciate it very much. To answer your question yes the value for B38 should be "17" but unfortunately I am not sure if this is the correct approach since it is not referencing the "StrategiesList" in the "Data" sheet section in the formula. Maybe I am not understanding what the formula is doing would you be kind enough to break it down for me so I can understand each step of it? It looks like its simply subtracting from the total values of A2:A37. It should also not substract -A39-A40 since it's kind of like an independant formula to accurately count days that have actual strategies.

I tried changing values around and it does seem like it updates the number correctly so this definitely looks like something though!

 

[vletm]

datacentralized
Do You know - what it should express (B38)?
I tried to figure from Your writings, that...
A-column has like days
B-columns has 'something' from which You've already solved 'NOT TRADED' and 'WEEKENDS' - okay?
to 'DAYS TRADED' ... would be 'count of day which has something' (in B-column, there seems to be more than one 'something' per day and some (one) day is blank) which is the 1st part of that formula - okay? ... and A39 & A40 ... there those values which You've solved.
I used those Your 'countif'-formulas in A39 & A40 - okay?
... back to the 1st sentence:
... ... Do You know - what is should express (B38)?
Your ... I am (You are) ... sure that ... B-column values can be ONLY from that list from 'Data'-sheet or how?

 

[datacentralized]

The value of B38 should be "17"

I think you might actually be correct. Let me play with this a bit to see what results I get. I really appreciate your help!

 

[datacentralized]

datacentralized
Do You need value '17' for B38?
... Is this something?
Some of You references ... could be something else...

Is there a way to do this without using cell A38 and putting all the formula together in B38? Or this will NOT work because B39 returns "5 DAYS WHEN NOT TRADED" and that is NOT a numeric only value?

Therefore I do NOT have a choice to first calculate all results as numeric only in a empty field only and THEN your code will work. Correct?

 

[vletm]

datacentralized
If You refer to that my sent file then
... please, open both eyes ....
cell B38 =A38&" DAYS TRADED"
cell B39 =A39&" DAYS WHEN NOT TRADED"
cell B40 =A40&" DAYS FOR WEEKENDS"
cell B41 =A41&" DAYS TOTAL"
More questions?
... or for some reason, You would like to solve those values twice or more times?
... and if need to modify formula, then have to remember to modify it in more than one cell.
... why 'it' should be in Data-sheet?

 

[datacentralized]

datacentralized
If You refer to that my sent file then
... please, open both eyes ....
cell B38 =A38&" DAYS TRADED"
cell B39 =A39&" DAYS WHEN NOT TRADED"
cell B40 =A40&" DAYS FOR WEEKENDS"
cell B41 =A41&" DAYS TOTAL"
More questions?

vletm,

Yes I have a few more questions. I have updated everything but facing problems with a few fields. I have attached the new updated version to this reply for you to look at. Basically I am having problems with column "K" and "L".

For column "K" I am trying to calculate the price difference between the entry price and exit price using =(J2-I2)*E2
I have copied this to all the way down to "K32" and the formula DOES works if you enter all three fields data, but is there a way to make it not show "0$" and only the right answer once the user fills in all three fields J2 I2 E2?

Same thing for column "L". Currently it shows "#DIV/0!" unless user enters entry price and exit price. Is there a way to only show the percentage once user fills in both fields? Thanks for your help it is much appreciated!

 

[vletm]

datacentralized
Case K ... eg by disable zero-values by default -- from preferences > view
Case L ... so far div with zero is hmm? Check cells L2:L5 ... and modify "?" and wanted

 

[datacentralized]

datacentralized
Case K ... eg by disable zero-values by default -- from preferences > view
Case L ... so far div with zero is hmm? Check cells L2:L5 ... and modify "?" and wanted

Disabling zero values could work, but then I do not see the values in currency with the $ sign if they are filled out correctly. Any other suggestions you can think of so that I can keep values with $ before them?

 

[vletm]

datacentralized
hmm?
if ALL values should be there before result then test below...
for cell K6 =IF(AND(E6<>"",I6<>"",J6<>""),(J6-I6)*E6,"")
I cannot figure Your' ... keep values with $ before...