MS Excel - Setting value after decimal to .95 1

[bamundsen]

Hello:

I have a spreadsheet of numerical values. I have a simple formula where C1 = A1+B1. If A1 = 5.59 and B1 = 6.32, is there any way to make the total equal 11.95 instead of 11.91? I would always want the value after the decimal point to be .95

Any help or ideas would be greatly appreciated!!

Thanks,

Brett

 

[CBasicAsslember]

Look at the functions MROUND, CEILING, and FLOOR. For MROUND you will need to install the Analysis ToolPak.

 

[mintjulep]

=Int(A1) + Int(B1) + .95

 

[KenWright]

mintjulep, I think your answer would give an incorrect result for what the OP is after if the values had been say 5.59 + 6.42, because yours would still return 11.95, whereas I'm guessing the OP would want 12.95.

I think he would actually want INT(A1+B1)+0.95

could be wrong though

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[TonyJollans]

I think he would actually want INT(A1+B1)+0.95

could be wrong though

Indeed, always wanting 0.95 regardless of actual result is an odd request which can only really be done that way.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[mintjulep]

The functional spec is that the decimal is always .95, and the example shows the sum if the integer parts of two numbers.

Of course functional specs are not allways correct...

 

[bamundsen]

It works! Thank you KenWright, and all others who replied. I wondered if anyone would think this is a strange request. This spreadsheet includes product cost & freight (over 5000 products). I needed to come up with a sell price, and since we sell everthing at ***.95, I was getting tired of using a formula to add cost & shipping, but still have to manually change the values after the decimal to .95

Thanks again for your help!

Brett

 

[KenWright]

You're welcome.

The functional spec is that the decimal is always .95

mintjulep - I would have said that the functional spec, although granted not 100% clear, could logically be interpreted to be a request for the decimal after the total to be 0.95, especially after the reference to the total just prior to the actual request.

.....and the example shows the sum if the integer parts of two numbers.

Pure coincidence and a bad choice of example numbers by the OP, as had both decimals summed to greater than 1, then it would have been much clearer. That having been said, the example numbers did also support what the OP was actually after.

Also, given the 0.95 part of the request, it was reasonable to assume that it was connected with some sort of pricing methodology in which case the logical assumption was that a few pence lost if rounded down to 0.95 would be generally irrelevant, whereas it was unlikely that they would want to lose £1 by using the method you chose.

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[mintjulep]

Ken,

A good functional spec leaves no room for guessing, interpretation or assumptions.

Had this been paying work, both of us would have been in the wrong for not seeking clarification before committing to code.

 

[KenWright]

Agree with you there 100%

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------