LinkBack URL
About LinkBacks
=ROUNDUP(E14*M5/0.51,0)*0.5
E14=9.58
M5 = 1
This is providing the result 9.5 but should be rounding up to 10
Can someone help me with this?
A further note it seems to work as it should unless you enter a number ending in .6 ie 6.6 7.6 8.6 9.6 etc
=ROUNDUP(E14*M5/0.51,0)*0.5
E14=9.58
M5 = 1
This is providing the result 9.5 but should be rounding up to 10
Can someone help me with this?
A further note it seems to work as it should unless you enter a number ending in .6 ie 6.6 7.6 8.6 9.6 etc
Last edited by mikerules; 03-26-2014 at 06:45 PM.
You need to keep the math inside the ROUNDUP function, like this:
Cheers,
Please Login or Register to view this content.
Would you like to say thanks? Please click the: "Add Reputation" button, on the grey bar below the post.
Deleted because identical to ConneXionLost.
Last edited by newdoverman; 03-26-2014 at 07:03 PM.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Unless you're attempting something exotic, the two numbers in red should be the same:
=ROUNDUP(E14*M5/0.51,0)*0.5
Entia non sunt multiplicanda sine necessitate
With this if I enter as an example .19 or some fraction of 1 under .5 it is rounding to 1 rather than .5
Another possibility for you
Formula:
Please Login or Register to view this content.
Last edited by newdoverman; 03-26-2014 at 07:50 PM.
Before I change that I would actually prefer if it was under 1 cf that it rounded to 1 with 1 as a minimum. Is this what is happening in the first example you sent?
In the example =CEILING((E14*M5/0.51*0.5),10) this is rounding up to 10 so this is not what it needs to do.
If the total of E14*M5 is less than .5 the result should round up to 1 in all other cases it should round up to the nearest .5
Would it help if I attached a copy of the sheet?
The 10 should be 1...typo...corrected.
Yes, it would help to see a copy of the worksheet as this is becoming confusing as to what you are looking for.
The entry is being made to E14 and it is * by E5 which I have set to 1 to keep this simple. The result is going into F14.
If when the amount entered in E14 * by E5 results in an amount under 1 this should calculate 1 F14 (1 being the minimum)
If when the amount entered in E14 * by E5 results in an amount of .5 or greater this should round up to the nearest .5 ie 4.3 > 5, 9.8 > 10, 6.1 > 6.5
As SHG suggested, the formula should be the following to round up to the nearest .5:
Formula:Please Login or Register to view this content.
Just from that explanation,
=max(1, ceiling(e5*e14, 0.5))
Sorry but that is def not right as say 1.4 is giving an answer of 41.5 rather than 1.5
Ok sorry that was my error in what I told you as it was not E5 it was M5. I have made the change and it works great. Thank-you for being patient with me
One last thing I just noticed is that if there is no entry in E14 F14 still gives a result of 1. Is it possible that if E14 does not have an entry then F14 would not post an entry?
If F14 is =ROUNDUP(E14*M5/0.5,0)*0.5 and there is nothing in E14, the result is 0
If you want a blank cell in F14 if E14 is blank
Formula:Please Login or Register to view this content.
This works except that if it is under .5 it is rounding to .5 rather that 1. Above .5 it works fine
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks