Excel Help: Functions and formulas

[Justinmcn]

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]

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

 

[Justinmcn]

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]

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]

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]

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]

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

 

[XL Guru]

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

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

 

[Anne Troy]

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)

 

[Justinmcn]

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

 

[Anne Troy]

http://www.maalox.ca/