Close

Page 1 of 4 123 ... LastLast
Results 1 to 25 of 88
  1. #1
    Veteran Fools Gold's Avatar
    Join Date
    Oct 2014
    Location
    Adelaide
    Teams
    Adelaide Crows, Adelaide United, Everton
    Posts
    1,321
    Rep Level
    2624
    Rep Power
    83
    Adelaide Crows Everton NULL

    MS Excel Help Thread

    Can't remember who was the resident advisor at TIA (god bless it's soul).

    Need some help, seems relatively easy but I can't nut it out.

    Need a formula where it will count the number of positive numbers in a series. I presume working negatives would just be reversing the > symbol.
    We demand an investigation into the replacement of referee Rafshan Irmatov and his departure at a difficult time in such an important match. We ask for an examination of the grassland that was not suitable for the Syrian team, while Australian players seem to be wearing shoes that cling to the long grass, so we saw Syrian players fall during the game, while Australia's players are steady.

  2. #2
    Veteran Dogbloke V3's Avatar
    Join Date
    Oct 2014
    Posts
    13,333
    Rep Level
    17284
    Rep Power
    710
    WCE Tottenham Hotspur Boston Celtics
    I can help you with ms paint if you need...

  3. #3
    Veteran Narkee's Avatar
    Join Date
    Oct 2014
    Teams
    New Orleans Saints, Geelong, Liverpool, OKC
    Posts
    3,763
    Rep Level
    7664
    Rep Power
    231
    New Orleans Saints Geelong Cats Liverpool
    =COUNTIF(cell_range, ">0")

  4. #4
    #demteeth origi's Avatar
    Join Date
    Oct 2014
    Posts
    3,812
    Rep Level
    5259
    Rep Power
    209
    WCE Liverpool
    =IF(narkee=fgt, "Y", " N")

  5. #5
    #FlyEaglesFly sblack's Avatar
    Join Date
    Oct 2014
    Location
    Sydney
    Teams
    Philadelphia Eagles, Brisbane Lions, Sixers, Hoyas, United, Roma
    Posts
    8,306
    Rep Level
    15171
    Rep Power
    488
    Philadelphia Eagles Man Utd Roma
    Quote Originally Posted by origi View Post
    =IF(narkee=fgt, "Y", " Y")
    Fixed the slight error

  6. #6
    #demteeth origi's Avatar
    Join Date
    Oct 2014
    Posts
    3,812
    Rep Level
    5259
    Rep Power
    209
    WCE Liverpool
    Narkee

    Conditional formatting... Say I am doing a DT spreadsheet and I want the cell to take on a certain colour based on the team name... How do I apply 18 different colour schemes in one cell range instead of having to select individual cells ranges and apply that conditional formatting formula, rinse and repeat x 18.

    Is is possible?

    EDIT: I just worked it out. You can apply 18 rules to the same cell range. Cheers
    Last edited by origi; 5th March 2015 at 11:39 PM.

  7. #7
    Veteran
    Join Date
    Oct 2014
    Posts
    4,317
    Rep Level
    12157
    Rep Power
    298
    Using a spreadsheet for my cricket world cup bets/a bit of excel training. So i have my colum of what the bet is, amount staked, odds, W/L of N/R and then return

    I set up an IF function for the return column, IF(D2="W",B2*C2, 0), so essentially If I type a W or L in the result column it'll automatically calculate the return.

    Is there any way to also put in the possibility of No Result (N/R) in the original fucntion, whereby if it is NR the 'return' just equals the stake?

    At the moment I just put a different if formula in for that one bet - If(D3="N/R",B3, 0), but would love to know if I can incorporate it all in one. It doesn't really matter for this spreadsheet too much but excel is something I want to get better and better at using

  8. #8
    Veteran yeah_nah's Avatar
    Join Date
    Oct 2014
    Location
    Perth
    Teams
    Toasters
    Posts
    9,838
    Rep Level
    22945
    Rep Power
    627
    Quote Originally Posted by Red Bear View Post
    Using a spreadsheet for my cricket world cup bets/a bit of excel training. So i have my colum of what the bet is, amount staked, odds, W/L of N/R and then return

    I set up an IF function for the return column, IF(D2="W",B2*C2, 0), so essentially If I type a W or L in the result column it'll automatically calculate the return.

    Is there any way to also put in the possibility of No Result (N/R) in the original fucntion, whereby if it is NR the 'return' just equals the stake?

    At the moment I just put a different if formula in for that one bet - If(D3="N/R",B3, 0), but would love to know if I can incorporate it all in one. It doesn't really matter for this spreadsheet too much but excel is something I want to get better and better at using
    If (cell=nr,stake,if (cell=w,stake x odds,0))

    Something to that effect will do it

    Basically youre making your "if cell doesnt equal whatever" a second IF function
    Quote Originally Posted by Eagle E View Post
    I then started saving extra money as a result of eating dumpster food.

    I knew when to hit the dumpster for optimum catch.
    Quote Originally Posted by Eagle E View Post
    accualy scared of getting knocked out by yenny
    #broughtback

  9. #9
    Veteran
    Join Date
    Oct 2014
    Posts
    4,317
    Rep Level
    12157
    Rep Power
    298
    Thank you, nailed it

  10. #10
    #FlyEaglesFly sblack's Avatar
    Join Date
    Oct 2014
    Location
    Sydney
    Teams
    Philadelphia Eagles, Brisbane Lions, Sixers, Hoyas, United, Roma
    Posts
    8,306
    Rep Level
    15171
    Rep Power
    488
    Philadelphia Eagles Man Utd Roma
    Narkee

    Would you mind lending some help?

  11. #11
    #FlyEaglesFly sblack's Avatar
    Join Date
    Oct 2014
    Location
    Sydney
    Teams
    Philadelphia Eagles, Brisbane Lions, Sixers, Hoyas, United, Roma
    Posts
    8,306
    Rep Level
    15171
    Rep Power
    488
    Philadelphia Eagles Man Utd Roma
    Or finance gs
    Ball So Hard? divvydan

  12. #12
    Veteran Triptanes's Avatar
    Join Date
    Oct 2014
    Teams
    Toasters
    Posts
    13,742
    Rep Level
    26322
    Rep Power
    817
    WCE Toast WCE
    Just post it and someone will help.

  13. #13
    #FlyEaglesFly sblack's Avatar
    Join Date
    Oct 2014
    Location
    Sydney
    Teams
    Philadelphia Eagles, Brisbane Lions, Sixers, Hoyas, United, Roma
    Posts
    8,306
    Rep Level
    15171
    Rep Power
    488
    Philadelphia Eagles Man Utd Roma
    Fine cunt

    So just one large question which requires us to create a working spreadsheet to workout prices

    This is the question and stipulations

    ORROWING COST OF ISSUING BANK BILLS

    In order to fund its short-term operations, the Chief Financial Officer (CFO) of Meilleur Company has decided use short-term money market instruments. The CFO has asked you to advise the company of the best course of action. After a lengthy discussion with the CFO, it was decided to issue bank-accepted bills of exchange (bank bills). In order to obtain board approval, the CFO has asked you to create a simple spreadsheet to illustrate the immediate factors (such as discount rates, fees and charges, and tax) that affect the amount of funds raised, and ultimate effective cost to Meilleur Company of issuing the bank bills.
    THE TASK

    You and your team are to create a simple spreadsheet for calculating bank bill prices and the effective cost of raising funds through the issue of bank bills allowing for fees and charges and taxation.
    Users of the spreadsheet should be able to input the following:
    Spreadsheet Inputs (i.e., what the user will type into the relevant cells of the spreadsheet).

    1. The face value of the bank bill (the dollar amount to be paid to the holder at maturity);
    2. The term to maturity at issue (in days);
    3. The market yield on the purchase date expressed as a simple rate of discount (% p.a.).;
    4. Fees and charges (the dollar amount to be paid by the issuer of the bank bills);
    5. When the fees and charges are to be paid (either at issue or maturity).
    6. Tax rate on interest. Tax is paid at maturity by the issuer.

    Once you have defined your inputs, your spreadsheet should output the following:
    Spreadsheet Outputs (i.e., what the user of the spreadsheet will see after entering all their inputs).

    1. The ‘gross’ amount of funds raised (i.e., the issue price of the bank bill). This does not allow for fees and charges and taxation.
    2. The ‘net’ amount of funds raised (which means the amount of funds raised after deduction of fees and charges and taxation if appropriate).
    3. The gross outgo at maturity (that is the amount that has to be paid at maturity including fees and charges and taxation if appropriate)
    4. The real cost of borrowing, allowing for the fees and charges and taxation (remember when you are “issuing” bank bills you are borrowing money). Express this as a rate of simple discount p.a., rounded to two decimal places, in percentage form.
    5. Include a bar graph in your spreadsheet to compare the issuer's borrowing costs (with no allowance for fees and charges and taxation, i.e., zero fees and charges and taxation); the borrowing costs allowing for fees and charges at issue (but tax paid at maturity) and the borrowing costs if allowance is made for the fees and charges and taxation at maturity.

    Your spreadsheet should be clearly labelled and easy to understand. Remember to make sure you identify what the “inputs” and “outputs” are.
    Obviously the work comes in the output part.

    Slowly working through

    FV - B1
    Term - B2
    etc for inputs

    Gross would be: =B1/((1+B3*B2/365))
    Net: =IF(B5="ISSUE",B9-B4,B9) Gross is B9, so essentially if Fees are maturity its just the gross price, if its at Issue its Gross minus the fees.

    I haven't gone to any of these classes so doing this purely from quick reading and memory from last semester.

    I think I've nearly got most of it thought out just need some guidance if Im going in the right direction.

    Don't want straight answers, maybe just help me force myself to learn

  14. #14
    Veteran Narkee's Avatar
    Join Date
    Oct 2014
    Teams
    New Orleans Saints, Geelong, Liverpool, OKC
    Posts
    3,763
    Rep Level
    7664
    Rep Power
    231
    New Orleans Saints Geelong Cats Liverpool
    You are on the right track.

    Just on the fees, if they are payable on maturity then the net proceeds will be more as the fees will also require discounting i.e. you are effectively getting a return on the deferral of payment.

    I also think the formula you have used to calculate the gross value is too simple as it does not consider payments of interest through the term.

  15. #15
    #FlyEaglesFly sblack's Avatar
    Join Date
    Oct 2014
    Location
    Sydney
    Teams
    Philadelphia Eagles, Brisbane Lions, Sixers, Hoyas, United, Roma
    Posts
    8,306
    Rep Level
    15171
    Rep Power
    488
    Philadelphia Eagles Man Utd Roma

    Quote Originally Posted by Narkee View Post
    You are on the right track.

    Just on the fees, if they are payable on maturity then the net proceeds will be more as the fees will also require discounting i.e. you are effectively getting a return on the deferral of payment.

    I also think the formula you have used to calculate the gross value is too simple as it does not consider payments of interest through the term.
    It's only very basic. All the formula sheets we get for price of bills is FV/1+rt/365

    ACST201 QUIZ1.xlsx

    This is the finished thing minus the graph which i need to figure out.

    Pretty sure its right. It's only meant to be basic af

  16. #16
    Veteran wolfgang's Avatar
    Join Date
    Oct 2014
    Posts
    2,955
    Rep Level
    6243
    Rep Power
    185
    Is it possible to have a scatter plot cyclic graph or do I have to use a line graph

  17. #17
    Veteran Triptanes's Avatar
    Join Date
    Oct 2014
    Teams
    Toasters
    Posts
    13,742
    Rep Level
    26322
    Rep Power
    817
    WCE Toast WCE
    Quote Originally Posted by wolfgang View Post
    Is it possible to have a scatter plot cyclic graph or do I have to use a line graph
    Marked radar graph? Not exactly sure what you are trying to do though.

  18. #18
    Veteran wolfgang's Avatar
    Join Date
    Oct 2014
    Posts
    2,955
    Rep Level
    6243
    Rep Power
    185
    Quote Originally Posted by Triptanes View Post
    Marked radar graph? Not exactly sure what you are trying to do though.
    marked radar doesn't look like it's what i need.

    I was running tests on batteries measuring their conductance over temperature cycles. 24,33,40,50,40,33,24 etc. the problem is, because room temperature was 24 degrees and the lowest temp the shitty oven i was forced to use can do is 33, that when I use a line graph the x axis is horrible but when I use a scatter plot the graph loops back around on it self.

  19. #19
    Veteran Triptanes's Avatar
    Join Date
    Oct 2014
    Teams
    Toasters
    Posts
    13,742
    Rep Level
    26322
    Rep Power
    817
    WCE Toast WCE
    Quote Originally Posted by wolfgang View Post
    marked radar doesn't look like it's what i need.

    I was running tests on batteries measuring their conductance over temperature cycles. 24,33,40,50,40,33,24 etc. the problem is, because room temperature was 24 degrees and the lowest temp the shitty oven i was forced to use can do is 33, that when I use a line graph the x axis is horrible but when I use a scatter plot the graph loops back around on it self.
    I think this is easily fixed. So you want conductance on an x-axis, and temp on y-axis?

    And then without the last point looping back?

  20. #20
    Veteran wolfgang's Avatar
    Join Date
    Oct 2014
    Posts
    2,955
    Rep Level
    6243
    Rep Power
    185
    Quote Originally Posted by Triptanes View Post
    I think this is easily fixed. So you want conductance on an x-axis, and temp on y-axis?

    And then without the last point looping back?
    temp on x axis, conductance on y. I just mocked these up quickly because I don't have the data with me right now.

    this is what my graph currently looks like


    this is what i'd like it to look like. but with x axis every 10 degrees

  21. #21
    Veteran Triptanes's Avatar
    Join Date
    Oct 2014
    Teams
    Toasters
    Posts
    13,742
    Rep Level
    26322
    Rep Power
    817
    WCE Toast WCE
    Quote Originally Posted by wolfgang View Post
    temp on x axis, conductance on y. I just mocked these up quickly because I don't have the data with me right now.

    this is what my graph currently looks like


    this is what i'd like it to look like. but with x axis every 10 degrees
    I think the best way to show it is actually as in the first graph. You could just add a label/arrow for heating and one for cooling.

    The second graph isn't right to me - an axis that increases then decreases is terrible. You can do it using a line graph and having a data point for each degree, but leaving blank all the corresponding conductance data that you don't have (i.e x data for every 1 degree from 24-50 then from 49-24, but y data only for 24, 33, 40, 50, 40, 33, 24)

  22. #22
    Veteran Triptanes's Avatar
    Join Date
    Oct 2014
    Teams
    Toasters
    Posts
    13,742
    Rep Level
    26322
    Rep Power
    817
    WCE Toast WCE
    e.g.

  23. #23
    #FlyEaglesFly sblack's Avatar
    Join Date
    Oct 2014
    Location
    Sydney
    Teams
    Philadelphia Eagles, Brisbane Lions, Sixers, Hoyas, United, Roma
    Posts
    8,306
    Rep Level
    15171
    Rep Power
    488
    Philadelphia Eagles Man Utd Roma
    Narkee and other Gs

    Function help

    Two tables of names and data.

    Ie

    Table 1 table 2

    1 1
    2 3
    4 4
    5 6
    7 7

    Rough idea of what it is.

    Next to table 2, there are values. I want to grab the values in table 2 and input them into table 1, but only if the names are matching. Simplistic way would be =IF(etc) but that kinda only works if they are in a certain order. My tables is slightly out of whack with extra values.

    How can I do what I need?

    If it makes sense

  24. #24
    Veteran Narkee's Avatar
    Join Date
    Oct 2014
    Teams
    New Orleans Saints, Geelong, Liverpool, OKC
    Posts
    3,763
    Rep Level
    7664
    Rep Power
    231
    New Orleans Saints Geelong Cats Liverpool
    Do you have a sample screeny?

  25. #25
    #FlyEaglesFly sblack's Avatar
    Join Date
    Oct 2014
    Location
    Sydney
    Teams
    Philadelphia Eagles, Brisbane Lions, Sixers, Hoyas, United, Roma
    Posts
    8,306
    Rep Level
    15171
    Rep Power
    488
    Philadelphia Eagles Man Utd Roma
    Yes its Fantasy Football spreadsheet




    I essentially need the value in the second salary column (S) to be put into the corresponding cell in Column K, as long as the name matches. Which as you can see doesnt match up exactly in each row.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •