Replies

hi Steve, hi Andrew, i got your formulas in my spreadsheet, please look back at the file because your result does not match my result. notice Steve’s formula =IF(COUNTIF(A11:U11,”B”)=21,”All Bs”,IF(COUNTIF(A11:U11,”S”)=21,”all Ss”,COUNTIF(A11:U11,”B”)&” B, “&COUNTIF(A11:U11,”S”)&” S”)) says all B in row10 and Target should be B, row33 is all S and Target is S, row79 is all B and Target is B. rows that has mixture of B and S has Target 0, like in row 84 has 20B and 1S and Target is 0, row 99 has 20B and 1S and Target is 0. and rows with continous all B has Target 0 until next all S comes. and if next all S comes in again, Target is 0. there will always be B,S,B,S,… alternate moving. first all B or first all S will be B or S. otherwise, following all B or following all S remain Target 0. so, first all B or first all S will have Target B or Target S and Target 0 happens when there is mixture of B and S, and when this row has the following all B or all S. if not, i will have to insert two more columns in AI with formula =IF(AH10=0,AI9,AH10) and in column AJ with formula =IF(AI10=AI9,0,AI10) to make my Target. the this formula =IF(COUNTIF(A10:U10,”B”)=21,”B”,IF(COUNTIF(A10:U10,”S”)=21,”S”,0)) is in column AH, column before AI and AJ. better not, 3 columns is far better than 12 columns. Steve, i notice your formula is referenced as ‘top’ maybe that is named formula i will track it down so that i could know how to make it and see the difference in my file memory. and i am not experienced in manipulating formulas, so i start with one formula at a time until i get the last one. there are some other parts in my spreadsheet that need shortcut and i will shrink them later. sorry guys, maybe i should explained above before and THANKS.

hi Steve, hi Andrew, i got your formulas in my spreadsheet, please look back at the file because your result does not match my result. notice Steve’s formula =IF(COUNTIF(A11:U11,”B”)=21,”All Bs”,IF(COUNTIF(A11:U11,”S”)=21,”all Ss”,COUNTIF(A11:U11,”B”)&” B, “&COUNTIF(A11:U11,”S”)&” S”)) says all B in row10 and Target should be B, row33 is all S and Target is S, row79 is all B and Target is B. rows that has mixture of B and S has Target 0, like in row 84 has 20B and 1S and Target is 0, row 99 has 20B and 1S and Target is 0. and rows with continous all B has Target 0 until next all S comes. and if next all S comes in again, Target is 0. there will always be B,S,B,S,… alternate moving. first all B or first all S will be B or S. otherwise, following all B or following all S remain Target 0. so, first all B or first all S will have Target B or Target S and Target 0 happens when there is mixture of B and S, and when this row has the following all B or all S. if not, i will have to insert two more columns in AI with formula =IF(AH10=0,AI9,AH10) and in column AJ with formula =IF(AI10=AI9,0,AI10) to make my Target. the this formula =IF(COUNTIF(A10:U10,”B”)=21,”B”,IF(COUNTIF(A10:U10,”S”)=21,”S”,0)) is in column AH, column before AI and AJ. better not, 3 columns is far better than 12 columns. Steve, i notice your formula is referenced as ‘top’ maybe that is named formula i will track it down so that i could know how to make it and see the difference in my file memory. and i am not experienced in manipulating formulas, so i start with one formula at a time until i get the last one. there are some other parts in my spreadsheet that need shortcut and i will shrink them later. sorry guys, maybe i should explained above before and THANKS.

hi John, hi jscher2000, i just notice the speed selection to 4x or lower if there is in the speed selection box, i forgot if there is any and i just burnt one time cd-rw so far with no error with cautious on speed selection, i remember i pulled it down to lower speed and see how it goes for following cd-rw burning. i just burn cd-rw and re-read them quite maybe 3-5 times a week and after this i just use it for burn and re-read in cd-rom over pc. i will check the setting later and the technical stuffs i will consider last. bye. and THANKS.

hi John, hi jscher2000, i just notice the speed selection to 4x or lower if there is in the speed selection box, i forgot if there is any and i just burnt one time cd-rw so far with no error with cautious on speed selection, i remember i pulled it down to lower speed and see how it goes for following cd-rw burning. i just burn cd-rw and re-read them quite maybe 3-5 times a week and after this i just use it for burn and re-read in cd-rom over pc. i will check the setting later and the technical stuffs i will consider last. bye. and THANKS.

