Forum Discussion
When would want to create and solve a formula manually without using a function?
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
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.
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.
- without using a function like SUM or VLOOKUP
what do you mean without SUM or Vlookup?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).
