• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Whats Wrong With This Formulae ???

P

pop

New Member
=OR(AND(D3=$I$3,E3=$J$3),AND(D3=$I$3,F3=$K$3),AND(D3=$I$3,F3=$K$3),AND(E3=$J$3,F3=$K$3),AND(E3=$J$3,G3=$L$3),AND(F3=$K$3,G3=$L$3))


I am trying to filter all 2 criteria combinations of a 4 criteria table


I am not sure why this returns true when three cells are selected - is there any way this can be adapted to return true if only 2 cells are selected


this is the criteria


I J K L


Ethnicity Gender Grade Status

m k 1


this is the result and comparison data


A D E F G

0 Ethnicity Gender Grade Status

TRUE Unknown M K 1

TRUE Unknown M K 1

TRUE Unknown M K 0

TRUE Unknown M K 0

FALSE Unknown F K 0

FALSE Unknown F K 0


Any assistance greatly appreciated !
 
Hi Pop...can you upload an example.....


on the forum homepage, there are a few sticky post where you can find details of posting a workbook.
 
Hi Indian thanks for the reply just learned how to upload the example


I hope the link works


https://www.dropbox.com/s/zrjf0wjj77ligf5/SampleData%209703a.xls
 
Hi ,


I am not sure whether this is what you are looking for :


In cell J5 , put in the formula : =SUMPRODUCT(--(A3:A85))


In cell P3 , put in the following array formula , entered using CTRL SHIFT ENTER :


=IF($O3>$J$5,"",INDEX(D$3:D$85,SMALL(IF($A$3:$A$85,ROW($A$3:$A$85)-MIN(ROW($A$3))+1),$O3)))


Copy this across and down.


Narayan
 
Hi NARAYANK991


thanks for the reply,


I am using the excel spreadsheet for and xcelsius 4.5 dashboard and it does not support the array functions of excel


i am really limited to solutions that use match and index functions


I really need a non array solution
 
i was thinking that the only solution was to add a lookup column that matches the criteria selected using the logical operators but cant figure out how to filter only for the criteria selected
 
I have not used xcelsius. In cell A3:

=IF(((D3=$I$3)+(E3=$J$3)+(F3=$K$3)+(G3=$L$3))=COUNTA($I$3:$L$3),ROW(),FALSE)


and copy down the formula. Is this what you want?
 
Hi shrivallabha


Thanks for your reply, Unfortunately Xcelsius does not accept the ROW or does allow array functions the use of sum, sumproduct and CSE formulaes the functions that it recognizes are below.


is there any way to adapt the formulue so that it does the following


1) checks to see how many items in the search criteria and recognise if its searching gor 1,2,3 or all criteria.


(ie if GENDER M is selected return true for all items that has M for 1 criteria search, if GENDER M and Status 1 is selected return true against all items that has GENDER M and Status 1, if GENDER M Status 1 Grade K then return true against all items of 3 criteria search etc etc)


2) return a true or false in the lookup column only for items to indicate a match the criteria


**

EXCEL functions regonised by Xcelsius 4.5 - have already tried the lookup, and sumproduct functions and CSE array forumales and these dont work with xcelsius.


**


ABS, ACOS, ACOSH, AND, ASIN, ASINH, ATAN, ATAN2, ATANH, AVEDEV, AVERAGE, AVERAGEA, CEILING, CHOOSE, COMBIN, CONCATENATE, COS, COSH, COUNT, COUNTA, COUNTIF, DATE, DATEVALUE, DAVERAGE, DAY, DAYS360, DB, DCOUNT, DCOUNTA, DDB, DEGREES, DEVSQ, DGET, DMAX, DMIN, DOLLAR, DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR, DVARP, EDATE, EOMONTH, EQUALS, EVEN, EXP, EXPONDIST, FACT, FALSE, FISHER, FISHERINV, FIXED, FLOOR, FORECAST, FV, GEOMEAN, HARMEAN, HLOOKUP, HOUR, IF, INDEX, INT, INTERCEPT, IPMT, IRR, ISBLANK, KURT, LARGE, LN, LOG, LOG10, MATCH, MAX, MEDIAN, MIN, MINUTE, MIRR, MOD, MODE, MONTH, NETWORKDAYS, NORMDIST, NORMINV, NORMDIST, NORMSINV, NOT, NOW, NPER, NPV, ODD, OR, PI, PMT, POWER, PPMT, PRODUCT, PV, RADIANS, RAND, RATE, ISBLANK, ROUND, ROUNDDOWN, ROUNDUP, SECOND, SIGN, SIN, SINH, SLN, SMALL, SQRT, STANDARDIZE, STDEV, SUM, SUMIF, SUMPRODUCT, SUMSQ, SUMX2MY2, SUMX2PY2, SUMXMY2, SYD, TAN, TANH, TEXT, TIME, TIMEVALUE, TODAY, TRUE, TRUNC, VALUE, VAR, VDB, VLOOKUP, WEEKDAY, WEEKNUM, WORKDAY, YEAR, YEARFRAC, ,
 
