Excel help

I currently have this formula
=IF(F6=0,"",SUM(((G6*0.1299)+(0.147*31))*0.94)*-1)
Currently give the value 40.92 as have it set for two decimal places it would be 40.9154 if set to four.
What I want is for the formula to round down to two decimal places, so this value would read 40.91

Any way to do it?

From very distant memory what you need to do is subtract 0.005 (i.e. half the next significant digit) then use the roundup command, or let Excel round for you.

Edited by deleted (Sun 03-Feb-13 23:19:21)

Or use the FLOOR fn.

Use the rounddown function

At its simpleness using A1 as the cell for 40.9154 =rounddown(A1, 2) would give you 40.91. Though you could nest the rounddown function in your formula, if you do not want the answer in a separate cell.

Thanks for the reply, and others too.

It's the nesting in the formula that I'm having trouble with.

I can do it in another cell but want to try and avoid that,
Having the "IF" in the formula is throwing me.

Try =IF(F6=0,"",ROUNDDOWN(SUM((((G6*0.1299)+(0.147*31))*0.94)*-1),2))

Thanks Mikey, worked a treat.

Or you could format the result cell to two decimal places.

In reply to a post by camieabz:

Or you could format the result cell to two decimal places.

That's what I had set, but it was rounding up.

Hmm. Isn't that the point in such cases?

(Not arguing, just curious )

In reply to a post by DavidFinbarr:

In reply to a post by camieabz:

Or you could format the result cell to two decimal places.

That's what I had set, but it was rounding up.

Excel will round to nearest as a default, so 41.9154 will round up to 41.92 when displayed to 2 decimal places. 41.9149 will round down to 41.91 when displayed to 2 decimal places. 41.915 is rounded up to 41.92 to 2 decimal places (at least in Excel 2010).

Not when I'm trying to match something that rounds down.

In reply to a post by David_W:

Excel will round to nearest as a default, so 41.9154 will round up to 41.92 when displayed to 2 decimal places. 41.9149 will round down to 41.91 when displayed to 2 decimal places. 41.915 is rounded up to 41.92 to 2 decimal places (at least in Excel 2010).

I Know, that was the point of my OP. as I wanted it rounded down

OP knows all that. He happens to want to round down (Floor) not round to nearest (Round).

I know all this too - I was explaining why rounding was no good in response to camieabz

Confuse us then. You were responding:

David_W [re: DavidFinbarr]

(experienced)
Sun 03-Feb-13 15:39:21

Re: Excel help

There's a hint of grandmother sucking eggs round here

XRaySpex,

This is not the first time you've jumped on me when you've taken a reply of mine as addressed directly to the poster being replied to. There is a fine line between sarcasm / acerbicness and being offensive. We all make mistakes - the last time you jumped on me over this, I had mixed you up with the original poster. This time, I didn't repeat that mistake - but if I had made that sort of mistake, is it necessary to focus so much attention on me making that mistake?

In this textual medium, with no non-verbal clues, it's sometimes hard to pick up the intended meaning beyond the literal. The smiley did not stop your post coming across as offensive to me.


This was a case of the relevant content to base a reply on being found elsewhere in the thread than the post raising the question - so do you thread based on poster (breaking the flow of ideas), or on content (breaking the conversational links)? I chose to thread based on content in this case - but this means that the reply was not aimed directly at the poster whose content I'm building on. I hoped people can make the obvious contextual links within the thread - someone clearly wasn't understanding the original poster's problem, so I built on a later post to explain exactly why the default behaviour was not appropriate for the original poster's needs. You're technically adept and I'm not surprised you'd understood the original poster's issue. Someone else was still struggling to understand.

I'm no great fan of drawing attention to individual posters as I don't like showing people up.


You hate the length of my posts, you hate my threading and you seem prefer a fast moving forum of very short replies, whereas I prefer slightly longer explanations.

You aren't the thread police. If you hate my posts that much, please keep your counsel to yourself and put me in your ignore list. I'd greatly prefer that to you continuing to snipe at me in public and by PM.

This is a public reply because when you sniped at me around a week ago by PM, you deleted my reply without reading it, then asked me to send it again. I gave up, on the basis that you couldn't be bothered to show any care in handling what I wrote the first time, so why should I bother to resend?


Can we find a way to live and let live peaceably? Thanks for your attention.

I don't recollect you mixing me with another poster. The 1st time I remember we crossed swords was when I PM'ed you. There was no "jumping on" or "sniping" involved.

In reply to a post by David_W:

I chose to thread based on content in this case - but this means that the reply was not aimed directly at the poster whose content I'm building on.

That's what's confusing. You replied directly to (and quoted) the OP with what he already knew and stated. Indeed he took your reply and replied immediately to you in the same manner as I did. I think more peeps view these threads in logical rather than physical order. This isn't a conversation after all.

In reply to a post by David_W:

so I built on a later post to explain exactly why the default behaviour was not appropriate for the original poster's needs.

There is no mention of lack of "appropriateness" or "goodness" in your response. It might have made more sense had there been. It was simply a factual description of the standard rounding algorithm.

In reply to a post by David_W:

you deleted my reply without reading it, then asked me to send it again

You were making false and non-obvious assumptions. I did read your reply and intended to reply to it, reasonably, but I simply hit the Delete button by mistake (and I told you that when I asked you to resend). A most common and simple error, particularly the way the buttons are positioned.