Excel Help: Functions and formulas

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

 

Would help to have your existing formula, Justin.
Then, I'm sure I'll be able to answer your question.

 

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

 

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)

 

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.

 

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.

 

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

 

>> 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

 

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

 

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)

 

Thanks, I got it to work now. I was just having a moment.

 

http://www.maalox.ca/