Ace My Paper - get your Excel tasks done by experts. - makes your homework shine.


Excel tutorials

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?

Array formula and Evaluate formula in Excel 

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 a 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 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. (This is CSE formula)


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 conditions text. 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)

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)

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.

Evaluate Formula the order calculation 


Evaluate formula step two 

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

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).

Evaluate formula step three 

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.

Evaluate formula step four

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}] 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 eg. value 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.

Evaluate formula step five


Evaluate formula step six

Pay attention, you may need as a separator of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.