Thread: 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(ISERR OR(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(ISERR OR(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?

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))

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

dorend,

Originally Posted by dorend

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?

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.

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))

You're very welcome