How to add whole numbers as displayed in excel 3

[wec43wec]

In the example below

"1.51" + "1.51" = 3.02 or 3 round to a whole number

however

1.51 (round to a whole number)+ 1.51 (round to a whole number)will show

2 + 2 = 3

I would like to know how to add a set of numbers by using the whole (rounded number)and not to use any numbers to the right of the decimal point. In the above example, I would like the results to be 4 and not 3. Is this possible?

 

[mintjulep]

Whole number" and "rounded number" are not the same. Which do you want?

INT(), ROUND() or ROUNDUP()

will get you where you want to go, once you figure out where that is.

 

[SkipVought]

There is a HUGE difference between what is DISPLAYED due to FORMATTING and what value is really there. It is the VALUES, not the DISPLAY that you are manipulating.

If 1.51 is displayed as 2, the VALUE is still 1.51!!!

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[wec43wec]

As i mentioned in my original post:

1.51 displayed or rounded to zero decimal places will be displayed as 2. however, when you add both together the sum would be 3.02 or 3. I would like to know if excel would allow the sum to be 4 since the actual number viewed on the worksheet is 2 plus 2?

The viewer would not always know that the number is a roundedd number

 

[wec43wec]

Skip - I understand that the actual value is 1.51 however the person that is reviewing the report would not know that the contents of the cell is a rounded number. They would only see the 2 and not 1.51. When both figures are added together it looks like > 2 + 2 = 3 and the user sees this as an error.

 

[SkipVought]

"I would like to know if excel would allow the sum to be 4 since the actual number viewed on the worksheet is 2 plus 2?"

1.51 + 1.51 will NEVER be equal to 4 or 3, for that matter.

If you want that to happen, then YOU have to CHANGE the values, using a function, and THAT must occur in a different cell, not the cell containing the original value.





Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[wec43wec]

Thanks

 

[SkipVought]

if A1 value is 1.51, then =ROUNDUP(A1,0) will return 2.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[wec43wec]

Skip - the roundup function is exactly what I was looking for and solves my problem.

As usually - thanks and accept the Star !

 

[Gavona]

Roundup(a1,0) with a1 = 1.01 will return 2. I can't see how Skip worked out you wanted that rather than Round(a1,0)

One not so well known feature is that with the round, roundown and roundup functions you can use negative numbers as the second argument. Round(a1,-2) will round to the nearest 100.

Tools, Options, Precision as displayed
Also be aware of the above option - but it permanently changes the values held in your workbook. If formatted to 0 decimal places then 1.49 will change to 1 and 1.5 will change to 2.




Gavin

 

[macropod]

Hi wec43wec,

You could simply go to tools|Options|Calculate and check the 'Precision as Displayed' option. Then your 1.51+1.51, which appear as 2+2 will calculate as 4.

Do note that this will affect all calculations in all worksheets in all workbooks until you change it back again. You could control this behaviour via a macro that determines which worksheet is visible.

Cheers

[MS MVP - Word]

 

[wec43wec]

Macropod - thanks for your suggestion of another method to resolve my problem. I will test the data sheet to see how your suggestion works. What I have in two separate columns is hours/units (data) that is converted to hours/minutes on various rows of another worksheet and then a totsl (sum) for each column within the same row(s). After the conversion, some of the hours/minutes column will not add properly as "seen" by anyone viewing the worksheet which created the problem that I posted. I am not sure if there is a difference in the "roundup/down" when it affects hours and minutes/units, but I am overly glad that I was able to receive the suggestions made from this post, in that I have other spreadsheets that have a similar problem - meaning the "sum" is off by 1 digit.

Gavona - you are right, the "round" function works better then the roundup for my problem. When I conducted a test on Skip's suggestion I only used the 1.51 and not 1.01 which gives a different result.

As such, both of you will get a STAR - Gavona and Macrpod !