hi Steve, THANKS a lot for your many suggestions on reducing file memory. my 4 files now can improve its memory to about 77k KB and i shrink them into two files now and that is a lot for me, around 80% of my data are merely copied down calculations from row 10 until row 4461 and increasing rows of new data everyday. now i use the copy-paste speacial- values for above formulas and leave a number of rows below with formulas to update necessary new data to generate new result. around 70% of the copied formulas calculations use IF function, others use a mixture of formulas, some pivot charts and pivot tables. i think maybe the named formula comment can be use somewhere i just have to pick up the theory and practice. what do you mean by IFs are ‘memory hogs’? are array formulas are the one that must be entered with Ctrl+Shift+Enter and a formula like =MIN(A2:A6000) is not an array, isn’t it? i don’t understand the ‘ built-in (non-array) functions (even mega-formulas) are faster than user-defined functions ‘ but i think built-in functions are those in list in excel by default and mega formulas are long list of functions combination and udf is new function created in vba i will check more later. all my formulas obtained from the excel function list. and i haven’t ponder upon pivot memory improvement. Thanks and bye.

hi Steve, THANKS a lot for your many suggestions on reducing file memory. my 4 files now can improve its memory to about 77k KB and i shrink them into two files now and that is a lot for me, around 80% of my data are merely copied down calculations from row 10 until row 4461 and increasing rows of new data everyday. now i use the copy-paste speacial- values for above formulas and leave a number of rows below with formulas to update necessary new data to generate new result. around 70% of the copied formulas calculations use IF function, others use a mixture of formulas, some pivot charts and pivot tables. i think maybe the named formula comment can be use somewhere i just have to pick up the theory and practice. what do you mean by IFs are ‘memory hogs’? are array formulas are the one that must be entered with Ctrl+Shift+Enter and a formula like =MIN(A2:A6000) is not an array, isn’t it? i don’t understand the ‘ built-in (non-array) functions (even mega-formulas) are faster than user-defined functions ‘ but i think built-in functions are those in list in excel by default and mega formulas are long list of functions combination and udf is new function created in vba i will check more later. all my formulas obtained from the excel function list. and i haven’t ponder upon pivot memory improvement. Thanks and bye.

hi Jim and hi bigaldoc, so far so good, i will have more time checking the monitor while startup.

hi Jim and hi bigaldoc, so far so good, i will have more time checking the monitor while startup.

hi John, i can answer some of your questions, i use Windows XP Professional, no internet so no hot fixes update, Nero version 5.5.10.9 bundled, pc assembled by salesman, computer setup you means motherboard? it is Asus P4V533-MX-UAYZ, LITEON model LXR- 40243A , i have installed around one year ago, no problem burning cd-rw disks and re-read cd-rw disks sucessfully until maybe 3 months ago. recent i burn cd-rw so the problem is on cd-rw and it was okay when i burn cd-r disk some while back. while i can burn, it is okay, when it jam, i have to reset the pc. where is the cause and how to fix it?

hi John, i can answer some of your questions, i use Windows XP Professional, no internet so no hot fixes update, Nero version 5.5.10.9 bundled, pc assembled by salesman, computer setup you means motherboard? it is Asus P4V533-MX-UAYZ, LITEON model LXR- 40243A , i have installed around one year ago, no problem burning cd-rw disks and re-read cd-rw disks sucessfully until maybe 3 months ago. recent i burn cd-rw so the problem is on cd-rw and it was okay when i burn cd-r disk some while back. while i can burn, it is okay, when it jam, i have to reset the pc. where is the cause and how to fix it?

hi Steve, no, i am after the sum of numbers of B and the sum of numbers of S. the sum source must be at selected cells. destination of sum B numbers in one column and destination of sum S numbers in another column. THANKS for reply.

hi Steve, no, i am after the sum of numbers of B and the sum of numbers of S. the sum source must be at selected cells. destination of sum B numbers in one column and destination of sum S numbers in another column. THANKS for reply.

hi Jim Helfer, i did what you mentioned and will monitor if this works. i use Asus P4V533-MX-UAYZ motherboard, Windows XP Professional and the pc is assembled by salesman. THANK YOU for the suggestion.

hi Jim Helfer, i did what you mentioned and will monitor if this works. i use Asus P4V533-MX-UAYZ motherboard, Windows XP Professional and the pc is assembled by salesman. THANK YOU for the suggestion.

hi Rudi, your formula can fit my calculation at selected cell. THANK YOU VERY MUCH.