hi, I need when referring to a cell (that is rounded up) in a formula to discard the roundingup.

if i have a number 101.5 that i rounded up to 102. i need that when i m using the same cell in a formula to diregard the rounding and use the original number which is 101.5

Aprreciate your quick response

Hi, It depends what you mean by

rounding upIf Cell A1 contains 101.5 and you format it as a number with no decimal places it will show 102

The original value is still in the cell, so in A2 enter =A1 and format it as a number with one decimal place will give you 101.5On the other hand if you have =ROUND(101.5,0) in cell A1 and in cell A2 you have =A1, the value in cell A2 is 102, and nothing will change it.

Regards

Hi, I meant that if i put a formula RoundUp(101.5,0) it will give me 102. then later if i want to use this cell but use the 101.5 and not 102, how i can do it?

Regards

Hi, Once you use the formula =ROUNDUP(101.5,0), the cell that contains it now has a value of 102.

Just as I said for =ROUND(101.5,0), the value stored by Excel is 102

Why not put 101.5 in a different cell.

Try this:

In cell A1 enter 101.5

In cell A2 enter = ROUNDUP(A1, 0)

In cell B1 enter = A1 * 2

In cell B2 enter = A2 * 2This is what you get:

A B 1 101.5 203.0 2 102.0 204.0That way you still have access to the original value and you have the rounded up value as well.

If you

absolutelyhad to extract the original value from a cell containing =ROUNDUP(101.5,0) you could write a User Defined Function - it would be specific to formulas which have a value between the first "(" and the first ","

It would not work if ROUNDUP was embedded in another formula, although you could use the same approach to extracting the value in another UDF.The function in the cell to return the un-rounded value is =GetRU(<cell reference>)

If A1 contains =ROUNDUP(101.5,0)

and A2 contains =GetRU(A1),

then A2 contains 101.5Here is the UDF:

Public Function GetRU(RUform As Range) GetRU = CDbl(Mid(RUform.Formula, _ InStr(1, RUform.Formula, "(") + 1, _ InStr(1, RUform.Formula, ",") - InStr(1, RUform.Formula, "(") + 1)) End FunctionRegards

re: I meant that if i put a formula RoundUp(101.5,0) it will give me 102. then later if i want to use this cell but use the 101.5 and not 102, how i can do it?Excel will not allow you to extract the 101.5 from the formula with any built in function. You'd have to use a User Defined Function (UDF).

You could then combine that UDF with the Excel formula that you want to use the 101.5 with.

Note: This solution is far from perfect, as I'll explain later.

First, here's the UDF to try:

Function NoRound(ByVal orgFormula As Range) NoRound = Mid(orgFormula.Formula, _ Application.WorksheetFunction.Find("(", _ orgFormula.Formula) + 1, _ Application.WorksheetFunction.Find(",", _ orgFormula.Formula) - Application.WorksheetFunction.Find("(", _ orgFormula.Formula) - 1) End FunctionHowever, you need to be aware that combining this UDF with built-in Excel functions will only work in certain cases.

For example put =ROUNDUP(101.5,0) in A1 and you'll see 102.

Put =NoRound(A1) in B1 and you should get 101.5.

Put =NoRound(A1)*5 in C1 and you should get 507.5.

Put =VLOOKUP(NoRound(A1),A1:B1,2,0) and you'll get #N/A because VLOOKUP will see the

resultof the ROUNDUP formula in A1 and not find the result of the NoRound UDF.

thanks, in reality i do not have any idea about UDF or from where i can acess it, can someone explain to me about UDF or just send me a link about it.

Regards

hi, Thank you, after entering the code, it s showing an error message

the following feature can not be saved in macro-free workbooks:

VB project ...........please help

Regards

So it's safe to assume that you are using Excel 2007. Scan this thread and note the comments about saving the file as xls, xlsm and xlsx.

When all is said and done, Response # 8 of that thread may be the answer you are looking for.

Hi, Is there any reason why you can't do this:

Put 101.5 in a different cell.

Try this:

In cell A1 enter 101.5

In cell A2 enter = ROUNDUP(A1, 0)

In cell B1 enter = A1 * 2

In cell B2 enter = A2 * 2This is what you get:

