|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 03:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com