Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

When would want to create and solve a formula manually without using a function?

Copper Contributor

Can someone please explain to me in what scenario would someone want or need to type in a formula and calculate it manually without using a function like SUM or VLOOKUP? Can I also see some examples? Super newbie Excel user here

8 Replies
without using a function like SUM or VLOOKUP

what do you mean without SUM or Vlookup?

When would you create a formula without using the preset functions? What reason would you want to calculate a formula manually vs. automatically with =SUM or =AVERAGE? @peiyezhu 

@Clebl004 

Here's a good example of the nuances of SUM vs using +

 

Formulas:

Patrick2788_1-1697118410307.png

 

 

Results:

Patrick2788_0-1697118372980.png

 

@Clebl004 

Can someone please explain to me in what scenario would someone want or need to type in a formula and calculate it manually without using a function like SUM or VLOOKUP? Can I also see some examples? Super newbie Excel user here

 

Interesting question. Thanks, too, for clearly stating your status as a "super newbie." The question is actually fairly sophisticated, philosophically at any rate.

 

@Patrick2788 has already given you an illustration of a nuance around SUM as opposed to a series of plus signs......

 

Another thing to be aware of, and you'll surely encounter this as you mature in your usage of Excel: there are always multiple ways in Excel to get from A to B; to get from a batch of raw data (Input) to whatever output you're seeking.

 

You mention VLOOKUP in your original question, so let's take it as an example. VLOOKUP can retrieve values from tables, running down the first column (typically) to find a match (or a near match) to the value that is to be "looked up." It will then retrieve a value from somewhere else in the row where that match is made. You can accomplish that same retrieval using INDEX and MATCH, or by using a more recently introduced function called FILTER. Each of those approaches to get from A to B brings its own nuances and reasons to use or not use. 

 

I have a hard time thinking, though, of ANY reason why one would want to use a manual method--what I'm going to call "brute force"--to achieve the same result without using one of those built-in functions. It can be done: one could write a macro or VBA routine to do the lookup. I'm not sure a formula--certainly not using normal mathematical symbols for addition, subtraction, division or multiplication--could accomplish that. I CAN think of reasons to want NOT to use "brute force"--notably the greater likelihood of hidden errors. Also less efficient, more time consuming even if the result could be achieved. 

The whole reason the functions have been created is to reliably ease the process of getting from A to B.

 

A story to illustrate. Years ago, decades ago actually, I inherited the job of producing a monthly headcount report for the company I worked for. I discovered that somebody in the IT division had written a program to do it, specifically, a VBA routine in Excel. That VBA routine took (as I recall) from 20 to 30 minutes to run. I took the same raw data and created a spreadsheet that produced the report in well under a minute (literally seconds). The problem? The IT people were accustomed to writing programs--what I have been calling "the brute force" method; they'd never bothered to investigate what Excel's built-in functions could do beyond the most basic. So they got from A (raw data input) to B (the monthly report; output), but in a way that was less efficient, more error prone. THAT is why what one should be learning to do in Excel is, first and foremost, how to use the various built-in functions before resorting to macros or VBA (or "manual" formulas). There are times when the latter might be appropriate, but often--OFTEN--the built in functions are faster and more reliable (and, not to be overlooked, easier to maintain). 

@Clebl004 

To be honest I really do not understand your posting.

 

Is it about calculation mode (manual vs. automatic)?

Or is it about calculating with Excel vs. calculating with pen+paper/calculator?

Both methods require manual typing/writing.

 

Thnak you so much! This is exactly the explanation I was looking for. I was having trouble explaining the differences between formulas and functions@mathetes 

@Detlef Lewin 

 

To be honest I really do not understand your posting.

 

It WAS an unusual question, for sure. I think the clue to understanding it was that he (she?) described himself (herself) as a total newbie. And the way it was worded was less about the mechanics of Excel than about the underlying philosophy or thinking process one should use in approaching using Excel. It's almost as if he (she) was asking, "What will this tool do for me that I can't do for myself with plain ol' mathematical formulas?"

 

So it was not at all consistent with the kinds of question that we normally get on these boards--which is why it wouldn't make sense to approach it from that mindset in trying to answer it.

 

In fact, it would be interesting to know the academic background of @Clebl004 (if he or she is still following this) that prompted the original question in the first place.

@Clebl004 

That's more theoretical, not practical question. Function is predefined formula. Next is how it is predefined - by Microsoft and is built into app, or you predefined it by VBA, lambdas, name manager, whatever. And here are discussions starts.

You may find related topic by googling "formula vs function in Excel". Just on of the results

 

In common business practice, Excel users use the terms formula and function almost interchangeably. From a communication and comprehension perspective, there’s not a big difference. Just know that technically, a function is a piece of code that executes a predefined calculation, while a formula is something you create yourself.

Formula vs Function - Important Differences in Excel to Know (corporatefinanceinstitute.com)