1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Help: Functions and formulas

Discussion in 'Business Applications' started by Justinmcn, Sep 4, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. Justinmcn

    Justinmcn Thread Starter

    Joined:
    Sep 4, 2004
    Messages:
    15
    First off I'm new to the forum, and just wanted to let you all know that everthing I know about Excel I tought my self. So, that being said I need help with mixing functions and formulas. What I want to do is be able to type a number in to a cell then in the adjacent cell have the number divided. But the problem that I've run into is I need this number to be rounded down. I'm not sure how to add the rounddown function with my existing formula. Any help will be appreciated.

    Justin
     
  2. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Would help to have your existing formula, Justin. :)
    Then, I'm sure I'll be able to answer your question.
     
  3. Justinmcn

    Justinmcn Thread Starter

    Joined:
    Sep 4, 2004
    Messages:
    15
    What I have is =D4/25. There will be several of similar formulas in this spread sheet. The problem I'm having is I need the solution to be rounded down to the whole number, ie: 4.9876 = 4. I'm not sure how to do this.

    I hope this is enough information for you to help me.

    Thanks again,

    Justin
     
  4. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    You bet. Sorry for not answering sooner. I was enthralled over at eBay. Please forgive me. LOL!!

    =round(d4/25,0)

    Where zero is the number of zeroes you want to have to the RIGHT of the decimal.

    So:

    =round(d4/25,0) will round to the nearest whole number (or dollar)
    =round(d4/25,1) will round to the nearest TENTH (or dime)
    =round(d4/25,2) will round to the nearest HUNDREDTH (or cents)

    =round(d4/25,-1) will round to the nearest TEN

    You may need to use ROUNDDOWN instead of round.

    =rounddown(d4/25,0)
     
  5. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Oh.
    And suppose you want to round to the nearest quarter (twenty-five cents).

    Go to Tools-Addins and make sure Analysis Toolpak is checked. Then use this:

    =MROUND(d4/25,25)

    That'll divide the number in d4 by 25, then round it to the nearest 25.
     
  6. Justinmcn

    Justinmcn Thread Starter

    Joined:
    Sep 4, 2004
    Messages:
    15
    Thank you so much, you rock. I've got another ? if you don't mind.

    I was wondering if there is an easy way, or any way, to have a formula set up but only be used if there is a yes or a check mark in a certain box. Ie: =d7+e7+f7 but only if g7=yes or something like that.
     
  7. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Don't mind at all. You can ask all night...as long as I'm up. :)

    =if(g7="yes",sum(d7:f7),"whatever you want if it's NOT yes")

    So, suppose you want h7 to = d7 UNLESS g7 says "yes".
    In that case, put in h7:

    =if(g7="yes",sum(d7:f7),d7)

    There's 3 parts to an IF formula (or IF statement, they're sometimes called):

    IF
    THEN
    ELSE

    So....

    IF this is true
    THEN do this
    ELSE do this

    If you don't put the ELSE part, honestly, I'm not sure what shows up! LOL!

    PS: =sum(d7:f7) is easier than writing =d7+e7+f7
     
  8. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> I need the solution to be rounded down to the whole number

    =INT(D4/25)

    >> only be used if there is a yes or a check mark in a certain box

    Checkbox controls can be found on the Forms toolbar. You could draw one on top of G7, edit out the label (eg: "CheckBox 1"), and resize the box to fit. With it still selected, you can rightclick it & Format Control. In the "Format Control" dialog, you'd mainly be interested in the "Control" tab ; make G7 the cell link (plus I always opt for 3D-shading, looks miles better). This way G7 will say TRUE or FALSE if the box is checked or unchecked. Then you can apply white font to G7, and use

    =IF(G7=TRUE,SUM(D7:F7),"")

    where you wish.

    Rgds,
    Andy
     
  9. Justinmcn

    Justinmcn Thread Starter

    Joined:
    Sep 4, 2004
    Messages:
    15
    I'm still having problems with the IF statement. What I need is if d4=yes then aa4=z4. If d4=no then aa4=0. Also, I don't know if this matters but, z4 has a formula that sums up a bunch of other cells. I keep getting a cirrcular reffernce error or something. Thanks once again.

    Justin
     
  10. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Send your spreadsheet?

    [email protected]

    For now, make sure that z4 doesn't have ITSELF in the sum formula.

    in aa4, you can put:

    =if(d4="yes",z4,0)

    :)
     
  11. Justinmcn

    Justinmcn Thread Starter

    Joined:
    Sep 4, 2004
    Messages:
    15
    Thanks, I got it to work now. I was just having a moment.
     
  12. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
  13. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/270274

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice