Rounding up to the next whole number in macro.

[TIMSHARPEUK]

Hi guys.

I have the following code.

Please Login or Register  to view this content.

I appreciate its a little untidy for which i apologise.

The problem i have is that

Please Login or Register  to view this content.

produces a number that in most cases will have at least one decimal place and i need to round this to a whole number but always up and never down, (5.001 should become 6). Can anyone offer any suggestions on how to achieve this?

The idea of this code is to calculate the cost of towing a glider to a particular height. there is a fixed price to tow to 2000ft (in this case its £18) so any height upto 2000 is £18. above this its another £3.50 for every 500ft or part there of, and this is where the rounding up comes in. My code takes the 2000 off the tow height to leave any extra chargeable height, and then divides that by 500 to give how many "500fts" there are left (ie. 1500ft is 3x500ft). as long as the number is exactly dividable by 500 there is no problem but if its say, 1600ft, the result is 3.2, but because its charged for every 500ft, or part of, i need the result to be 4 so the calculation works correctly.

The values are not hard written into the code as prices and heights could change in the future hence the reference to a worksheet.

Sorry if im babbling on, and i hope you can understand what im trying to achieve.

thanks in advance

Tim

[GC Excel]

Try

Please Login or Register  to view this content.

[TIMSHARPEUK]

Hi. thanks for your help.

i moved a bracket or two around and came up with this...

Please Login or Register  to view this content.

however it returns daft figures.

If Cell B8 = 18
cell B9 = 2000
cell B10 = 2.5
cell B11 = 500

i put 2000 into AerotowHeight and the code returns "5008" which should be 18.
i put 3200 into aerotowheight and the code returns "8008" which should be 25.5

any ideas?

[GC Excel]

Your formula is incorrect:

With your values :

X = 18 + Roundup(2000 - 2000/500)*2.5
X = 18 + Roundup(2000 - 4)*2.5
X = 18 + 1996*2.5
X = 5008

Your formula should be :

Please Login or Register  to view this content.

[TIMSHARPEUK]

hi. i tryed that and it worked except that id didnt work for heights of less than 2000, it gave a price of less than 18. However, iv used this and it seems to work fine...

Please Login or Register  to view this content.

thanks for all your help!!!

Tim