+ Reply to Thread
Results 1 to 11 of 11

Calculation takes long time

  1. #1
    Registered User
    Join Date
    09-24-2011
    Location
    albanien
    MS-Off Ver
    Excel 2010
    Posts
    5

    Calculation takes long time

    Hello to everyone.
    First question: The values in column E: E based on the values of column D: D. How can that value in E:E with ”Data Validation” to be no less than the previous value? (for example: Golf 1 to have a value greater than the previous value, Golf 2 to have a value greater than the previous value…)
    The second question: In column I2:I20 I set the formula “{=IF(ISERROR(IF(OR(D2="",H2=""),"",LARGE(IF($D$1:$D2=D2,$H$1:$H2),1))-IF(IF(ISERROR(LARGE(IF($D$1:$D2=D2,$H$1:$H2),2)),"",LARGE(IF($D$1:$D2=D2,$H$1:$H2),2))=0,"",IF(ISERROR(LARGE(IF($D$1:$D2=D2,$H$1:$H2),2)),"",LARGE(IF($D$1:$D2=D2,$H$1:$H2),2)))),"",IF(OR(D2="",H2=""),"",LARGE(IF($D$1:$D2=D2,$H$1:$H2),1))-IF(IF(ISERROR(LARGE(IF($D$1:$D2=D2,$H$1:$H2),2)),"",LARGE(IF($D$1:$D2=D2,$H$1:$H2),2))=0,"",IF(ISERROR(LARGE(IF($D$1:$D2=D2,$H$1:$H2),2)),"",LARGE(IF($D$1:$D2=D2,$H$1:$H2),2))))} and works wonders. But if the copy that formula to the end I2:I1048576 then opening Excel and calculation takes 20 to 30 min.

    How can I eliminate this?
    Attached Files Attached Files
    Last edited by dorend; 12-13-2011 at 06:07 PM.

  2. #2
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Calculation takes long time

    Try this formula instead:
    =IF(OR(H2="",D2="",COUNTIF($D$1:D1,D2)=0),"",H2-LOOKUP(2,1/($D$1:D1=D2),$H$1:H1))
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Calculation takes long time

    Also, you're telling excel to calculate over 1 million cells. It will probably take awhile regardless of what the formula is. I would instead recommend only pasting the formula down to row 5,000 or 10,000. If you are going to need more rows than that, you should probably look into using Access instead of Excel

  4. #4
    Registered User
    Join Date
    09-24-2011
    Location
    albanien
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Calculation takes long time

    Thank you very much.

    Any idea to the first question.

  5. #5
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Calculation takes long time

    dorend,

    Quote Originally Posted by dorend View Post
    First question: The values in column E: E based on the values of column D: D. How can that value in E:E with ”Data Validation” to be no less than the previous value? (for example: Golf 1 to have a value greater than the previous value, Golf 2 to have a value greater than the previous value…)

    I'm afraid I don't actually understand what you're asking for here. In your example workbook, column E is not a validation list. Also, column D doesn't contain numbers, it contains a text string that has a number at the end of, like "Golf 1". When you say "to have a value greater than the previous value", what does that mean?

  6. #6
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Calculation takes long time

    Oh, I think I get what you mean. In cell E2, for data validation:
    Allow: Whole number
    Data: greater than
    Minimum: =IF(COUNTIF($D$1:$D1,$D2)=0,0,LOOKUP(2,1/($D$1:$D1=$D2),$E$1:$E1))

    Then copy cell E2, select the range you want to paste the data validation settings to (so something like E2:E5000) and then right-click -> Paste Special -> Select "Validation" -> OK

    That will require that a number is entered into column E that is greater than the previous number entered in column E for the same vehicle selected in column D.

  7. #7
    Registered User
    Join Date
    09-24-2011
    Location
    albanien
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Calculation takes long time

    Each new value (Km for each auto) to be greater than previous, to prevent the introduction of smaller value with Data Validation.
    Sorry for my bad english

  8. #8
    Registered User
    Join Date
    09-24-2011
    Location
    albanien
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Calculation takes long time

    Small clarification
    I think not for values in column E, but values in column H

  9. #9
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Calculation takes long time

    So instead of cell E2, make it cell H2, and change the Minimum formula to:
    =IF(COUNTIF($D$1:$D1,$D2)=0,0,LOOKUP(2,1/($D$1:$D1=$D2),$H$1:$H1))

  10. #10
    Registered User
    Join Date
    09-24-2011
    Location
    albanien
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Calculation takes long time

    Thank you.
    It helped me very much.

  11. #11
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Calculation takes long time

    You're very welcome

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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