• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Explore the formula

sachar

Member
Dear All,

with reference to the attachment applied the following formula in Column "AF" to make KPI Distribution interactive Chart.

=IFERROR((FIND($AF$18,$O$20:$O$119)>0)*($P$20:$T$119),NA())

Please can you explore the above formula in details?
 

Attachments

  • our-kpi-dashboard-step5_find formula.xlsx
    99.2 KB · Views: 8
Hi ,

When ever you come across a formula using the IFERROR function , the first step is to eliminate the IFERROR function.

The IFERROR function in this case merely inserts the NA() error function result , which is #N/A , when ever the result of the actual formula is an error value.

The actual formula is :

(FIND($AF$18,$O$20:$O$119)>0)*($P$20:$T$119)

The FIND function is normally used as follows :

=FIND(find_text , within_text)

What this will do is return either the position within a cell contents ( within_text ) , where the looked up value ( find_text ) , is found or the #VALUE! error result if the cell contents do not contain the looked up value.

As an example , consider that $AF$18 contains the digit 1 ; the cell $O$20 contains the text Product Name 13.

The formula :

=FIND($AF$18 , $O$20)

will return the value 14 , signifying that the digit 1 occurs in the 14th position in the cell $O$20.

Suppose instead , we use the formula :

=FIND($AF$18 , $O$24)

will return the error value #VALUE! , signifying that the digit 1 does not occur anywhere in the cell $O$24 (Product Name 75).

Now , because the second parameter of the FIND function is not a single cell , but a range of cells , the result of this will be an array of results , which will contain either numbers or the #VALUE! error value.

Comparing this to 0 , using the check >0 will result in an array of TRUE or #VALUE! values ; this is because a numeric result from the FIND function will return TRUE while an error result will return the same #VALUE! error.

When used in an arithmetic operation , as in the multiplication by ($P$20:$T$119) , TRUE is replaced by 1 ; the final result will be an array of values from the range $P$20:$T$119 where ever the TRUE or 1 value was present , and the #VALUE! error value elsewhere.

This error value is then replaced by the #N/A value through the IFERROR function.

Since this is a multi-cell array formula , the entire output range AF20 through AJ119 is first selected , and then the formula is typed in and entered by pressing CTRL SHIFT ENTER. Excel displays the same formula in all the cells within this range , but calculates the individual cell values correctly.

Narayan
 
Back
Top