Querry @ rounding numbers

Question

i want to link a function to a cell Suppose, after doing all calculations i always have to go to print option for Printing or Print Preview so is there any way by clicking any cell i can jump to that particular function " Please reply......

Answer

You can put this code in a normal module:

Sub PrintPreviewSheet()

   ActiveSheet.Printout Preview:=True

End Sub

And then put a button on a sheet tied to that macro.

Then use this one to directly print.

Sub PrintSheet()

   ActiveSheet.Printout

End Sub

Question

QUESTION: I Know how to round a value to the multiple of any number. But if , i have some standard values( like-10,14,15,17,19,24) and want to round my answer for these values (to the lower or to the higher side of the answer) What should i do? please reply

ANSWER: Could you perhaps please post a list of example numbers and the expected end result?

---------- FOLLOW-UP ----------

QUESTION: suppose , i have some values(like 10,12,14,17,21,25,30,36) [suppose that are diameters of steel bar available in the market] and i got some answer ( like 15 mm by design )I need to round it up to higher side [ i.e. i have to take 17 mm dia. bar (which is greater than 15mm )for further claculations what should i do????? Please reply........

Answer

Create a list of numbers like so (starting on cell A1): 0 10 12 14 17 21 25 30 36

Now put this formula in cell C2 and in cell B2, enter the diameter you're looking for.

=IF(INDEX(A1:A10,MATCH(B2,A1:A10,1))=B2,B2,INDEX(A1:A10,MATCH(B2,A1:A10,1)+1))

Question

QUESTION: I Know how to round a value to the multiple of any number. But if , i have some standard values( like-10,14,15,17,19,24) and want to round my answer for these values (to the lower or to the higher side of the answer) What should i do? please reply

ANSWER: the basic approach would be like something like this:

=IF(ISNUMBER(A1),INDEX({0;10;14;15;17;19;24},MATCH(A1,{0;10;14;15;17;19;24},1),1),"")

Note that in my version of Excel, the array I show is a vertical array - in your version ofyou might need to use a different separator character than the colon to get a vertical array (single column).

Even normal rounding has rules. There several different approaches to rounding. You haven't defined what your rounding rules are so you may need to treat the the value being rounded prior to doing the match - like I say, you haven't defined the rules so the only thing I can give you is an approach that you might be able to adapt to implement your rules.

If you had specific rules, you might be able to implement it with some type of mathematical formula or some series formula or something like that - again, I don't know your rules but your description sounds like you want to select a value from a list to use as the rounded value and that is what I have given you.

---------- FOLLOW-UP ----------

QUESTION: Yes it works But only for rounding values to the lower side. What if i have to round the value to the higher side??? Please Reply ...

Answer

I guess you are asking me to make up my own rules. I would use something like this:

=IF(NOT(ISNUMBER(A1)),A1,IF(A1<10,10,IF(A1>21.5,24,IF((A1-INDEX({9;10;14;15;17;19;24},MATCH(A1,{9;10;14;15;17;19;24},1),1))>(INDEX({9;10;14;15;17;19;24},MATCH(A1,{9;10;14;15;17;19;24},1)+1,1)-INDEX({9;10;14;15;17;19;24},MATCH(A1,{9;10;14;15;17;19;24},1),1))/2,INDEX({9;10;14;15;17;19;24},MATCH(A1,{9;10;14;15;17;19;24},1)+1,1),INDEX({9;10;14;15;17;19;24},MATCH(A1,{9;10;14;15;17;19;24},1),1)))))

If gave me the results I expected (my rules) when I tested it against all the integers from 1 to 30

basically if the value being rounded was between two values in your list, if it was greater than half way to the higher value, then I rounded up to that value. Otherwise I rounded down. for example, 16 is not more than half way to 17, so it is rounded down. 21.75 is more than half way to 24 from 19, so it is rounded up