Possible to add a Floor and Ceiling to existing formula?

[Jim Clayton]

I have four cells with values in them, and then the total in the fifth cell.
Cell A3 Value is 12
Cell B3 Value is 29.3
Cell C3 Value is 34.7
Cell D3 Value is 10
Cell E3 Total is 1018.71
The formula in cell E3 is =SUM(B3*C3)+(A3-D3). I need to add a CEILING of 0.5, and a FLOOR of 1. Not sure how to phrase this. Have attached spreadsheet for clarity. Thanks all for your help.

[MrShorty]

I don't understand.

The current result is 1018.71. =CEILIING(E3,0.5) would return 1019.0. =FLOOR(E3,1.0) would return 1018.0. =FLOOR(CEILING(E3,0.5),1.0) would return 1019.0. =CEILING(FLOOR(E3,1.0),0.5) would return 1018 (and should always be the same as FLOOR(E3,1.0)).

From the other thread, I expect you understand the basic syntax and use of the FLOOR() and CEILING() functions, so it seems like this is more about the pre-Excel part of the problem of truly understanding the logic you want behind this rounding problem. Which result is correct, and what is the logic you used to get to that result?

[ben_hensel]

I'm reading this as, you want it rounded to the nearest half?

Please Login or Register  to view this content.

[Pete_UK]

FLOOR and CEILING are actually function names in Excel (used for rounding down/up to a particular increment), so this might be a bit confusing to anyone who reads your thread.

I'm not really sure what you want to do, though. Your total is 1018.71, but presumably you want this to be shown differently, so can you explain what it is that you want to achieve.

Pete

[Jim Clayton]

Thanks all for your help. Short answer to your question, I have zero training in any of this any only a moderate understanding. I've been at this less than 6 months and I'm figuring it out as I go. A lot of it makes sense though. In this particular instance, I probably didn't phrase my question appropriately, but the answers I received were super helpful and I was able to use them to help me get a resolution to my problem. So thank you both for your help and apologies for the confusion. I'm working on it.