| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with formula
Hello...
I have a formula I am looking for some assistance with. The formula I currently have rounds a dollar amount to the nearest dollar. The problem is that it always rounds up. I need the formula to be able to determine, if possible, wether it should round up or down. Example: 33.49 should round down to 33.00....33.50 (and above) should round up to 34.00. Here is what I have (which may be completly wrong anyways but works as far as rounding up only): =IF(J6="X", CEILING(I26/I25,1), I26/I25) Any assistance will be greatly appreciated. I thank you in advance for your time! -- Randy Street Rancho Cucamonga, CA |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with formula
Randy
Try =IF(J6="X", (ROUND(I26/I25,0), I26/I25) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS web: www.nickhodge.co.uk blog (non-tech): www.nickhodge.co.uk/blog/ "Randy" wrote in message ... Hello... I have a formula I am looking for some assistance with. The formula I currently have rounds a dollar amount to the nearest dollar. The problem is that it always rounds up. I need the formula to be able to determine, if possible, wether it should round up or down. Example: 33.49 should round down to 33.00....33.50 (and above) should round up to 34.00. Here is what I have (which may be completly wrong anyways but works as far as rounding up only): =IF(J6="X", CEILING(I26/I25,1), I26/I25) Any assistance will be greatly appreciated. I thank you in advance for your time! -- Randy Street Rancho Cucamonga, CA |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with formula
Hi, Randy-
The CEILING function specifically rounds up. Try ROUND instead, comme ca: =IF(J6="X", ROUND(I26/I25,0), I26/I25) ROUND rounds up or down at the .5 mark Dave O |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with formula
I think you want ROUND, not CEILING.
Maybe this: =IF(J6="X", ROUND(I26/I25,0), I26/I25) HTH, Paul "Randy" wrote in message ... Hello... I have a formula I am looking for some assistance with. The formula I currently have rounds a dollar amount to the nearest dollar. The problem is that it always rounds up. I need the formula to be able to determine, if possible, wether it should round up or down. Example: 33.49 should round down to 33.00....33.50 (and above) should round up to 34.00. Here is what I have (which may be completly wrong anyways but works as far as rounding up only): =IF(J6="X", CEILING(I26/I25,1), I26/I25) Any assistance will be greatly appreciated. I thank you in advance for your time! -- Randy Street Rancho Cucamonga, CA |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with formula
eeeek
Take the first ( off, sorry =IF(J6="X", ROUND(I26/I25,0), I26/I25) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS web: www.nickhodge.co.uk blog (non-tech): www.nickhodge.co.uk/blog/ "Nick Hodge" wrote in message ... Randy Try =IF(J6="X", (ROUND(I26/I25,0), I26/I25) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS web: www.nickhodge.co.uk blog (non-tech): www.nickhodge.co.uk/blog/ "Randy" wrote in message ... Hello... I have a formula I am looking for some assistance with. The formula I currently have rounds a dollar amount to the nearest dollar. The problem is that it always rounds up. I need the formula to be able to determine, if possible, wether it should round up or down. Example: 33.49 should round down to 33.00....33.50 (and above) should round up to 34.00. Here is what I have (which may be completly wrong anyways but works as far as rounding up only): =IF(J6="X", CEILING(I26/I25,1), I26/I25) Any assistance will be greatly appreciated. I thank you in advance for your time! -- Randy Street Rancho Cucamonga, CA |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with formula
something wrong with =round(a1,0) & format for 2 decimals
-- Don Guillett SalesAid Software "Randy" wrote in message ... Hello... I have a formula I am looking for some assistance with. The formula I currently have rounds a dollar amount to the nearest dollar. The problem is that it always rounds up. I need the formula to be able to determine, if possible, wether it should round up or down. Example: 33.49 should round down to 33.00....33.50 (and above) should round up to 34.00. Here is what I have (which may be completly wrong anyways but works as far as rounding up only): =IF(J6="X", CEILING(I26/I25,1), I26/I25) Any assistance will be greatly appreciated. I thank you in advance for your time! -- Randy Street Rancho Cucamonga, CA |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with formula
Added another ")" after the "0" and it worked like a charm!
Thank you very much for your quick response! You are a Rock Star! -- Randy Street Rancho Cucamonga, CA "Nick Hodge" wrote: Randy Try =IF(J6="X", (ROUND(I26/I25,0), I26/I25) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS web: www.nickhodge.co.uk blog (non-tech): www.nickhodge.co.uk/blog/ "Randy" wrote in message ... Hello... I have a formula I am looking for some assistance with. The formula I currently have rounds a dollar amount to the nearest dollar. The problem is that it always rounds up. I need the formula to be able to determine, if possible, wether it should round up or down. Example: 33.49 should round down to 33.00....33.50 (and above) should round up to 34.00. Here is what I have (which may be completly wrong anyways but works as far as rounding up only): =IF(J6="X", CEILING(I26/I25,1), I26/I25) Any assistance will be greatly appreciated. I thank you in advance for your time! -- Randy Street Rancho Cucamonga, CA |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with formula
Randy
I like it when posters pick up the typos. Glad it works! -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS web: www.nickhodge.co.uk blog (non-tech): www.nickhodge.co.uk/blog/ "Randy" wrote in message ... Added another ")" after the "0" and it worked like a charm! Thank you very much for your quick response! You are a Rock Star! -- Randy Street Rancho Cucamonga, CA "Nick Hodge" wrote: Randy Try =IF(J6="X", (ROUND(I26/I25,0), I26/I25) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS web: www.nickhodge.co.uk blog (non-tech): www.nickhodge.co.uk/blog/ "Randy" wrote in message ... Hello... I have a formula I am looking for some assistance with. The formula I currently have rounds a dollar amount to the nearest dollar. The problem is that it always rounds up. I need the formula to be able to determine, if possible, wether it should round up or down. Example: 33.49 should round down to 33.00....33.50 (and above) should round up to 34.00. Here is what I have (which may be completly wrong anyways but works as far as rounding up only): =IF(J6="X", CEILING(I26/I25,1), I26/I25) Any assistance will be greatly appreciated. I thank you in advance for your time! -- Randy Street Rancho Cucamonga, CA |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
