• 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.

Long row formula

S

sms2luv

Member
Hi all,

I remove attendance data from my intranet site for the production floor.

The data has data, Emp I'd and some other info.
Starting from column A to Y.

The data is small at start of the month, but increases as the month end.

So I will count the rows, its sometimes 3000 and at the end of the month it becomes around 13000.

I have used Vlookup to get name from Emp I'd.
I have used text formula to get Week day, Week num, Month, Month num, based on date.

So I have copied the formula from row 2 till row 20000(for safer side in case the data may increase)

This has increased the size of the entire work book.

So my question is whether adding too many formula takes more space?

The raw dump size is just 800kb but after adding formulas the size comes to 3.5 mb in binary format.

Is there any alternatives that will paste the formulas only till the last used cell.
 
If you convert your data to Excel table format, then the formulae should populate down each time you add a new row. It depends very much on the nature of the formulae, too: volatile functions such as INDIRECT and heavy array formulae, if overused, can slow things down dramatically.
 
If you upload a file I can provide a vba on change solution if you don't want to convert your data to a table.
 
Hi ,

Can you explain what is the further processing that is done on the data in this workbook ?

What is the purpose of inserting the formulae in columns T through AF , alongside the data ?

Narayan
 
I create a pivot for this sheet.
Purpose of inserting formula till AF is that the rows are now limited, but as the data increases the row count will also increase
To avoid copy pasting formula I am doing this
 
Wanted to know, if I use Vlookup with For loop, then I would save on the file size.
Because I am not writing formula in each and every cell.
Could you please help.
 
Instead of each row why not using summary for single employee of particular day. Look attach file summary sheet.
 

Attachments

  • Shrink.xlsx
    184 KB · Views: 5
Well, i guess this will not do the work of a pivot.
We need leave count, absent count, Present count
Then we need shrinkage %(leave+absent)/(leave+present+present)
Then we need day by day shrinkage
Manager wise
Date wise
Emp wise
Segment wise
Highest to Lowest wise
Pl leave, Ul leaves, SL Leaves
So there are several other things which i guess only pivot table can do.
If you will are Present,Leave,Absent,Off.
It has a long formula, which I guess will increase file size.
So I think that we can use Macros to reduce size as formula will not be needed.
 
Check out attached file and see if this works for you. Any edit on Sheet2 till trigger the VBA to replace the formulas in the rows that have data.
 

Attachments

  • ShrinkEdit.xlsm
    91.3 KB · Views: 18
Hi, I didn't understand the concept.
The formula are still there in the sheet, which will increase the size.
Please don't mind, can you tell me what changed here.
 
sms2luv said:
Hi, I didn't understand the concept.
The formula are still there in the sheet, which will increase the size.
Please don't mind, can you tell me what changed here.
Click to expand...

Add some new data and or delete the formulas...
 
OK, I will try this.
I had a question in my mind about excel file size.
For example if I create a new excel file.
1) I add around 100 words in a cell.
2) I added some formula which is also of 100 words.
So will the file size remain the same.
 
Hi, sms2luv!
I'd bet on Excel working on a different unveiled way. And unknown for me.
Regards!
 
Hi ,

Can you explain why you are so critical of the file size ?

A user is generally concerned about recalculation time ; file size , unless it goes beyond a few MB is not a major concern.

What is the maximum size of your file , with all the data in it , that is acceptable ? 3.5 MB is not so big ; how much time does it take to open / close ?

Narayan
 
Last edited:
Hi ,

Definitely it cannot be 35 MB merely from 15000 rows of data over 30 columns.

Can you upload at least a scaled down version having 1000 rows of data and formulae ?

Narayan
 
There are 8 sheets, data goes from Column A to BD.
And till row 48000.
This usually happened if I have legacy data.
 
We have several parameters like.
Quality
Customer Satisfaction
Attendance
Average Handling Time
Aux Report.
Login/Logout
Calls in & out data
Service level.

I checked the file, really make good sense that I don't have to paste the formulas anymore.

So, once we put the data, it will automatically put the formula in the cells.
I was thinking rather then putting the formula in cells via VBA, don't you think that it would be better if we directly add it as Value.
This will infact makes the file run faster.
In case of using formula, excel will calculate the formula every time.
Please suggest.
 
The file worked fine.
I added more data and it copied the formula till the last used row.
I deleted some values from some rows and got run time error, after I added some more data and it was not pasting the formula automatically.
 
You must log in or register to reply here.
Back
Top