Round with given condition

**pnegi** · 09-20-2013, 06:23 AM

Hi, i want to round the output in a cell to the specified condition.
The condition is:

if "output in cell" is >x.3 then the output is rounded to x, if the "output in cell" is <x.3 then the output is rounded to x+1

Help appreciated.

Thanks,

**daddylonglegs** · 09-20-2013, 06:31 AM

Are you sure it's that way round? Taken literally that will round 5.9 to 5 and 5.1 to 6! Assuming those should be the other way round try this

=ROUND(A1+0.2,0)

**XOR LX** · 09-20-2013, 06:33 AM

Hi,

=INT(B1)+(MOD(B1,1)>0.3)

You don't mention what the result should be if the output is precisely x.3

Regards

**kvsrinivasamurthy** · 09-20-2013, 06:45 AM

Try this

Please Login or Register  to view this content.

**pnegi** · 09-21-2013, 12:13 AM

Hi Daddylonglegs, thanks for pointing our.. yes i put ">" sign wrongly in the query

.

And your solution works well. Thanks,

What if i want to add another complication to this and would want the following:

if "output in cell" is <x.3 then the output is rounded to x, if the "output in cell" is >x.3 then the output is rounded to x+1, if the "output in cell" is >0.1 then it is rounded to 1

**pnegi** · 09-21-2013, 12:18 AM

Hi XOR LX and KVSrinivasamurthy, your formula's also work well. ZOR LX's they way i wanted, KVSrinivasamurty's the way i described the query with wrong "<>" signs. Thanks

Can you help me with the another complication in this formula with this added 3rd complication.

if the "output in cell" is >0.1 then it is rounded to 1

**kvsrinivasamurthy** · 09-21-2013, 12:43 AM

Between what values of "output in cell" value should be 1 .

**FDibbins** · 09-21-2013, 03:42 PM

maybe...
=if(A1<0.1,1,ROUND(A1+0.2,0)

I am assuming that again, you have the sign mixed around again?

If not,
=if(and(A1>0.1,A1<1),1,ROUND(A1+0.2,0)

**pnegi** · 09-23-2013, 01:03 AM

Hi kvsrinivasamurthy, the complete equation is like this:

if output in cell is >0.1 then round to "1" which means if the output in cell is <0.1 then round to 0
if output in cell is <x.3 then the output is rounded to x
if the output in cell is >x.3 then the output is rounded to x+1

Hi FDibbins's second code works well and this is what i wanted.

=if(and(A1>0.1,A1<1),1,ROUND(A1+0.2,0)

Can you guys help me put this code in VBA function, i basically need to put this round function in many output cells where there is already a sumifs function which needs to be rounded with this new function...?

Thanks Guys for you help! i am loving this forum more and more.

Round with given condition

LinkBack

Thread Tools

Rate This Thread

Display

Round with given condition

Re: Round with given condition

Re: Round with given condition

Re: Round with given condition

Re: Round with given condition

Re: Round with given condition

Re: Round with given condition

Re: Round with given condition

Re: Round with given condition

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Conditional Round up or Round up Function

[SOLVED] Is there a way to round this condition to 1 decimal place?

If condition and SUM equation

[SOLVED] Round Price Up or Down to nearest $.99 based on a Condition

[SOLVED] How do I ROUND() round off decimals of a column dataset?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions