I am trying to lock a cell to a value, but it reverts back to the calculated value. What I am trying to do is take a cell value from say cell I4 divided it by the sum of F4-G4 which would give me a value of x.xxxxxx. I use the number format to round it to a whole number without the decimal points. Cell H4 = I4/(F4-G4) Now when I reference that cell H4 times another cell it reverts back (H4) to the number with all the decimals at not the whole number. How can I make it so if I need to reference the cell (H4) it will be the whole number and not the number with all the decimal places. I hope I explained my dilemma well enough. I am at my wits end.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
locking a cell to a value
0
Answers
0
Selected Answer
You might use the ROUND() function.
[H4] = ROUND(I4/(F4-G4),0) In this formula the final argument indicates the number of decimal places you want to keep, 0 in the above example.
However, this method retains a Double data type which is very good for having lots of decimals or astronomically large numbers. Instead, you might simply convert the calculated result to an integer. The integer data type can't have decimals. I might add that Excel's integer is different from VBA's in that it isn't limited to a maximum value of 32000-odd
[H4] = INT(I4/(F4-G4)) The difference is in the rounding. The ROUND() function rounds 5/4. The INT function truncates, meaning 3.99 will come out as 3. But there is a little trick to round up instead of down. =INT(-3.01)*-1 will produce 4. Applied to your formula that would look as shown below.
[H4] = INT(-I4/(F4-G4)) * -1The key is that the INT() function rounds negative numbers differently. So, -3.5 will become -4 while +3.5 will become 3. Therefore my formula will not work as expected if I4/(F4-G4) produces a negative result naturally.
Discussion
Works perfectly thanks. I had tried the round function, but I forgot to put the ,0 at the end. Thank you thank you
Old Greenhorn (rep: 2) Oct 12, '20 at 2:50 am