Shorten a calculation?

[melodramatic]

I've got a problem where my worksheet will only allow me to paste my calculation so many times, and then it will not paste it anymore. I'm hoping that maybe, by shortening the calculation somehow (to where it does the same function), my worksheets will be happier and let me take it down as far as I need.

I've got approximately 10,000 rows of data on a sheet called "Data". This data is strictly numeric, run through an equipment logger into columns of channels. Each row comprises one minute, and I batch them into cycle numbers (roughly anywhere between 1500 and 3000 rows of data per cycle).

On my sheet called "Stats", I have a grid where I mark if a certain channel is malfuncting per each cycle. As a note, there are 5 cycles per file.

My third involved sheet is "Calc." What I'm doing here is telling the system to look at the cycle number for that row (in Col B)

THIS CALCULATION COMES FROM Calcs!R19 (channel 14). The cycle number found in Column B is 71. I want to check the Stats page to see if Channel 4 is malfunctioning during Cycle 1 (all malfunctioning boxes are marked with a "1"). If the channel is not malfunctioning, I want to pull in the value of the corresponding cell on the Data page (Data!R19). If the channel IS malfunctioning, I want to enter the value "0".

=IF((MAX(IF(Stats!$A$7:$A$11=$B19,Stats!R$7:R$11)))=1,0,Data!R19)

The calculation works fine. HOWEVER, I need to do about 16 columns, 10000 rows of this calculation. Right now, it has allowed me to copy the calculation down to row 6124, and then it won't copy anymore.

Will shortening my formula help me be able to carry it further in my worksheet? If so, how do I shorten it?

Thanks!

 

[Dave Patton]

What version of Excel are you using?

There are limits to the number of elements in an Array Formula; search Microsoft for specifics.

You may be able to organize your data, use a formula, and then convert the results to values.

If you post a very concise example, someone may provide a solution.

 

[melodramatic]

I'm using 2003 - sorry, I should have thought of that.

I tried to post the applicable portions of my worksheet, but I just got data text - obviously, there's a trick that I haven't figured out.

 

[Dave Patton]

Look at a message at the top of the list of messages, it provides info on tools to put spreadsheet on forum. You can also search for additional help for such tools.

 

[melodramatic]

I can't download software to the computer here - IT has that shut down after someone let a virus loose in the company.

 

[tusharm]

If I understand the data layout correctly, you should consider using a MS Query result and/or a PivotTable to get the results you want.

I've got a problem where my worksheet will only allow me to paste my calculation so many times, and then it will not paste it anymore. I'm hoping that maybe, by shortening the calculation somehow (to where it does the same function), my worksheets will be happier and let me take it down as far as I need.

I've got approximately 10,000 rows of data on a sheet called "Data". This data is strictly numeric, run through an equipment logger into columns of channels. Each row comprises one minute, and I batch them into cycle numbers (roughly anywhere between 1500 and 3000 rows of data per cycle).

On my sheet called "Stats", I have a grid where I mark if a certain channel is malfuncting per each cycle. As a note, there are 5 cycles per file.

My third involved sheet is "Calc." What I'm doing here is telling the system to look at the cycle number for that row (in Col B)

THIS CALCULATION COMES FROM Calcs!R19 (channel 14). The cycle number found in Column B is 71. I want to check the Stats page to see if Channel 4 is malfunctioning during Cycle 1 (all malfunctioning boxes are marked with a "1"). If the channel is not malfunctioning, I want to pull in the value of the corresponding cell on the Data page (Data!R19). If the channel IS malfunctioning, I want to enter the value "0".

=IF((MAX(IF(Stats!$A$7:$A$11=$B19,Stats!R$7:R$11)))=1,0,Data!R19)

The calculation works fine. HOWEVER, I need to do about 16 columns, 10000 rows of this calculation. Right now, it has allowed me to copy the calculation down to row 6124, and then it won't copy anymore.

Will shortening my formula help me be able to carry it further in my worksheet? If so, how do I shorten it?

Thanks!

 

[melodramatic]

Unfortunately, that's not an option - I have engineers use this file, and the task of training them to go through steps would be daunting. I'll just have to figure something out.

For my current problem, I was able to pull up an older file that had all the calculations for the number I needed (not all of the files cut off at the same place), and place my data there.

Frustrating...

 

[barry houdini]

Perhaps it would be more efficient with a non-array SUMIF, i.e.

=IF(SUMIF(Stats!$A$7:$A$11,$B19,Stats!R$7:R$11),0,Data!R19)

 

[melodramatic]

I tried it, and that didn't work, dangit.

I found a way around it - by creating a new column and then placing a variable (which equals to the five cycle status in a certain order), and then simply telling the calculation that if the number in the column on the left is less than the variable at top, the value is 0, otherwise, the value is the original number from the data.

Thanks for trying to help on this!