• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Use of formula in cells where it is needed...

kuldeepjainesl

kuldeepjainesl

Member
Hi Experts,


As of now my workbook takes 5-10 minute to calculate the output data based on the formulas used in sheets. as of now only 2100 rows are filled up with sum product formulas. i am using F9 to trigger calculations.


While input data on sheets may vary from few hundred to several thousand rows (expected up to 20000 rows). imagine if i drag my formulas to 20000 rows, it will take hours to calculate.


I want to know is there a way i can control my workbook to use formulas in rows where the data is present and ignore other. This will save a lot of time.


As if sheet2 (input data) has 15000 rows then only 15000 thousand rows will be taken for calculation in my sum product formula range.


Regards,
 
Kuldeep


Is there anyway you can post a sample of the file with offending cells highlighted :

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Copy/Paste values a Random number like 3.141592654 across the data areas

Obviously don't use Pi or tell us what the number is
 
Hi Hui..


That is not the case.....It is a company confidential that why i needed to fill it up with dummy. I know that without sharing anyone can not expect very much...but to explain it i think it should be a meaningful data as well.


you are requested to downlode the file from http://sdrv.ms/N8IqTZ


Regards,
 
Kuldeep


If I put in Data Processing!E3

Code: 
=COUNTIFS($U$3:$U$10000,E$2,$T$3:$T$10000,">="&$B3,$T$3:$T$10000,"<="&$C3)

Copy across and down

It takes about 9 seconds to calculate

It took about 1 minute originally, but I have a high end PC with lots of memory
 
Hui,


9Sec..Do you have super computer.......


Did you copied the formula down to 10000 rows....and across..to S coloum.


For me its about same speed as it was for sum-product... To fill it with dummy data it took 30 minute.


i have Dual core E5400 with 2GB RAM :(


Regards,

Kuldeep
 
Hi Hui,


Yes it is faster....Antivirus was running on background for my above observation....after stopping that it taken 5 minutes.....Thanks a lot. Now at least i can have a template for 15000 rows which is expected with a expectation to finish in 10 minutes.


But does it means Sum-product is slow....


And what i asked in original is possible by any means....however it does not seems to be required for this...Asking you to just increase my GK.


Regards,

Kuldeep
 
Kuldeep

I copied the formula across and down to fill the same area you mentioned

Column S and 10000+ rows sounds about right


I have a HP quad core i970 something with 16GB ram it's about 1 yr old

This may justify an upgrade
 
Hi All,


I could figure out the difference as SUMPRODUCT version took nearly five times longer than SUMIFS.


Here’s what I found:


When I used 500 SUMIFS formulas, each referencing those 150,000 rows, they took an average of about 2.7 seconds to calculate.


When I used 500 SUMPROUDCT formulas to return the same results, they took about 13.2 seconds to calculate.


Full details at http://exceluser.com/blog/483/excels-sumifs-or-sumproduct-which-is-faster.html


Regards,

Kuldeep
 
You must log in or register to reply here.
Back
Top