+ Reply to Thread
Results 1 to 14 of 14

Excel alternative formula for a Python loop

  1. #1
    Registered User
    Join Date
    07-28-2020
    Location
    Switzerland
    MS-Off Ver
    office 365
    Posts
    12

    Excel alternative formula for a Python loop

    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

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,596

    Re: Excel alternative formula for a Python loop

    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.

  3. #3
    Registered User
    Join Date
    07-28-2020
    Location
    Switzerland
    MS-Off Ver
    office 365
    Posts
    12

    Re: Excel alternative formula for a Python loop

    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.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,596

    Re: Excel alternative formula for a Python loop

    A boolean isn't iterable.

  5. #5
    Registered User
    Join Date
    07-28-2020
    Location
    Switzerland
    MS-Off Ver
    office 365
    Posts
    12

    Re: Excel alternative formula for a Python loop

    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
    Attached Files Attached Files

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,596

    Re: Excel alternative formula for a Python loop

    What would be the expected result with the data in the workbook?

  7. #7
    Registered User
    Join Date
    07-28-2020
    Location
    Switzerland
    MS-Off Ver
    office 365
    Posts
    12

    Re: Excel alternative formula for a Python loop

    I have reattached the file with additional column as Expected values (which I want, as they are correct from my Python script)
    Attached Files Attached Files

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,596

    Re: Excel alternative formula for a Python loop

    What are the 'conditions' you mention in post #3?

  9. #9
    Registered User
    Join Date
    07-28-2020
    Location
    Switzerland
    MS-Off Ver
    office 365
    Posts
    12

    Re: Excel alternative formula for a Python loop

    I am sorry, what do you mean by Post #3 conditions?

  10. #10
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,265

    Re: Excel alternative formula for a Python loop

    Quote Originally Posted by fadiu2 View Post
    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.

  11. #11
    Registered User
    Join Date
    07-28-2020
    Location
    Switzerland
    MS-Off Ver
    office 365
    Posts
    12
    Quote Originally Posted by BMV View Post
    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.
    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

  12. #12
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,566

    Re: Excel alternative formula for a Python loop

    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.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  13. #13
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,596

    Re: Excel alternative formula for a Python loop

    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.
    Attached Files Attached Files

  14. #14
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,265

    Re: Excel alternative formula for a Python loop

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to use python language with excel
    By jorel in forum Excel General
    Replies: 4
    Last Post: 05-14-2018, 03:12 AM
  2. Python or VBA in excel
    By songhaegyo in forum The Water Cooler
    Replies: 49
    Last Post: 01-11-2018, 12:40 PM
  3. python in excel
    By idandush in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-05-2015, 04:26 PM
  4. Can you use (or compile?) Python code in Excel
    By mrvp in forum The Water Cooler
    Replies: 7
    Last Post: 06-08-2014, 06:49 AM
  5. Python commands in Excel
    By ljoseph in forum Excel General
    Replies: 0
    Last Post: 09-18-2009, 07:02 AM
  6. Increase Loop Efficiency/Loop Alternative
    By nsorden in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-19-2009, 09:54 AM
  7. Excel and Python
    By davidlawrence in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-24-2008, 08:42 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1