diregarding the roundingup

July 4, 2010 at 02:23:05
Specs: Windows XP
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


See More: diregarding the roundingup

Report •


#1
July 4, 2010 at 05:08:54
Hi,

It depends what you mean by rounding up

If 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.5

On 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


Report •

#2
July 4, 2010 at 07:43:39
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


Report •

#3
July 4, 2010 at 09:19:48
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 * 2

This is what you get:

	A	B
1	101.5	203.0
2	102.0	204.0

That way you still have access to the original value and you have the rounded up value as well.

If you absolutely had 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.5

Here 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 Function

Regards


Report •

Related Solutions

#4
July 4, 2010 at 09:25:33
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 Function

However, 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 result of the ROUNDUP formula in A1 and not find the result of the NoRound UDF.


Report •

#5
July 5, 2010 at 11:05:35
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


Report •

#6
July 5, 2010 at 11:53:47
This page covers UDF's in detail:

http://www.cpearson.com/excel/Writi...


Report •

#7
July 5, 2010 at 13:07:32
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


Report •

#8
July 5, 2010 at 14:02:31
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.

http://www.pcreview.co.uk/forums/th...


Report •

#9
July 5, 2010 at 14:15:23
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 * 2

This is what you get:

	A	B
1	101.5	203.0
2	102.0	204.0

That way you still have access to the original value and you have the rounded up value as well.

Regards


Report •

#10
July 5, 2010 at 23:36:49
I have excel 2010, can i fix this problem with same solution?

Report •

#11
July 6, 2010 at 03:37:13
Humar has asked you twice about 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.


Report •

#12
July 6, 2010 at 03:43:30
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


Report •

#13
July 6, 2010 at 04:10:35
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


Report •

#14
July 6, 2010 at 04:52:28
This link will get you information on using macros in Excel 2010.

http://tinyurl.com/2wvsuq7

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.


Report •

#15
July 14, 2010 at 02:09:25
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


Report •

#16
July 14, 2010 at 04:22:32
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 number

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

But if your formula is ROUNDUP(A1,1), you don't need a 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 formula

Regards


Report •

#17
July 14, 2010 at 04:52:13
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.5

You 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 0

The 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 Function

Regards


Report •

#18
July 14, 2010 at 05:10:13

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


Report •

#19
July 14, 2010 at 06:14:29
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


Report •

#20
July 14, 2010 at 06:43:15
You re misising my point. Anyway, thanks for your wasted time

Regards
Wael


Report •

#21
July 14, 2010 at 06:43:22
You re misising my point. Anyway, thanks for your wasted time

Regards


Report •

#22
July 14, 2010 at 07:50:33
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


Report •

#23
July 14, 2010 at 08:08:24
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


Report •

#24
July 14, 2010 at 16:21:39
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


Report •

#25
July 14, 2010 at 16:25:04
Hello Iq,

Thank you for your comments.

Appreciate

Regards

Humar


Report •

#26
July 15, 2010 at 03:28:38
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


Report •


Ask Question