This content originally appeared on Level Up Coding - Medium and was authored by Adejumo Ridwan Suleiman
In the article How To Create Excel Functions With Python, I talked about how you can use Python in Excel to create your own Excel functions, also known as User Defined Functions (U.D.F).
Since Python and R are popular in the world of Data Science, we now have add-ins to support their use in Excel, to make life easier for those coming from a programming background.
In this article, you will learn how to create Excel functions with the knowledge you have in R.
Getting Started With B.E.R.T
Not Bidirectional Encoder Representations from Transformers but Basic Excel R Toolkit, a tool that connects Excel and R giving you the opportunity to create your own User-Defined Functions (UDFs) in Excel.
You can download the BERT installer here and install it.
After installation, start Excel and click the Excel Add-ins tab, and you will see the BERT Console button.
Click on the button to open the console,
The console has a code editor on the left and an R shell on the right, just as you have it in R Studio.
For example, running this code on BERT,
head(iris)
print("hello world")
You get,
Creating Your Own Excel Functions
On opening the BERT console in Excel, you will see a file that opens by default functions.R which is located in the startup folder Documents\BERT2\functions.
You can create your functions in this file or create other files in the folder.
Let’s create a function that performs matrix multiplication between two matrices.
MAT_MULT <- function(mat_a,mat_b){
return(mat_a%*%mat_b)
}Save the file and close the console, don’t worry your file is saved in the BERT directory,
Go to excel and create a 3x3 matrix,
Just like you will call a regular excel function, call the MAT_MULT() which starts as R.MAT_MULT() in Excel.
Documenting Your Functions
You can document the MAT_MULT() function by going to the functions.R file and adding the code below and saving.
attr(MAT_MULT, "category") <- "Matrix"
attr(MAT_MULT, "description") <- "Input Matrix"
This is going to give the function you created a category and description.
When you go back to Excel and click on the function icon on the formula bar, browse the categories and you will see the newly added category MATRIX.
Click on OK and you will see the MAT_MUL() function interactive documentation.
Conclusion
This article is just a scratch of the surface of what you can use R to do in Excel, you can read more by going to the official documentation, and also read on how to interact with Excel from Julia.
Related Reads
Level Up Coding
Thanks for being a part of our community! Before you go:
- 👏 Clap for the story and follow the author 👉
- 📰 View more content in the Level Up Coding publication
- 🔔 Follow us: Twitter | LinkedIn | Newsletter
🚀👉 Join the Level Up talent collective and find an amazing job
How To Create Excel Functions With R Programming was originally published in Level Up Coding on Medium, where people are continuing the conversation by highlighting and responding to this story.
This content originally appeared on Level Up Coding - Medium and was authored by Adejumo Ridwan Suleiman
Adejumo Ridwan Suleiman | Sciencx (2022-11-28T00:29:37+00:00) How To Create Excel Functions With R Programming. Retrieved from https://www.scien.cx/2022/11/28/how-to-create-excel-functions-with-r-programming/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.