+ Reply to Thread
Results 1 to 41 of 41

Replace array formula with regular formula

  1. #1
    Registered User
    Join Date
    07-13-2018
    Location
    hanoi
    MS-Off Ver
    2010
    Posts
    24

    Replace array formula with regular formula

    Date Invoice
    16/04/1900 943
    24/10/1900 441
    31/10/1900 872
    02/05/1901 338
    05/07/1900 872
    26/08/1900 129
    27/02/1900 141
    02/01/1901 996
    01/03/1901 86
    22/11/1900 419

    I am trying to identify which invoice number is out of order. The larger invoice number must have larger date. To achieve that, I use a formula to return the largest date of any invoice has lower number than the current one. If that date is larger than the current date then the current invoice number is flagged. The entire column C will be calculated. This is in cell C11.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    My workbook got fairly large and the calculation becomes very slow. Can anyone point me how to replace this with non-array formula? Reorder the table is not an option as many other checks need the table in the original order.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Replace array formula with regular formula

    Why does it need to be the whole column? How many rows of data do you have?

    Your version of Excel will not support the new MAXIFS function, but I don't think it would be any more efficient with whole column references.

    Explain which rows in your sample data are out of order, please.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Replace array formula with regular formula

    You could try this:

    =IF(A2< IF(ISTEXT(A1),0,A1),"Out of Order","")
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  4. #4
    Registered User
    Join Date
    07-13-2018
    Location
    hanoi
    MS-Off Ver
    2010
    Posts
    24

    Re: Replace array formula with regular formula

    Re-sorting is not an option as I have to also summary and analyze the data based on other criteria like location, employees, etc. This is the solution I have to work with. I cannot attach file or post link. So the file's link in the reason for editing.

    I would like to optimize the function either by replacing array formula or some other method.
    Last edited by AliGW; 07-16-2018 at 03:38 AM.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Replace array formula with regular formula

    Attach your sample file here.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Replace array formula with regular formula

    I am confused. There was no re-sorting in my post.

  7. #7
    Registered User
    Join Date
    07-13-2018
    Location
    hanoi
    MS-Off Ver
    2010
    Posts
    24

    Re: Replace array formula with regular formula

    Quote Originally Posted by Glenn Kennedy View Post
    I am confused. There was no re-sorting in my post.
    I think I did not have a very clear example. The second column is the invoice's number, not the amount. The goal is to identify the number that do not follow order. Also, my date format is different than yours so I think that's also make a misunderstanding which lead to your solution. So I just post the file instead.

  8. #8
    Registered User
    Join Date
    07-13-2018
    Location
    hanoi
    MS-Off Ver
    2010
    Posts
    24

    Re: Replace array formula with regular formula

    It says that I cannot post link,files etc. until I post a few times.
    Attached Files Attached Files

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Replace array formula with regular formula

    Yea. Post a sample file, but also include some manually calculated results.

  10. #10
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Replace array formula with regular formula

    Looking at your sample, I would say that rows 7 to 10 are not out of order, but only appear out of order because of the errors in rows 6 and 11.

    Based on that, I would use something like

    =choose(sign(RANK(B3,$B$3:$B$26,1)-RANK(C3,$C$3:$C$26,1))+2,"Invoice number too high","OK","Invoice number too low")

    Does that help?

  11. #11
    Registered User
    Join Date
    07-13-2018
    Location
    hanoi
    MS-Off Ver
    2010
    Posts
    24

    Re: Replace array formula with regular formula

    Glenn: the result in the file is correct and run exactly as intended. It helped me identify all potential problems of invoices numbering. Like I said in my original post, I am looking for ways to improve the performance of the formula.


    Jason: It only "appear out of order" because this example purposely did so. In reality, I have to check all of those invoices to see if any one or all of them are out of order. Imagine that they are hundred or possibly thousand rows apart. Many of the rows in between could have wrong order as well.

    Thanks you for your formula, a very nice approach. (sorry in advance for not clearing this up earlier) I think of a few things that's limit its uses:
    - it may not work properly if more than 1 invoice is issued on the same date (which is the case). The later invoices in that date would be marked as too high even though it's acceptable.
    - the real case I actually have more than 1 criteria, those criteria may or may not be numerical. For example, I would not compare with invoices that's marked as "canceled". I also would not compare a invoice in one group (like "AA") to invoices in a different group (like "AB").
    Last edited by nvu19991; 07-16-2018 at 11:48 AM.

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Replace array formula with regular formula

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  13. #13
    Registered User
    Join Date
    07-13-2018
    Location
    hanoi
    MS-Off Ver
    2010
    Posts
    24

    Re: Replace array formula with regular formula

    Quote Originally Posted by Glenn Kennedy View Post
    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
    I mean the original formula in my file works exactly as intended. I hope to find a better solution than the one I got with limited excel knowledge.

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Replace array formula with regular formula

    Hi. Complete misunderstanding....

    try this instead of yoru MAX-IF array:
    =SUMPRODUCT(MAX(($C$3:$C$26<C3)*$B$3:$B$26))

    or
    =LARGE(INDEX(($C$3:$C$26<C3)*$B$3:$B$26,0),COUNTIF(C:C,C3))

    Does this do it??
    Attached Files Attached Files

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Replace array formula with regular formula

    I checked all 3 formulae. The SUMPRODUCT formula is fastest.

  16. #16
    Registered User
    Join Date
    07-13-2018
    Location
    hanoi
    MS-Off Ver
    2010
    Posts
    24

    Re: Replace array formula with regular formula

    Quote Originally Posted by Glenn Kennedy View Post
    I checked all 3 formulae. The SUMPRODUCT formula is fastest.
    Thanks very much Glenn. SUMPRODUCT is indeed the fastest. However, arrayed-MAX seem to scale best (by very small margin) but it means at larger scale, it could be faster than SUMPRODUCT. I couldn't test further so I put the file here.
    Attached Files Attached Files

  17. #17
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Replace array formula with regular formula

    Your revised sheet and my PC don't like each other. Excel has crashed several times. However, I do see that there's not much difference...

  18. #18
    Registered User
    Join Date
    07-13-2018
    Location
    hanoi
    MS-Off Ver
    2010
    Posts
    24

    Re: Replace array formula with regular formula

    This is a summary. It's by design that time complete for these functions increase exponentially when the number of rows increase. The strange thing is that some increase more than other. Just an observation. Consider the minor different in time, I think probably I need a new approach for this, maybe with some kind of sorting that do not mess up other formulae

    Item SUMPRODUCT MAX LARGE
    Amout Rows 1 1300 1300 1300
    Time/iteration 0.43 0.54 0.64
    Amout Rows 2 4,001 4,001 4,001
    Time/iteration 4.54 4.77 7.37
    Row increase 3.08 3.08 3.08
    Time increase 10.49 8.91 11.50
    Time increase/row increase 3.41 2.89 3.74
    Last edited by nvu19991; 07-17-2018 at 03:35 AM.

  19. #19
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Replace array formula with regular formula

    No. But...

    As it happens, I am about to upgrade my laptop to Excel 2016. I will see if i can get your sheet to run on it. If it does, I will look at MAXIF, a new function for Excel 2016+ It may be significantly faster... or may not! We'll see... Look back later.

  20. #20
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Replace array formula with regular formula

    It's MAXIFS.

    https://support.office.com/en-us/art...b-9b6376b28883

    But the OP only has 2010 ...

  21. #21
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Replace array formula with regular formula

    MAXIFS is not in Excel 2016, you only get it with a 365 subscription (unless I'm missing an update).

  22. #22
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Replace array formula with regular formula

    MAXIF(s)... typo. OP: yes, I realise that. Just interested to see if it's faster...

  23. #23
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Replace array formula with regular formula

    Are you getting the 365 subscription, Glenn? If not, I don't think you will have it.

  24. #24
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Replace array formula with regular formula

    Quote Originally Posted by nvu19991 View Post
    I think of a few things that's limit its uses:
    - it may not work properly if more than 1 invoice is issued on the same date (which is the case). The later invoices in that date would be marked as too high even though it's acceptable.
    - the real case I actually have more than 1 criteria, those criteria may or may not be numerical. For example, I would not compare with invoices that's marked as "canceled". I also would not compare a invoice in one group (like "AA") to invoices in a different group (like "AB").
    Could you provide a sample based on this as well, suggestions that we provide without these criteria might not work with.

    You said in your first post that you cannot resort the data, but would it be permissable to sort for finding the invoices that are out of order, then sort back? Assuming that 'No' in column A is always ascending sequentially.

  25. #25
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Replace array formula with regular formula

    in this case the below formula will work
    D3=IFERROR(LOOKUP(2,1/(C$3:C$4003<C3),B$3:B$4003),0)
    Try this and copy towards down
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  26. #26
    Registered User
    Join Date
    07-13-2018
    Location
    hanoi
    MS-Off Ver
    2010
    Posts
    24

    Re: Replace array formula with regular formula

    Quote Originally Posted by AliGW View Post
    Are you getting the 365 subscription, Glenn? If not, I don't think you will have it.
    I thought Excel only sell premium features like sharing, collaboration, cloud thingy. Now they actually sell functions and call it features :|. And that MAXIFS function is easily replicated with a nested array function like mine. Let's hope that's worthwhile, Glenn will have the answer.
    Last edited by nvu19991; 07-17-2018 at 05:44 AM.

  27. #27
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Replace array formula with regular formula

    Microsoft's activation servers are down!! Would you believe it????

  28. #28
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Replace array formula with regular formula

    Luck of the Irish ... NOT!!!

    Murphy's Law?

  29. #29
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Replace array formula with regular formula

    fwiw, I have 2016 desktop and maxifs (and a few others) is not included
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  30. #30
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Replace array formula with regular formula

    No, they are only in the subscription version, unfortunately.

  31. #31
    Registered User
    Join Date
    07-13-2018
    Location
    hanoi
    MS-Off Ver
    2010
    Posts
    24

    Re: Replace array formula with regular formula

    Quote Originally Posted by jason.b75 View Post
    Could you provide a sample based on this as well, suggestions that we provide without these criteria might not work with.

    You said in your first post that you cannot resort the data, but would it be permissable to sort for finding the invoices that are out of order, then sort back? Assuming that 'No' in column A is always ascending sequentially.
    This is the file with real conditions, there are other columns not included here. Resorting the data would mean I have to redo almost from scratch many other reports. That would be report by locations, products, customers, etc (they all need original sorting for speed optimization). That would take the whole month to redo,test,approve,implement.

    Sort and re-sort is quite adventurous because other function also heavily rely on that original order. In my actual file, the data is contained in table, not so sure if functions in table will handle that well. I have to test it well before I can answer if that's possible.
    Attached Files Attached Files
    Last edited by nvu19991; 07-17-2018 at 11:19 PM.

  32. #32
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Replace array formula with regular formula

    It strikes me that you might be overcomplicating things....

    Using your criteria invoices 6 to 9 are OoO. Are they? They're in sequence and in their correct place in the overall sequence. I cut down your sample abit and have highlighted those value which I would consider OoO.

    Then, either this:
    =IF(A3<>C3, "Out of Order","OK")

    or this (uglier):
    =IF(AND(C3<>IF(ISTEXT(C2),0,C2)+1,C3<>C4-1), "Out of Order","OK")

    or this:
    =IF(COUNTA($C$2:C2)<>C3, "Out of Order","OK")


    deliver the goods.
    Attached Files Attached Files

  33. #33
    Registered User
    Join Date
    07-13-2018
    Location
    hanoi
    MS-Off Ver
    2010
    Posts
    24

    Re: Replace array formula with regular formula

    @Glenn: My sample I generate is in sequence but in real case, it's not. Imagine that it's a list collected from different location. All of which would have their data in their date sequence from beginning of the month to the end, several times. However, even if they are from different location, their numbering still have to be in order with all other locations regarding the date of issuance. So I would have invoice 1,2,3, 5,10 at the beginning of the data and 4,6,7,8 near the end of the data. All that invoices would have about the same date.
    Last edited by nvu19991; 07-18-2018 at 04:14 AM.

  34. #34
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Replace array formula with regular formula

    Grouping = location in your sample?

    The way I read your post is that invoice numbers should fall in order by date regardless of location, which, unless I'm missing something? Would make the ($E$3:$E$32=E3) part of your MAX(IF( array redundant.

    edit:-

    What volume of data are we looking at with the real workbook?

    2k rows, 10k?
    1 month of data, or 1 year?

    Your sample file is not a very good indication of what your real file might contain, among many other potentially confusing nuances, I doubt that you have invoices in your real file that contain dates from the 32nd to the 99th of January

    One thought is to export to a pivot table showing min and max invoice number by date (filter to exclude cancelled). While it would not show every individual problem invoice number, it might simplify the process by elimination. The content of your sample is too unrealistic to test the theory. 1000 unique, sequential and mostly invalid dates with 1 invoice per day, is not the same as 10 unique, non-sequential valid dates with 100 invoices per day.
    Last edited by jason.b75; 07-18-2018 at 06:53 AM.

  35. #35
    Registered User
    Join Date
    07-13-2018
    Location
    hanoi
    MS-Off Ver
    2010
    Posts
    24

    Re: Replace array formula with regular formula

    Grouping = location in your sample?
    No, grouping is the invoices' grouping. Another group will be use if the previous one is used up.

    The way I read your post is that invoice numbers should fall in order by date regardless of location, which, unless I'm missing something? Would make the ($E$3:$E$32=E3) part of your MAX(IF( array redundant.
    It check the grouping. The function will only get the date of the same grouping. Like I said above, invoices in different grouping do not need to be compared.


    edit:-

    What volume of data are we looking at with the real workbook?

    2k rows, 10k?
    1 month of data, or 1 year?
    It's 1 quarter, about 4K of rows.

    Your sample file is not a very good indication of what your real file might contain, among many other potentially confusing nuances, I doubt that you have invoices in your real file that contain dates from the 32nd to the 99th of January
    It's true that's not a good indication of the real problem. My original intend is to find regular function to replace array function to improve speed. That part is actually solved by Glenn and the result was not a significant improvement. It seems that without changing the structure of the data, different ways of brute forcing will not yield good improvement.

    One thought is to export to a pivot table showing min and max invoice number by date (filter to exclude cancelled). While it would not show every individual problem invoice number, it might simplify the process by elimination.
    Getting/analyzing the result in a different table would also be a viable approach. Thanks for the idea.

  36. #36
    Registered User
    Join Date
    07-13-2018
    Location
    hanoi
    MS-Off Ver
    2010
    Posts
    24

    Re: Replace array formula with regular formula

    Quote Originally Posted by Glenn Kennedy View Post
    It strikes me that you might be overcomplicating things....
    I will try to sort to simplify this. That I have significant simpler function and can use binary lookup. Now I have to consider what will be scarified to improve this operation. This kind of error could lead to government punishment so it must be done. Let's hope that the overall speed of the spreadsheet will improve.

    Also, please inform us of the performance of MAXIFS.
    Last edited by nvu19991; 07-18-2018 at 07:35 AM.

  37. #37
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Replace array formula with regular formula

    Assuming that you understand how to create a basic pivot table, try setting one up with the following parameters.

    Filter:- Status (to deselect cancelled invoices).
    Rows:-Grouping, then Date (typically excel tries to be helpful when you pivot on dates, it might automatically add month / quarter fields as well, if so you will need to remove these).
    Values:- Invoice number (twice, will default to sum, you will need to change 1 to min and the other to max).

    The pivot will automatically sort in date order (even though your dates are not proper format, they are in a format that will still work).
    When looking at the output, OK rows should follow the logic of max invoice no of day 1 being less than min invoice no of day 2, etc.

    Anything that doesn't follow the correct pattern will give you date and grouping to filter the original data and identify the problem(s).

    Not sure how practical this would be for you, but it is how I would do it based on the information you have given us.

  38. #38
    Registered User
    Join Date
    07-13-2018
    Location
    hanoi
    MS-Off Ver
    2010
    Posts
    24

    Re: Replace array formula with regular formula

    Quote Originally Posted by jason.b75 View Post
    Assuming that you understand how to create a basic pivot table, try setting one up with the following parameters.

    Filter:- Status (to deselect cancelled invoices).
    Rows:-Grouping, then Date (typically excel tries to be helpful when you pivot on dates, it might automatically add month / quarter fields as well, if so you will need to remove these).
    Values:- Invoice number (twice, will default to sum, you will need to change 1 to min and the other to max).

    The pivot will automatically sort in date order (even though your dates are not proper format, they are in a format that will still work).
    When looking at the output, OK rows should follow the logic of max invoice no of day 1 being less than min invoice no of day 2, etc.

    Anything that doesn't follow the correct pattern will give you date and grouping to filter the original data and identify the problem(s).

    Not sure how practical this would be for you, but it is how I would do it based on the information you have given us.
    This sure is one good way to solve the problem. In my table, filtering is a PAIN.

  39. #39
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Replace array formula with regular formula

    Another way that 'might' make speed up the formula method slightly (not sure how we all managed to miss this until now)

    Using the sample file from post #31.

    In H3, fill doen to end

    =D3<>"Cancelled"

    In F3, fill down to end (based on Glenn's suggestion of using sumproduct in place of array).

    =IF(D3="Cancelled",0,SUMPRODUCT(MAX(($C$3:$C$1002<C3)*$H$3:$H$1002*($E$3:$E$1002=E3)*$B$3:$B$1002)))

    By using the helper column, the element of the array that checks for cancelled invoices can be reduced from ($D$3:$D$1002<>"Canceled") which requires 1000 calculations, to the single calculation in the helper cell.

    The number of 'calculations' in the formula could be open to interpretation in many ways, in my way of looking at this formula, this slight change should reduce the number of calculations per formula by around 16-17%.

  40. #40
    Registered User
    Join Date
    07-13-2018
    Location
    hanoi
    MS-Off Ver
    2010
    Posts
    24

    Re: Replace array formula with regular formula

    Quote Originally Posted by jason.b75 View Post
    Another way that 'might' make speed up the formula method slightly (not sure how we all managed to miss this until now)

    Using the sample file from post #31.

    In H3, fill doen to end

    =D3<>"Cancelled"

    In F3, fill down to end (based on Glenn's suggestion of using sumproduct in place of array).

    =IF(D3="Cancelled",0,SUMPRODUCT(MAX(($C$3:$C$1002<C3)*$H$3:$H$1002*($E$3:$E$1002=E3)*$B$3:$B$1002)))

    By using the helper column, the element of the array that checks for cancelled invoices can be reduced from ($D$3:$D$1002<>"Canceled") which requires 1000 calculations, to the single calculation in the helper cell.

    The number of 'calculations' in the formula could be open to interpretation in many ways, in my way of looking at this formula, this slight change should reduce the number of calculations per formula by around 16-17%.
    For a test of 1000 rows, the improvement is 10%. It's a good improvement. I will remember next time when having a column has only 2 statuses.

  41. #41
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Replace array formula with regular formula

    Why when a column only has 2 statuses?

    Can you give an example where it will not work with more statuses?

+ 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. Replies: 10
    Last Post: 07-24-2016, 08:28 AM
  2. [SOLVED] Regular formula or array for multi colum multi criteria?
    By scaffdog845 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-25-2016, 01:40 PM
  3. [SOLVED] Excel VBA Replace Formula Array
    By trizzo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-10-2014, 09:02 AM
  4. [SOLVED] Replace ARRAY formula with VBA code
    By MariaPap in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-02-2014, 08:29 AM
  5. [SOLVED] Formula to replace this array
    By cboys00 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-06-2012, 08:47 PM
  6. Replace hard coded array in formula with link to input array
    By David Brown in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-08-2011, 07:45 AM
  7. Replies: 0
    Last Post: 12-16-2005, 01:45 AM

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