How to see the Evaluate Formula step by step in Excel?
How to add the values of a column with a particular condition, if the condition shared a cell with other conditions?As you can notice in the picture above, this task is specific in that it is a condition that we set (in this case text) found in some cells which share a cell with other text. In the picture above the selected cell 'F2' in which using an ARRAY formula that has nested multiple functions. Note that in the formula used for the absolute address of the cell range of data.
So the formula would be: {=SUM(IF(ISERROR(FIND(E2;$A$2:$A$5));0;$B$2:$B$5))} you end up like an ARRAY formula
So the formula would be: {=SUM(IF(ISERROR(FIND(E2;$A$2:$A$5));0;$B$2:$B$5))} you end up like an ARRAY formula
Excel will display the formula enclosed in curly braces { }. You do not type in the curly braces - Excel will display them automatically. Type the formula in the cell 'F2' and press the CTRL+SHIFT+ ENTER keys at the same time.
The task now is to add up the corresponding values in column 'B' text RRU.
Looking cell 'E2' in which he set a condition RRU, we can see that in the column ie. The range "A3:A5' is a list of all the conditions that we set. In the first two cells 'A2' and 'A3' text is self-contained, while at the 'A4' and 'A5' is a text in the same cell. Such a situation where two different text (conditions) in a single cell creates a problem in the calculation. That each code in a separate cell then the problem is solved simply by using VLOOKUP function but this is what it can not.
It is necessary for each condition who share a cell with other text inserted separately and then added together. In the formula used following functions FIND, ISERROR, IF, SUM
The task now is to add up the corresponding values in column 'B' text RRU.
Looking cell 'E2' in which he set a condition RRU, we can see that in the column ie. The range "A3:A5' is a list of all the conditions that we set. In the first two cells 'A2' and 'A3' text is self-contained, while at the 'A4' and 'A5' is a text in the same cell. Such a situation where two different text (conditions) in a single cell creates a problem in the calculation. That each code in a separate cell then the problem is solved simply by using VLOOKUP function but this is what it can not.
It is necessary for each condition who share a cell with other text inserted separately and then added together. In the formula used following functions FIND, ISERROR, IF, SUM
The explanation for the cell 'F2'
We will use the FIND function to start, because this function searches a text value within another, and we are the text of the condition.
We'll start first with the function FIND to search the column 'A' in a range of cells "A2:A5" in which we search for the term from cell 'E2'
FIND function will return a string of numbers that show there is a text item in a given cell and the result will be {#VALUE!,1,5,4}
Meaning parts function FIND:
- Error #VALUE! mean error that will explore later ISERROR function, the more error in Excel, see the link
We will use the FIND function to start, because this function searches a text value within another, and we are the text of the condition.
We'll start first with the function FIND to search the column 'A' in a range of cells "A2:A5" in which we search for the term from cell 'E2'
FIND function will return a string of numbers that show there is a text item in a given cell and the result will be {#VALUE!,1,5,4}
Meaning parts function FIND:
- Error #VALUE! mean error that will explore later ISERROR function, the more error in Excel, see the link
For comparison, here's an example of elaboration order flow calculation function FIND for the condition in 'E2' and 'E3' preference for the range in cell 'A2'
FIND (E2;A2)
FIND ("RRU";A2)
FIND ("RRU";"RDI")
FIND ("RRU";"RDI")
The final result of the FIND function for the condition in cell 'E2' error #VALUE!
The order examples of elaboration flow calculation function FIND for the condition in the desire G4 (condition RRI)
FIND (E2,A2)
FIND ("RRI";A2)
FIND ("RRI";"RDI")
FIND ("RRI";"RDI")
The final result of the FIND function for the condition in cell 'E3' is the number one
FIND (E2;A2)
FIND ("RRU";A2)
FIND ("RRU";"RDI")
FIND ("RRU";"RDI")
The final result of the FIND function for the condition in cell 'E2' error #VALUE!
The order examples of elaboration flow calculation function FIND for the condition in the desire G4 (condition RRI)
FIND (E2,A2)
FIND ("RRI";A2)
FIND ("RRI";"RDI")
FIND ("RRI";"RDI")
The final result of the FIND function for the condition in cell 'E3' is the number one
The first step in calculating our entire formula is finding a "requirement" in the function FIND (note that this is the condition in 'E2')
Evaluate formula in Excel for this example Array formula
On the Formulas tab using the command (icon) Evaluate Formula displays the window where you can see the flow of the order of operations in the calculation formula.
When the formula with the function FIND inserted into the ISERROR function at some point in the elaboration of the flow calculation formula we have the following: ISERROR({#VALUE!/1/5/4})
- These numbers 1,5,4 have the following meanings:
If you apply an elaborate calculation formula (evaluate formulas) ISERROR(FIND(E2;$A$2:$A$5)), you'll notice the numbers and the same order
These numbers indicate a regular of the digits in the value/text in this particular cell
- These numbers 1,5,4 have the following meanings:
If you apply an elaborate calculation formula (evaluate formulas) ISERROR(FIND(E2;$A$2:$A$5)), you'll notice the numbers and the same order
These numbers indicate a regular of the digits in the value/text in this particular cell
number 1 - So we have a case for cell C4 where the search term in this case RRU is right at the beginning and starts with the first digit, ie. number one
number 5 - Look at the cell 'A2' in which the function is found Find search term in the home village five digits (RRI_RRU or 123_5), you notice that the distance between the parts of the text counts as a place digits
number 4 - See the 'A4' cell in which the function Find found the search term from cell 'E2' on the home place digits in sequence, ie, the number four (GG_RRU or 12_4).
ISEROR charge to #VALUE! replaced with TRUE and each occurrence of conditions with FALSE
ie, we get the following sequence {TRUE;FALSE;FALSE;FALSE}. Please see picture bellow.
ie, we get the following sequence {TRUE;FALSE;FALSE;FALSE}. Please see picture bellow.
IF function will result in TRUE - 0 for FALSE corresponding value from column 'B' (ie. The numbers 2,6,3).
To end the SUM function remains that such a set of values {0/2/6/3} [{0;2;6;3}] added up. These numbers 2,6,3 data from column 'B' and the corresponding textual codes of our Terms and Conditions.
The result of the first formula in cell 'F2' will be the sum of the number 11
This "complicated" formula ends Array where you have to press CTRL+SHIFT+ENTER when completing enrollment formula in 'F2' cell, and copy down. For more details, see the tutorial ARRAY formula.
To end the SUM function remains that such a set of values {0/2/6/3} [{0;2;6;3}] added up. These numbers 2,6,3 data from column 'B' and the corresponding textual codes of our Terms and Conditions.
The result of the first formula in cell 'F2' will be the sum of the number 11
This "complicated" formula ends Array where you have to press CTRL+SHIFT+ENTER when completing enrollment formula in 'F2' cell, and copy down. For more details, see the tutorial ARRAY formula.