ExcelBanter - Excel Worksheet Functions http://www.excelbanter.com (microsoft.public.excel.worksheet. functions) Detailed discussions and queries on Excel worksheet functions en Fri, 10 Sep 2010 03:14:12 GMT vBulletin 60 http://www.excelbanter.com/images/002/misc/rss.jpg ExcelBanter - Excel Worksheet Functions http://www.excelbanter.com Conditional Delete Statement http://www.excelbanter.com/showthread.php?t=265929&goto=newpost Thu, 09 Sep 2010 19:41:37 GMT I want to create a code that can run through the entire Excel spreadsheet and delete the rows of all accounts that have $0 or less Outstanding amt in column K.... I want to create a code that can run through the entire Excel spreadsheet and delete the rows of all accounts that have $0 or less Outstanding amt in column K. Example in the below sample account 31203 started with an outstanding balance of $3000 but had two transactions that followed in Column J resulting in an ending balance of $0. As a result of this all rows of account 31203 should be deleted and the rest of the remaining data should be moved up. All accounts that have Outstanding balances in column K like account # 76292 & 56512 in the example below will Not be deleted because they have balances of $2500 and $3800 respectively. The only Accounts that will remain on the spreadsheet will be account numbers with Outstanding balances in the Outstanding Amt field in column K. Can you please help me create a macro or a VBA code to accomplish this task I am new at this. Sorry I had to present my case this way because when I try to copy and paste it the way I have it in Excel comes out all clumped together in this medium. If you put them side by side you will see what it should look like. Thanks for your help.

A
Account
31203
31203
31203
76292
56512

J
Encumb Amt
Blank field
$1000
$2000
Blank field
Blank field

K
Outstanding Amt
$3000
$2000
$0
$2500
$3800 ]]> Excel Worksheet Functions chrisjack001 http://www.excelbanter.com/showthread.php?t=265929 Conditional Delete Statement http://www.excelbanter.com/showthread.php?t=265928&goto=newpost Thu, 09 Sep 2010 19:36:16 GMT I want to create a code that can run through the entire Excel spreadsheet and delete the rows of all accounts that have $0 or less Outstanding amt in column K.... I want to create a code that can run through the entire Excel spreadsheet and delete the rows of all accounts that have $0 or less Outstanding amt in column K. Example in the below sample account 31203 started with an outstanding balance of $3000 but had two transactions that followed in Column J resulting in an ending balance of $0. As a result of this all rows of account 31203 should be deleted and the rest of the remaining data should be moved up. All accounts that have Outstanding balances in column K like account # 76292 & 56512 in the example below will Not be deleted because they have balances of $2500 and $3800 respectively. The only Accounts that will remain on the spreadsheet will be account numbers with Outstanding balances in the Outstanding Amt field in column K. Can you please help me create a macro or a VBA code to accomplish this task I am new at this. Sorry I had to present my case this way because when I try to copy and paste it the way I have it in Excel comes out all clumped together in this medium. If you put them side by side you will see what it should look like. Thanks for your help.

A
Account
31203
31203
31203
76292
56512

J
Encumb Amt
Blank field
$1000
$2000
Blank field
Blank field

K
Outstanding Amt
$3000
$2000
$0
$2500
$3800 ]]> Excel Worksheet Functions chrisjack001 http://www.excelbanter.com/showthread.php?t=265928 delete row macro http://www.excelbanter.com/showthread.php?t=265905&goto=newpost Tue, 07 Sep 2010 15:48:56 GMT Hello, Can anyone help with a macro to delete a row of data if colomn C (date) is before 1/1/2010. I want to anaylize the data from this year olny. I can do... Hello,

Can anyone help with a macro to delete a row of data if colomn C (date) is before 1/1/2010. I want to anaylize the data from this year olny. I can do an auto format to get rid of the info, but the info gets updated every month and all previous data gets dumped back into the file. I need to stream line it so that if anyone else opens the file they only see this years info.

Thanks in advance for the help.

Terry ]]> Excel Worksheet Functions DeFautT http://www.excelbanter.com/showthread.php?t=265905 <![CDATA[Needs Help "calculate of courier service charge"]]> http://www.excelbanter.com/showthread.php?t=265903&goto=newpost Tue, 07 Sep 2010 12:35:20 GMT Hi needs some help !!!

I am trying to create a very simple formula to calculate the courier shipping charge. " but i am just a nuts and needs help "

i just want the formula to automatically calculate the shipping cost for me each time when i key in the " weight "

The standard formula to calculate the courier shipping charge are in this way.

1) There is a initial minimum charge. first " 0.1kg to 0.5kg " was charge at the price of $24.80. and thereafter for every addintional 0.5kg will be additional charge $5.80 ( Which mean the charger of 1kg is [$24.80 + $5.80= $30.60 )

Any help will be greatly appreciated!

Thanks ! ]]> Excel Worksheet Functions DKTRL http://www.excelbanter.com/showthread.php?t=265903 Distribution calculation http://www.excelbanter.com/showthread.php?t=265898&goto=newpost Mon, 06 Sep 2010 09:22:02 GMT I am trying to reflect a better sense of distributing items at a lower level of granularity using constant growth. Here is my example: If I forecast sales in... I am trying to reflect a better sense of distributing items at a lower level of granularity using constant growth.

Here is my example: If I forecast sales in a given year to be 400 units, how do I best distribute those sales among four quarters? The wrong answer is, obviously, 100 units per quarter. Keep in mind that the total sales for the year must still be 400 units.

I suspect that the answer may be quite simple and my brain is moving slow. And I thank you in advance for the advice. ]]> Excel Worksheet Functions JMSprufrock http://www.excelbanter.com/showthread.php?t=265898 kabbalah http://www.excelbanter.com/showthread.php?t=265889&goto=newpost Sun, 05 Sep 2010 16:50:06 GMT i i am altered (testimony) here like into unknown pepole and stuff have planned a access maturity to i i am altered (testimony) here

like into unknown pepole and stuff

have planned a access maturity to ]]> Excel Worksheet Functions GriepeTield http://www.excelbanter.com/showthread.php?t=265889 Negative fraction power http://www.excelbanter.com/showthread.php?t=265815&goto=newpost Sun, 29 Aug 2010 06:39:55 GMT Hi! Can anyone help me how to properly apply this function =(2/3)*(B2)*(B2^(-2/3)), where B2=-1 I tried this =((2/3)*(B2))*(SIGN(B2)^(-2/3)), but not... Hi! Can anyone help me how to properly apply this function

=(2/3)*(B2)*(B2^(-2/3)), where B2=-1

I tried this =((2/3)*(B2))*(SIGN(B2)^(-2/3)), but not working

Thanks in advance! ! ! ]]> Excel Worksheet Functions renzouken http://www.excelbanter.com/showthread.php?t=265815 Update dates annually http://www.excelbanter.com/showthread.php?t=265814&goto=newpost Sun, 29 Aug 2010 06:37:42 GMT I need to have excel calculate a formula each month from a stationary date which I can do with =date however at the beginning of each new year I need the... I need to have excel calculate a formula each month from a stationary date which I can do with =date however at the beginning of each new year I need the stationary date's year to change. Is there a way to do this? ]]> Excel Worksheet Functions Misty Sunshine http://www.excelbanter.com/showthread.php?t=265814 SumIf/IfStatements: Referencing more them one attribute http://www.excelbanter.com/showthread.php?t=265686&goto=newpost Wed, 25 Aug 2010 02:32:51 GMT Hi all, just wondering if any one can help with my If statement dilemma. I'm creating a stock tracking excel for my company and have run into a issues when writing an if statement which is reference more then on attribute on the worksheet.

So far I have

* Created a input form in which the user can add jobs with how much stock was used on that job. The form allows the user to input a job name and 6 different piles (products) which can vary in length and can be in 2 different designs.
* Created a stock worksheet (Main Page) which will allow the user to change manufactured stock levels and show how much stock has been used form the jobs that have been added. Thus allowing the subtraction of used stock from manufactured stock totalling current stock levels. This worksheet also includes buttons for adding a new job, Saving and Printing the Form.
* Created a Jobs Worksheet which will list all the jobs that have been added, which could include up to 6 piles with different lengths and design.


Confused yet? My dilemma is I have to compile all the piles (Products) from the jobs added and filter them by the different lengths and design then place the Quantity used for each length and design into the relevant cell on a table.

I hope I have not confused anyone to much and would really appreciate any help as I am lost.

If you are still puzzled to what the hell i am talking about please look at the excel workbook attached and make your own conclusions.

Kind Regards
Gary
Attached Files
File Type: zip StockTracker11.zip (33.7 KB)
]]> Excel Worksheet Functions gaznev2000 http://www.excelbanter.com/showthread.php?t=265686 Can the MATCH function search across several worksheets? http://www.excelbanter.com/showthread.php?t=265685&goto=newpost Wed, 25 Aug 2010 00:03:41 GMT I'm on Excel 2003, Windows XP. No VBA, please (beyond my and my co-worker's capabilities).

I have a MATCH function that is working successfully like this:
=IF(ISNUMBER(MATCH(A1,Sheet1!A:A,0)),"Yes","")

But what I'd like to know is the syntax required to query multiple sheets.
For example, I've tried:

=IF(ISNUMBER(MATCH(A1,Sheet1!,Sheet3!,Sheet4!A:A,0 )),"Yes","No")

AND

=IF(ISNUMBER(MATCH(A1,'Sheet1'!A:A,'Sheet3'!A:A,'S heet4'!A:A0)),"Yes","No")

Neither one work. Thus, my quest is: can MATCH be used to search several sheets? If not, please let me know (I've tried searching for an answer to this to no avail).

If so, how do I list the names of those worksheets?

Thank you! ]]> Excel Worksheet Functions michellep http://www.excelbanter.com/showthread.php?t=265685 Can the MATCH function search across several worksheets? http://www.excelbanter.com/showthread.php?t=265684&goto=newpost Wed, 25 Aug 2010 00:02:10 GMT I'm on Excel 2003, Windows XP. No VBA, please (beyond my and my co-worker's capabilities).

