Excel Help: Functions and formulas

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 

Anne Troy

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

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
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
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)
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
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.
 

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.
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
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
 
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
 

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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top