Hello,
What would be the alternative to the following as an Excel Function?
#Scores is a matrix of 20 x 3
for i in range(scores):
for j in range(scores):
if all(scores[j] >= scores[i]) and any(scores[j] > scores[i]):
#if true then return 0, if false then return 1
What are you trying to calculate?
Also, why are you using any and all?
Both those functions work with multiple values and you only seem to be comparing single values on each iteration.
If posting code please use code tags, see here.
I am trying to calculate the minimum values in 3 columns simulateously.
I am using any and all in my python script. any and all take iterables and return True if any and all (respectively) of the elements are True. So, in the first condition I check if all three column values meet the condition and then additionally i check if any of the column meets the condition as well.
A boolean isn't iterable.
yeah i know, which is why I am asking how to do it in Excel.
I wrote this formula but i am not getting the same output from it. Please see the attached file
What would be the expected result with the data in the workbook?
I have reattached the file with additional column as Expected values (which I want, as they are correct from my Python script)
What are the 'conditions' you mention in post #3?
I am sorry, what do you mean by Post #3 conditions?
I think yes. Could you describe what do you try to get and why. Not all of us use Python and this string if all(scores[j] >= scores[i]) and any(scores[j] > scores[i]): must be explained.Originally Posted by fadiu2
Hi, I had already explained above what this loop is doing, so to explain again, the first part of the loop all(scores[j] >= scores[i]) checks if selected Row has any values greater or equal in the entire column, this condition is true if all three columns have greater values than the selected row.
any(scores[j] > scores[i]): is true if any of the column has a value greater than the selected Row. For example
1 2 3 are the selected row values in each column, the loop first checks if there are any values lower than these in each of their respective columns. If all has greater then output is True and then similarly second part 1 2 3 if any of these values have a value greater than it in their respective columns, the condition is true.
If you look at the attached excel sheet, it has formula i formulated, but it needs some fixing
I'm with the others, having trouble understanding (or replicating) your expected results. Here's what I've done (remember, at this stage, I am trying to understand how you get the expected results, so this is not a final solution).
1) You talk about a "selected row", so I simulate a selected row with an INDEX() function.
1a) In I35 (so it is in the middle of the table and I can scroll up and down and still see the selected row), I enter my index number (1 for now).
1b) In J35, I enter =INDEX(A$2:A$70,$I35) [note the mix of relative and absolute references for easy copying]. Copy into K35:L35.
1c) J35:L35 now contains my "selected row"
2) Then you claim that your "all()" condition is looking for rows where all 3 values are greater than or equal the 3 selected values. In F2, I enter =AND($A2>=$J$35,$B2>=$K$35,$C2>=$L$35) (copy down to F70). This should return true if all of the values are greater, and, if I've understood correctly, should accurately represent your all() function.
3) Then you claim that your "any()" condition is looking for rows where any one of the 3 values is greater than the selected values in J35:L35. I copy F2 into G2, and edit it to remove the = signs and change from AND to OR =OR($A2>$J$35,$B2>$K$35,$C2>$L$35). Copy down to G70.
4) Now, it seems like I should be looking for rows where both Fi and Gi are TRUE, so I enter =AND(F2:G2) into H2 and copy down to H70.
5) If all of the values in column H are FALSE, then this point (in I35) does not get flagged. If one or more values in column H are TRUE, then this point gets flagged. I can do this with a COUNTIFS() function in M35 =COUNTIFS(H2:H70,TRUE)
That's how I've interpreted your description of the logic, but it does not give the same results as your expected values in column E. Example rows where my logic gets a different result than your expected:
3(spreadsheet row 4) I don't find any rows that meet the two criteria
5(6) I find 3 rows that meet the criteria
10(11) I don't find any rows that meet both criteria
11(12) I find 6 rows that meet the criteria.
I must be misunderstanding, but I'm not sure where I'm going wrong. Why did you flag row #3, but did not flag row #5?
Last edited by MrShorty; 08-04-2020 at 01:27 PM.
Have a look at the attached file.
I've added formulas in each row to count the no of values in each column that are greater than the value in the same column in the current row.
I've also added formula to check if all the values in the row are greater than all the values in the rest of the column and to check if any of the values in the row are greater than all the values in the rest of the column.
As you can see no rows satisfy the former condition but 2 satisfy the latter.
Is this any where near what you are trying to do or am I completely missing the whole thing.
P.S. If you were doing this in Python wouldn't you be looping through the rows and then for each row looping through the values in the columns of that row to compare them to the rest of the values in the column.
Kiind of like this.
Please Login or Register to view this content.
For my not clear how you have get expected value and oe it could be true the condition
all(scores[j] >= scores[i]) and any(scores[j] > scores[i]) . OR in stand of AND is ok for me but not AND.!
First con can be calculated my array and volatile formula
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks