Posts by Lil

  • Matching An Approximate Number

    I have a cell lets say A1 in which it is calculated by goal seek a percentage number, and on the other hand I have a table that has a range of percentages for example 10% 20% 30%... 100% for each percentage there is a certain number designated to it i.e. 10%---2 20%----4 etc..


    What I am trying to achieve is that by telling the computer look up the percentage value of A1 and look at the percentage rage in my table and put the number that matches with the return. I normally use an =Index(..match(..)) formula but in this case my problem is that the ammount that I can get on A1 is not a rounded ammount like 30% I can obtain numbers like 31%, 32% Is there a way I can set up in my frmula so that it may find the closest number in the table range.


    So if on cell A1 I have 35% to look up 30% on the table, but if it is over 35% to use 40% and so on.


    Thank you!!!

  • If Statement Referring To Another Worksheet

    Re: If Statement Referring To Another Worksheet


    Sure Here it is. The macro asks you for beg and ending dates 1/1/2006 12/31/2006.


    THANK YOU so much!! I appreciate your help fellow texan!


    MMh I would attach it here but the file is pretty big :(


    324 KB

  • If Statement Referring To Another Worksheet

    Re: If Statement Referring To Another Worksheet


    Well the contents on sheet 1 are derived from a query linked to access from a database, so I will be changing the account number depending on the client I am trying to develop the report. So the way it is set up know it works but only for the current account numbers in the query that I have selected, but whenever I edit the query to change the account numbers even though the information refreshes in the same destination cells, the If statement doesn't work anymore since it then selects the wrong column and row. :(


    I'm a little bit frustrated since this part only represents a small fraction of my macro, and evrything else in the macro works perfectly except for this part :(

  • If Statement Referring To Another Worksheet

    Re: If Statement Referring To Another Worksheet


    Oh the asterisk is just telling the formula that if in fact G9 is diferent than zero , then to put the value in cell G9 multiplied by -1 in order to change the sign the value has. Thank you for shortening the formula for me!! :music:

  • If Statement Referring To Another Worksheet

    I am trying to insert an If statement using the

    Code
    With Sheets("Sheet2").Select
Range("c" & rBegRow).Select
  Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=IF(Sheet1!R[-8]C[4]<>0,Sheet1!R[-8]C[4]*-1,Sheet1!R[-8]C[5]*-1)"
Range("C" & rBegRow).Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
End With


    The reason is that once the formula is inserted in to the cell in worksheet 2 then I drag it down kind of like a copy paste so it changes automatically the if statement cell reference to worksheet 1 automatically.


    the trouble I am having is that I do not want to use the R[#]C[#] since when the contents of the information changes the formula does not work properly.


    If i substitute them for G9 instead of the R[-8]C[4] and H9 R[-8]C[5] but it inputs it in the actual excel worksheet as =IF(Sheet1!'G9<>0,Sheet1!'G9*-1,Sheet1!'H9*-1) therefore creating a value error.


    I have attached a sample to this Thank youu


    :crying:

  • Calculating Std Deviation and Asset Weighted deviation of accounts grouped by class

    I am no expert at all in Access all I can do partially wel is run queries, this i what I have to do I have two tables that derive from queries.


    The first one gives the whole accounts fro the firm and a check mark on the composite classification they belong too. and then for every month of each year the account existed in teh firm it calculates the gross profit.


    Something like this


    Acct # Composite 1 Composite 2 Composite 3 200202 200203... 200607
    231 1 1.2% -1% 3%
    256 0 1 0 0 2% 1.6%
    288 1 0 0 0 0 1%
    291 0 1 0 3% 2.2% -1% 213 0 0 1 2% .2% 1.4%



    And the second table we obtain The accounts and their beggining market value for those same periods


    Acct# 200202 200203..................200607
    231 $300,000 $350,000 645000
    256 0 100000 150000
    288 0 0 50000
    291 600000 595000 550000
    213 1500000 1450000 1320000


    I made up the numbers as an example since I cant provide the actual data.


    Now I need to create a query in which it will calculate the standard deviation and the asset weighted std deviation for Each composite, like If I specify in the query to show these results for composite 1 It will show the accounts belonging to that composite and the std dev and Asset Wiegth Std for each month of the years:I have attached the example bt I solved this manually in excell. :( And I need to figure out a way to do it in access through a query.


    These are the formulas:


    Std Deviation= ((Sum(Return%of Acct #-MEan %tot)/n))^1/2


    n=number of accounts that had a return on that month.


    Mean %Tot= Sum the returns of the accounts for that month/n


    Asset Weighted Std Dev= Sum( BegMarket Value of Account#*(Return of thr account#))/Sum of all the beginning Mkt values of the accounts of that month.



    THANKYOUUU soo much I know this is probably too complicated or tooo basic but if you can give me some pointers it woudl be greatle appreciated


    THANKS!!!! :)

  • If statement to long

    Re: If statement to long


    Thank you you all the thread link helped me out a lot!! I ended up using the following:


    =INDEX(Sheet3!$C$2:$C$25,MATCH(M3,Sheet3!$B$2:$B$25,0))


    THANKYOU!!!!!!: D

  • If statement to long

    Re: If statement to long


    Im sorry here is the attachment Basically on sheet 3 I want it to look up the the account mneumonic and put the corresponding composite name on sheet 1 column O.

  • If statement to long

    I am trying to write the following statement in a cell in excel lets say O3 so I can drag it down to the rest of the column, I was trying to see if there was a previous posting about this but I didn't find it.


    the if statement goes like this:


    =IF(M3=Sheet3!$B$2,Sheet3!$C$2,IF(Sheet1!M3=Sheet3!$B$3,Sheet3!$C$3,IF(M3=Sheet3!$B$3,Sheet3!$C$3,....until it reaches to if(Sheet3!$B$20,Sheet3!$C$20,"PENDING"))


    Is there a way I can do this I know probably through a VBA but I have no idea how to set it up.


    Any help would be greatly appreciated


    THANX

  • Colour coding cells

    Re: Colour coding cells


    A way I did it is by using conditional formating , lets say cell A1 is blank


    Select whichever is the area you want to conditional format and you select to put a yellow cell color whenever equal to A1 since it is a blank cell


    and then conditional format so when cell is equal to ="Yes" Whichever color you want and so on

  • Fixed the row absolute

    Re: Fixed the row absolute


    I see what you mean in theory if you type in =B$4 you can drag it down to your column and only the column will change while the rw remains the same. It isnt working for me Another way i selecting the data copy /paste special/transpose


    I know this is not exactly what you wanted to do, but HTH

  • Extracting Unique Entries

    Re: Extracting Unique Entries


    It Actually Is working , try substituting in the F column instead of having two gregs substitute one greg for Tom and another for chris and youll see that it does work.

  • Setting a Range of Fill Dates

    Re: Setting a Range of Fill Dates


    Ok I have it working except for one glitch the closing date unfortunately if teh closing date is 200504 the macro fills the date up till 200503 instead of 200504. :(


    I created two macro this first one is to fill the dates and the account number



    and this second one is to fill the criteria which works perfectly, I know I probably designed it the loong way :)



    Any help would be greatly appreciated fixing this minor detail.


    Im attaching a sample of the file THANKYOU !!

  • Setting a Range of Fill Dates

    Re: Setting a Range of Fill Dates


    THANKYOUU! As you can see I am completely inexperienced when it comes to VBA's If it werent for the help I have gotten here I dont think I would even have a coding right now.


    Well it works out it stops one month before the closing date. So if the closing date is lets say 200604 it stops right now at 200603.


    But also I have in Sheet one more rows than the sample I posted and unfortunately with the new code it doesnt continue with each of the other accounts it stops in row 2.


    The purpose of it is that so whenever we add a new account on sheet 1 then sheet 2 when you run the macro would automatically update the previous accounts plus the new account added.