I have a MATCH function that is working successfully like this:
=IF(ISNUMBER(MATCH(A1,Sheet1!A:A,0)),"Yes","")

But what I'd like to know is the syntax required to query multiple sheets.
For example, I've tried:

=IF(ISNUMBER(MATCH(A1,Sheet1!,Sheet3!,Sheet4!A:A,0 )),"Yes","No")

AND

=IF(ISNUMBER(MATCH(A1,'Sheet1'!A:A,'Sheet3'!A:A,'S heet4'!A:A0)),"Yes","No")

Neither one work. Thus, my quest is: can MATCH be used to search several sheets? If not, please let me know (I've tried searching for an answer to this to no avail).

If so, how do I list the names of those worksheets?

Thank you! ]]> Excel Worksheet Functions michellep http://www.excelbanter.com/showthread.php?t=265684 Hello, fresh here! http://www.excelbanter.com/showthread.php?t=265683&goto=newpost Tue, 24 Aug 2010 21:13:47 GMT I'm a beginner to this community forum.
I hope you guys help me always if I fall in trouble. And I'm really happy to join with you.
Pls share your valuable tips and tricks with me. ]]> Excel Worksheet Functions HardenMassie http://www.excelbanter.com/showthread.php?t=265683 Complex Sumif Formula http://www.excelbanter.com/showthread.php?t=265677&goto=newpost Tue, 24 Aug 2010 13:55:35 GMT I am creating a weld mapping tool for work that tracks welding errors. I am trying to sum the total errors for any given company ("a", "b", "c", etc.).

The company name will be in cells Piping!L4:L<infinity> where the weld error will be in cell Piping!Q4:Q<infinity> and in Piping!S4:S<infinity>. The column of cells will be designated with a "yes" if the weld is accepted or a "no" if it is rejected. I will need to sum the number of times "no" appears in the database for company "a", company "b", company "c", etc.

The raw data is on worksheet Piping and the sum will appear on worksheet Chart Data in cell G21

I tried using the sumproduct function, but can't seem to get it to work as needed. Either it will sum any entries in cell Piping!Q4:Q<infinity> and in Piping!S4:S<infinity> or will return a zero. Here is an example of what I am trying {=SUMPRODUCT(PIPING!L4:L100="hovensa")*(PIPING!Q4: Q100="no")*(PIPING!S4:S100="no")}.

Hope this makes sense and any help would be greatly appreciated. ]]> Excel Worksheet Functions Lorax http://www.excelbanter.com/showthread.php?t=265677 Counting the Number of Consecutive Ranges http://www.excelbanter.com/showthread.php?t=265669&goto=newpost Sun, 22 Aug 2010 19:46:07 GMT I need to create a formla that counts the number of times certain text will appear in a row. For example in cells A1 - J1 there could be following: A1 = S, B1... I need to create a formla that counts the number of times certain text will appear in a row. For example in cells A1 - J1 there could be following:
A1 = S, B1 = W,C1 = S, D1 = S, E1 = S , F1= W, G1= W, H1=S, I1 = S, J1 = S

S S W S S S W W S S

I need to know how many consecutive ranges there are of S - the answer here is (obviously) 3 - as there are 3 distinct ranges of S.

Can anyone help? ]]> Excel Worksheet Functions allan2005CL http://www.excelbanter.com/showthread.php?t=265669 Macro for populating spreadsheet with formulas http://www.excelbanter.com/showthread.php?t=265668&goto=newpost Sun, 22 Aug 2010 10:46:04 GMT Hello All What I am trying to do is create a macro that populates a spreadsheet with 10+ columns of formulas for a number of rows that will be determined in... Hello All

What I am trying to do is create a macro that populates a spreadsheet with 10+ columns of formulas for a number of rows that will be determined in the macro. The formulas will reference data from another worksheet that will also contain the data for the number of rows.


The first spreadsheet contains data about a continues length of pipe (a drill string)

OD…weight/foot…Length
4……………5……………3
5…………..50……………1
8…………..15……………2


The second spreadsheet will do a set of incremental calculations (every 1 feet) along the length of that pipe that are either cumulative (depth and total weight) or increment specific (cross sectional area).

Depth…Total Weight…Cross Sectional Area
1…………………5…………………..12.6
2…………………10…………………12.6
3…………………15…………………12.6
4…………………65…………………19.6
5…………………80…………………50.3
6…………………95…………………50.3

These aren’t the exact calculations I am trying to do but if I can get these sorted then I will be good-to-go. Even if you only have partial solutions or suggestions anything would help.

I know this is a lot so I appreciate any help. If you have any questions please ask.

Thanks
-Johannes ]]> Excel Worksheet Functions johannes2008 http://www.excelbanter.com/showthread.php?t=265668