A B 1 101.5 203.0 2 102.0 204.0That way you still have access to the original value and you have the rounded up value as well.

Regards

I have excel 2010, can i fix this problem with same solution?

Humar has asked you twiceabout putting 101.5 in another cell and then referencing that cell for both rounding up and for when you need to use 101.5.Before we offer any more help, we'd like to see you acknowledge his help and answer that question.

It's about respect.

sorry guys, i thought i had answered this question, I m building a model and i prefer to have one cell showing the number. regards

Hi, Put the unrounded value on another worksheet that will not be seen, or put it in a column and hide the column.

Put 101.5 in cell A1 on "Sheet2"

On "Sheet1" in any cell enter:

=ROUNDUP(Sheet2!A1,0)

Hide "Sheet2"Now you can continue to access the unrounded value but users cannot see it.

Regards

This link will get you information on using macros in Excel 2010. However, there are some things you should keep in mind:

1 - Putting macros in workbooks that will be used by others can be troublesome. They will need to have a version that allows macros, macros must be enabled, etc.

2 - Don't take this the wrong way, but since your level of expertise is presently low (but growing!) maintenance might be an issue. If a user has problems enabling his macros or if the code needs to be changed or acts strange on a user's machine, who will be there to fix it? If you'll have to post your question in a forum such as this and wait for an answer, the delay might impact your users.

Thank you very much guys; the 2 codes provided by Humar and DerbyDad03 worked fine in one condition if the number in the roundup formula was enetered manually, but it did not work if the number in roundup is refered to another cell

For example if i put roundup(101.5,0) and in another cell i put getru or noround, it will give me 101.5 which is correct but on the other hand if i put roundup(A1,0) where A1 contains 101.5 and in another cell i put getru or noround; it s giving me #novalue

please advise

Regards

Hi, Just look at the UDF's ....

