Excel Custom Function

Question

I am trying to write a custom function for rounding a set of data. So that I can reuse it for each form I create and not have to rewrite the whole function.

I have tried to use Macros and VBA but frankly I have no idea what to write in them, I tried an assortment of things but none worked.

The code i wish to simplify into its own function:

=IF(ISODD(ActiveCell.value),CEILING(ActiveCell.value,1),FLOOR(ActiveCell.value,1))

so for example F3 contains the value of 31.2

=IF(ISODD(F3),CEILING(F3,1),FLOOR(F3,1))

= 32

Can I make this it’s own function?

peiyezhu · Answer

not have to rewrite the whole function.just short length of function?how about name the formula as f?then =f(...)https://support.microsoft.com/zh-cn/office/%E5%9C%A8%E5%85%AC%E5%BC%8F%E4%B8%AD%E5%AE%9A%E4%B9%89%E5%92%8C%E4%BD%BF%E7%94%A8%E5%90%8D%E7%A7%B0-4d0f13ac-53b7-422e-afd2-abd7ff379c64#:~:text=%E5%9C%A8%E5%85%AC%E5%BC%8F%E4%B8%AD%E4%BD%BF%E7%94%A8%E5%90%8D%E7%A7%B0%201%20%E9%80%89%E6%8B%A9%E4%B8%80%E4%B8%AA%E5%8D%95%E5%85%83%E6%A0%BC%EF%BC%8C%E7%84%B6%E5%90%8E%E8%BE%93%E5%85%A5%E5%85%AC%E5%BC%8F%E3%80%82%202%20%E5%B0%86%E5%85%89%E6%A0%87%E7%BD%AE%E4%BA%8E%E5%85%AC%E5%BC%8F%E4%B8%AD%E8%A6%81%E5%BA%94%E7%94%A8%E6%AD%A4%E5%90%8D%E7%A7%B0%E7%9A%84%E4%BD%8D%E7%BD%AE%E3%80%82%203%20%E9%94%AE%E5%85%A5%E5%90%8D%E7%A7%B0%E7%9A%84%E9%A6%96%E5%AD%97%E6%AF%8D%EF%BC%8C%E7%84%B6%E5%90%8E%E4%BB%8E%E5%87%BA%E7%8E%B0%E7%9A%84%E5%88%97%E8%A1%A8%E4%B8%AD%E9%80%89%E6%8B%A9%E5%90%8D%E7%A7%B0%E3%80%82,%E6%88%96%E9%80%89%E6%8B%A9%E2%80%9C%20%E5%85%AC%E5%BC%8F%20%E2%80%9D%3E%E2%80%9C%20%E5%9C%A8%E5%85%AC%E5%BC%8F%E4%B8%AD%E4%BD%BF%E7%94%A8%20%E2%80%9D%E5%B9%B6%E9%80%89%E6%8B%A9%E8%A6%81%E4%BD%BF%E7%94%A8%E7%9A%84%E5%90%8D%E7%A7%B0%E3%80%82%204%20%E6%8C%89Enter%E9%94%AE%E3%80%82

peiyezhu · Answer

To name an Excel formula, you can follow these steps:

Select the formula cell, for example, A1.

In the formula bar, enter the desired name, for example, "my_formula", and press Enter. The formula in cell A1 will still exist.

Select cell A1 and click the "Name Manager" button (under the "Formulas" tab).

In the "Name Manager" dialog box, click the "New" button.

In the "New Name" dialog box, enter the name "my_formula" (or any other name you want) and in the "Refers to" field, enter the formula "=let(x,1,3+x)" (or any other formula you want).

Click the "OK" button and then close the "Name Manager" dialog box.

Now, you can type "my_formula" in any cell, and Excel will automatically replace it with the formula "let(x,1,3+x)".
You can also name formulas with parameters by following similar steps. Here is a link to Microsoft's documentation on using names in formulas: https://support.microsoft.com/en-us/office/define-and-use-names-in-formulas-4d0f13ac-53b7-422e-afd2-abd7ff379c64.

lost1nexcel · Answer

peiyezhuthank you for your help, I am still confused on forming the whole thing.What can I use in the formula to set reference to the cell selected by the user.for example, if I want to do simple addition:=sum(F3+F4)F3 and F4 were each clicked on the user after starting the formula.or in your example “x”For my formula,I want the user to insert the formula name and select the desired cell they wish to use. What do I write to reference to the current selected cell by the user in:IF(ISODD(Cell),CEILING(CELL,1),FLOOR(X,1))

peiyezhu · Answer

=sum(F3+F4)name as =sum(x+y)then =my_formula(x,y)as toIF(ISODD(Cell),CEILING(CELL,1),FLOOR(X,1))I am not familiar with formlas,so I do not know what is the exact mean about your function.Hope other experts come with other suggestions.

Forum Discussion

Excel Custom Function

4 Replies

Resources