Hi Pop,


Just a blind shot.. :)

In A3 write formula as

Code: 
=IF(CONCATENATE($J$3,$K$3,$L$3)=CONCATENATE(E3,F3,G3),1,0) 


and in J5 write =SUM(A3:A85)


as I got info that.. Concatenate & Sum both works in XCELSIOUS.. and I know.. you know better, how to use these functions.. :)


https://scn.sap.com/docs/DOC-25292

http://xcelsius.excelyogi.com/supported.html


Regards,

Deb
 
Hi deb,


thanks for the reply,


unfortunately this only works if its a 3 criteria search and i am expecting searches that range from 1,2,3 4 and any combination of cells
 
Hi ,


You have listed some functions as follows :

[pre]
Code: 
ABS, ACOS, ACOSH, AND, ASIN, ASINH, ATAN, ATAN2, ATANH, AVEDEV, AVERAGE, AVERAGEA, CEILING, CHOOSE,
COMBIN, CONCATENATE, COS, COSH, COUNT, COUNTA, COUNTIF, DATE, DATEVALUE, DAVERAGE, DAY, DAYS360, DB,
DCOUNT, DCOUNTA, DDB, DEGREES, DEVSQ, DGET, DMAX, DMIN, DOLLAR, DPRODUCT, DSTDEV, DSTDEVP, DSUM,
DVAR, DVARP, EDATE, EOMONTH, EQUALS, EVEN, EXP, EXPONDIST, FACT, FALSE, FISHER, FISHERINV, FIXED,
FLOOR, FORECAST, FV, GEOMEAN, HARMEAN, HLOOKUP, HOUR, IF, INDEX, INT, INTERCEPT, IPMT, IRR, ISBLANK,
KURT, LARGE, LN, LOG, LOG10, MATCH, MAX, MEDIAN, MIN, MINUTE, MIRR, MOD, MODE, MONTH, NETWORKDAYS,
NORMDIST, NORMINV, NORMDIST, NORMSINV, NOT, NOW, NPER, NPV, ODD, OR, PI, PMT, POWER, PPMT, PRODUCT,
PV, RADIANS, RAND, RATE, ISBLANK, ROUND, ROUNDDOWN, ROUNDUP, SECOND, SIGN, SIN, SINH, SLN, SMALL,
SQRT, STANDARDIZE, STDEV, SUM, SUMIF, SUMPRODUCT, SUMSQ, SUMX2MY2, SUMX2PY2, SUMXMY2, SYD, TAN,
TANH, TEXT, TIME, TIMEVALUE, TODAY, TRUE, TRUNC, VALUE, VAR, VDB, VLOOKUP, WEEKDAY, WEEKNUM,
WORKDAY, YEAR, YEARFRAC
[/pre]
Is this a list of functions which work with XCelsius or a list of functions which do not work with XCelsius ?


You said SUMPRODUCT does not work with XCelsius ; is this correct ?


Narayan
 
Hi NARAYANK991


Thanks for your reply.


The list is a list of functions that DO work with Xcelsius.


But the
Code: 
SUMPRODUCT , SUM , VLOOKUP , HLOOKUP
functions when used for arrays do not return dynamic results in Xcelsius which is what i am trying to achieve - the use of these functions generates a display error and Xcelsius does not recognise the results generated.


They probably will work for non array formula i guess.
 
I have never used Xcelcius so these comments may be way off mark


When you say they "Don't work with Xcelcius" do you mean that Xcelcius can't work directly on cells with those formulas in or they don't work if anywhere on a worksheet?


If it is the former, do you calculations somewhere and then link to those cells somewhere else using simple =A1 style formulas. Then use Xcelcius on those cells.
 
Hi Hui