They take the formula in the cell and return the part between the opening bracket and the first comma (...,

If your Formula is =ROUNDUP(101.5, 1)

you get 101.5

If your Formula is ROUNDUP(A1, 1)

you get A1 as text .... the character A and the character 1

which is not a numberThe formulas work

in one condition

because they were written to work in one very specific condition - which is what you asked for.

Butif your formula is ROUNDUP(A1,1), youdon't needa formula to get the unrounded value - because the unrounded value is in cell A1, just use =A1 or put A1 where you need the unrounded value in another formulaRegards

Hi, As a follow-up:

The GetRU function converts the text it finds between the opening bracket and the first , to a number and returns a number

The NoRound function returns text, which when it looks like a number, Excel mainly converts to a number automatically.When I tried the two formulas when the cell contained =ROUNDUP(A1,0),

GetRU returned an error because it could not convert A1 to a number (GetRU uses the Cdbl function which converts text to a number)

NoRound returned A1 because it returns text.For no good reason at all you could have the following:

A1 contains 101.5

B1 contains =ROUNDUP(A1,0) and displays 102

C1 contains =NoRound(B1) and displays A1

D1 contains =INDIRECT(NoRound(B1)) and displays 101.5

E1 contains =A1 and also displays 101.5You will see that Excel sometimes handles the results from the two UDFs differently.

A1 contains =ROUNDUP(101.5,0)

B1 contains =GetRU(A1) and displays 101.5

C1 contains =NoRound(A1) and displays 101.5

B2 contains =SUM(B1) and displays 101.5

C2 contains =SUM(C1) and displays 0The SUM() function does not implicitly convert text that looks like a number to a number, and as noted before, NoRound returns text and GetRU returns a number

I also noted an error in GetRU - it appears to work OK when the Roundup is to zero due to the way Cdbl functions. It does not work correctly for other values of 'number of digits'

The correct formula is:Function NoRound(ByVal orgFormula As Range) NoRound = Mid(orgFormula.Formula, _ Application.WorksheetFunction.Find("(", _ orgFormula.Formula) + 1, _ Application.WorksheetFunction.Find(",", _ orgFormula.Formula) - Application.WorksheetFunction.Find("(", _ orgFormula.Formula) - 1) End FunctionRegards

In fact Humar, the number is not in another cell, the number in round up is a combination of multiple formulas. I just gave A1 as an example.So as I understand from you that i can not applie the formula when it is related to other cells because it is a text and can not be converted to number.

Is there any other code to fix this issue? i know that maybe you are thinking that i m complicating my self, but beleive me this will help me a lot in my model.

Regards

Hi, you said

So as I understand from you that i can not applie the formula when it is related to other cells because it is a text and can not be converted to number.You miss the point completely ...

The formulas are very specific and met your original request...

They return what is in the formula between the first bracket and the first comma.If your ROUNDUP() formula does not have the unrounded value such as "101.5" actually in the formula, then neither of the user defined functions will return it.

If you formula is ROUNDUP(A1+B2+C3/99,0)

then getting the unrounded value will require a user defined function that can get the values from A1, B2 and C3 and then do the calculation.But If you have ROUNDUP(A1*B1), the user defined function will 'only' have to get the values from A1 and B1 and do the calculation.

So to create a user defined function that can cope with any range of ways you use ROUNDUP() is theoretically possible - but I certainly could not put that sort of time into trying to do it - you could of course pay me megabucks to do it ....

I keep coming back to what I said before - put the unrounded value or the calculation that created the unrounded value in another cell and then you can access the unrounded value and ROUNDUP() can access it as well. You need to start thinking outside the box that you seem to have gotten into.

Finally a plea - post real data:

ROUNDUP(A1,0) was what you said and I spent time responding on that basis

Now you say it wasn't ROUNDUP(A1) or anything like it ....So I just wasted a whole load of my time .....

Regards

You re misising my point. Anyway, thanks for your wasted time Regards

Wael

You re misising my point. Anyway, thanks for your wasted time Regards

Wael I am so sorry to see your response to the two gentlemen. who have gone far beyond their call of professionalism to help you out. In return they get

"You re misising my point. Anyway, thanks for your wasted time"

Please be cognizant of the fact that I have no idea who these gentlemen are and I have no conflict of interest with them. As such, my two cents of input is for your future benefit in dealing with your supporters and also not to discourage good people from doing their excellent job all in the name of helping others. Please do not take this in a negative sense in any form or shape. I wish you the very best!!!

Regards

Iq, I thanked them many times before and i really appreciate what they did (refer to the previous replies). I was disapointed to see Humar response as if i do not know what i m talking about or as if he know s better my model and my need. so i just thanked Humar for his "wasted time" as he said.

Regards

Hi Wael, I clearly do not know what your model is, and I certainly never implied that I did.

What I did note was that you shared very little, making it hard to respond with suggestions.

We all get into situations were we have a project and a perceived way of getting there, in your case it appeared from what you said to be a very specific way of handling a value that has to be rounded up. Nothing wrong with that, except that the only solutions that came along were specific user defined functions. To expand that user-defined function to do what you later said you wanted would be a major task, not just to program but also to test.

At that point, I suggest, you need to look around for alternative strategies. You want to go down a particular path, but the path is effectively blocked.

I may of course be wrong, and I would be delighted if someone offered a solution. I would be most interested to see how they solved the problem.I had been thinking about strategies to take parts of formulas and obtain the intermediate results (Excel does this when you use Formula evaluation), and I wondered if that function could be harnessed. But whatever the possibilities it is not something that I was likely to achieve in the next month or two, or more likely ever!

My comments were partly driven by the practicalities of getting to a workable solution, and part by the fact that you shared so little and then changed the goal-posts. It was indeed frustrating.

I did not imply that you do not know what you are talking about, but your response to the issue of the user defined formula, saying that it was a matter of text / not being able to convert to a number, was wrong. That was not the issue and it was not the basis for not being able to get a working user defined formula for the situation that you described. As a result I feel comfortable with my comment about missing the point. I am sorry if that upset you, but the failure of the user defined function and the reason it failed was crucial to any attempt to go further along the route of extracting the unrounded value from a cell containing a formula.

Regards

Humar

Hello Iq, Thank you for your comments.

Appreciate

Regards

Humar

In reality Humar, due to my limited knowledge, i assumed that the solution of the issue will be the same if i entered a number or refered a formula into the round up, which i discovered later that it is not the same, i just was fraustrated from your answer taht i wasted your time which was not my plan ever as i really need the answer. Anyway thank you guys for your valuable time, and sorry for any inconvinience.

Regards

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History