Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
if_worksheet function help please
Hi,
I think i posed this question already but i dont think it made sense. So, Im basically trying to do analysis on a variety of trades in a list that have their symbols in range b1:b500. In addition to the symbol name, i also check 3 other criteria before calculating. So , i have sum(if statements that consider these criterion. The problem is that out of all the trades there are 10 unique futures products each with their own unique multiplier and formatting. They are easy to include in the formula because they each are represented by their own column in the analysis worksheet. The tricky part is that after these 10 unique futures, there are 1000's of other stocks i trade that have different symbols. However they ALL share the same parameters for analysis (except for name). They can all be considered as the 11th product. So my question is, in the column where i group stocks together for analysis, how can i write the sum(if formula that will consider the 3 other criteria in a trade in addition to the symbol name, but where in this case the symbol can be anything OTHER THAN the 10 unique futures symbols that i have in a list elsewhere in the worksheet. Ive tried OR statements: OR(a1:a500 <"ES, a1:a20<"NQ" etc etc but it doesnt count each occurrence, it only results in true/false. How can i write a formula that looks at 3 other critera, and then also looks to see if the name is NOT in the listed range of names.... and if true - counts only those occurrences or gives a sum for only those occurring rows ? The sum(if statement i wrote will consider the 4 criteria... except the 4th one (name of symbol not in range) will only give a true/false and thus if it is true - the formula will count EVERY trade that has the other 3 criteria as true. If the OR statement is false, it results in the false result (0). I have: (1st 3 are other criterion, then the OR statement checks names)- =sum(IF($C$2:$C$1028<0,IF($G$2:$G$1028<=$H$1,IF($ H$2:$H$1028="scalp",OR($B$2:$B$1028=B3,$B$2:$B$102 8=C3,$B$2:$B$1028=D3,$B$2:$B$1028=E3,$B$2:$B$1028= F3,$B$2:$B$1028=G3,$B$2:$B$1028=H3,$B$2:$B$1028=I3 ,$B$2:$B$1028=J3,$B$2:$B$1028=K3),n2:1028,0))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Data Validation/Lookup function does function correcty | Excel Worksheet Functions | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
User-Defined Function pre-empting Built-in Function? How to undo???? | Excel Programming |