Xcelsius produces a dashboard that is driven by an imported excel worksheet - it recognises some but not all of excels functions i am discovering that in this instance when trying to work with arrays Xcelsius does not recognise the calculations generated by these formulaes (ie the sumproduct sum vlookup and hlookup) or those that require CSE it forces reliance on match and index to give the impression of dynamic results which is what i am trying to achieve.


My problem is that I am trying to figure a formula that can match between 1 and 4 criteria and any combination between and then extract the matched records.


Hope this helps explain the constraints.
 
Hi ,


I am not sure that your direction is proper ; instead of asking an XCelsius related question in an Excel forum , you might do better to ask this same question in an XCelsius forum , since XCelsius is intimately connected with Excel. Those who are XCelsius experts will also be familiar with Excel , but not the other way round.


Just BTW , have you gone through the following links ?


1. http://everythingxcelsius.com/


2. http://xcelsius.wordpress.com/


3. http://www.youtube.com/watch?v=JG_UY5Y5QOw


4. http://datapigtechnologies.com/blog/index.php/integrating-xcelsius-with-excel-and-access/


Narayan
 
Hi NARAYANK991


Thank you for the reply and the links which you have suggested. I had checked some of these already for solutions but have yet to find one.


I think the logic of the problem is fairly straightforward and if I could figure out the solution to the comparing and matching of the search criteria I think this will resolve this.


This is what i think the logic of the problem is


1. Identify the number of items in the search criteria (in this case 1-4).

2. Compare fields of search criteria with fields in data table

3. Return True (and relative location in the table) if match is found

4. Extract matched items (Lookup relative locations of matched records)


1. Identifying the number of items in the search criteria (in this case 1-4).


If I have calculated correctly there are 16 combinations that can be selected from 4 criteria.


1 combination with 4 criteria selected

4 combinations with 3 criteria selected

** 6 combinations with 2 criteria selected **

4 combinations where 1 criteria is selected


=IF($O$3=2,OR(AND(E3=$J$3,F3=$K$3),AND(E3=$J$3,G3=$L$3),AND(E3=$J$3,G3=$L$3),AND(F3=$K$3,G3=$L$3),AND(F3=$K$3,H3=$M$3),AND(G3=$L$3,H3=$M$3))+B2)


I am testing for the number of criteria selected by using "=IF($O$3=2”


In this case it is 2 and then I am testing which of the 6 combinations are true


"OR(AND(E3=$J$3,F3=$K$3),AND(E3=$J$3,G3=$L$3),AND(E3=$J$3,G3=$L$3),AND(F3=$K$3,G3=$L$3),AND(F3=$K$3,H3=$M$3),AND(G3=$L$3,H3=$M$3))”


before indexing its relative position in the table if it’s a match


"+B2)”


The problem is that this formula is not consistently returning the right results on all of the 2 criteria combinations for some reason So I am suspecting there is a problem in how the and or has been constructed or is working.


I have illustrated where the formula is picking up the wrong results in the uploaded spreadsheet.


Any help in reviewing amending this formula would be greatly appreciated.


https://www.dropbox.com/s/zrjf0wjj77ligf5/SampleData%209703a.xls
 
Hi NARAYANK991


I did check the file and it does achieve the outcome i was seeking. It would be helpful if you could advise me of how it works. I am still puzzled about how the OR(AND()) function was working and is supposed to work.


Have I missed understeed that OR(AND(1)..(AND(6)) should pick up any 1 of the six combinations that are true and return TRUE???
 
Hi ,


What I have introduced is checks to see which of the criteria are not blank ; only those which are not blank should be used , two at a time e.g. if J3 and K3 are not blank , use them ; else if J3 and L3 are not blank use them , else if J3 and M3 are not blank , use them , and so on for the remaining combinations of 2 out of 4.


Other than this inclusion , everything is as you originally framed it.


Narayan
 
Hi NARAYANK991,


Thanks to you for your explanation and everyone for the assistance and the help in

resolving this.
 
Hi all,


I have been trying to do the same thing as pop and came up with a different solution using a 2 stage calculation with DSUM. I am however eager to find out any other solutions to this issue and was wondering if NARAYANK991 could post the solution file again as I can't get access to it.
 
Hi Oukofae ,


The file is still available at the DropBox link posted earlier. I didn't have any problem downloading it using that link.


Narayan
 
I think it may be my company firewall blocking access to it. I will try to access it when I eventually get home tonight.


Cheers

Oukofae
 
You must log in or register to reply here.
Back
Top