Technical Discussion
  >> Windows Issues


Register (or login) on our website and you will not see this ad.


Pages in this thread: 1 | 2 | (show all)   Print Thread
Standard User DavidFinbarr
(freechataholic) Sun 03-Feb-13 11:41:08
Print Post

Excel help


[link to this post]
 
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?

A bottle of white, a bottle of red
Perhaps a bottle of rosé instead.
Standard User HTTP404
(learned) Sun 03-Feb-13 11:46:59
Print Post

Re: Excel help


[re: DavidFinbarr] [link to this post]
 
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 HTTP404 (Sun 03-Feb-13 23:19:21)

Standard User XRaySpeX
(eat-sleep-adslguide) Sun 03-Feb-13 12:11:50
Print Post

Re: Excel help


[re: DavidFinbarr] [link to this post]
 
Or use the FLOOR fn.

1999: Freeserve 48K Dial-Up => 2005: Wanadoo 1 Meg BB => 2007: Orange 2 Meg BB => 2008: Orange 8 Meg LLU => 2010: Orange 16 Meg LLU => 2011: Orange 19 Meg WBC


Register (or login) on our website and you will not see this ad.

Standard User Mikey2
(eat-sleep-adslguide) Sun 03-Feb-13 12:30:13
Print Post

Re: Excel help


[re: DavidFinbarr] [link to this post]
 
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.

Mike
If you have to swallow a frog, try not to think about it. If you have to swallow two frogs, don't swallow the smaller one first.
Standard User DavidFinbarr
(freechataholic) Sun 03-Feb-13 12:34:58
Print Post

Re: Excel help


[re: Mikey2] [link to this post]
 
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.

A bottle of white, a bottle of red
Perhaps a bottle of rosé instead.
Standard User Mikey2
(eat-sleep-adslguide) Sun 03-Feb-13 13:08:55
Print Post

Re: Excel help


[re: DavidFinbarr] [link to this post]
 
Try =IF(F6=0,"",ROUNDDOWN(SUM((((G6*0.1299)+(0.147*31))*0.94)*-1),2))

Mike
If you have to swallow a frog, try not to think about it. If you have to swallow two frogs, don't swallow the smaller one first.
Standard User DavidFinbarr
(freechataholic) Sun 03-Feb-13 13:13:21
Print Post

Re: Excel help


[re: Mikey2] [link to this post]
 
Thanks Mikey, worked a treat.

A bottle of white, a bottle of red
Perhaps a bottle of rosé instead.
Standard User camieabz
(sensei) Sun 03-Feb-13 14:55:45
Print Post

Re: Excel help


[re: DavidFinbarr] [link to this post]
 
Or you could format the result cell to two decimal places.

~ Camieabz ~

All Connection Data ~ Some plusnet links

mod'er·a'tion n.
Synonyms: temperance, restraint, modesty.
Standard User DavidFinbarr
(freechataholic) Sun 03-Feb-13 15:17:36
Print Post

Re: Excel help


[re: camieabz] [link to this post]
 
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.

A bottle of white, a bottle of red
Perhaps a bottle of rosé instead.
Standard User camieabz
(sensei) Sun 03-Feb-13 15:28:07
Print Post

Re: Excel help


[re: DavidFinbarr] [link to this post]
 
Hmm. Isn't that the point in such cases?

(Not arguing, just curious smile )

~ Camieabz ~

All Connection Data ~ Some plusnet links

mod'er·a'tion n.
Synonyms: temperance, restraint, modesty.
Pages in this thread: 1 | 2 | (show all)   Print Thread

Jump to