Excel syntax for 1 use of REPT(): |
|---|
=REPT(text, number_times) |
UK Pound Sterling is back, this time in cell B11 to illustrate how REPT() can be used to rewrite nested IF()s.
Unlike the other functions and formulas shown here to rewrite nested IF()s, REPT() is a text processing function: it repeats a string of text some whole number of times (including zero times).
Usually number_times is either hard-coded into the function or it refers to a cell which has the number of times to repeat. Sometimes number_times is entered in the form of a calculation.
A number is required by number_times, and that number can also be produced by… Boolean Logic.
The comparison (B11=$G$3) would be used in place of number_times. Here is how 1 use of REPT() would look:
| // (“GBP”=$G$3) –> (“GBP”=”AUD”) –> FALSE –> 0 // REPT($H$3,0) –> REPT(1.368300,0) –> [nothing to show] |
|---|
=REPT($H$3,(B11=$G$3)) |
GBP isn’t equal to AUD, so this comparison failed and produced a 0. This happens 6 more times.
In the case of REPT(), nothing is repeated except a string of zero characters in length. Nothing was generated.
The winning comparison takes place with $G$7:
| // (“GBP”=$G$7) –> (“GBP”=”GBP”) –> TRUE –> 1 // REPT($H$7,1) –> REPT(1.368300,1) –> 1.368300 |
|---|
| =REPT($H$7,(B11=$G$7)) |
Similar to the Boolean Logic rewrite of nested IF()s, the REPT() rewrite strings together a series of building blocks. While the building blocks for the Boolean Logic rewrite are connected by “+” characters, the building blocks for the REPT() rewrite are connected by “&” characters. The “&” characters are used to concatenate separate strings into a larger string. Since only one building block gets repeated 1 time while all others have no length, the REPT() rewrite ends up with the correct rate for GBP, 1.368300.
| Note: |
|---|
While the correct rate is found for GBP, 1.368300, it is rendered as a text string. To produce a numeric value, multiply that output from the chain of REPT() calls by 1. |
Since REPT() returns text, its output needs to be converted to a number. Multiplying this output by 1 forces that conversion to a number.
This is what the rewritten nested IF()— multiplied by 1– looks like using REPT():
Final formula for Rewrite #8 (REPT()) |
|---|
=1 * ( REPT($H$3,(B11=$G$3)) & REPT($H$4,(B11=$G$4)) & REPT($H$5,(B11=$G$5)) & REPT($H$6,(B11=$G$6)) & REPT($H$7,(B11=$G$7)) & REPT($H$8,(B11=$G$8)) & REPT($H$9,(B11=$G$9)) & REPT($H$10,(B11=$G$10)) ) |
This excerpt is taken from 8 Ways To Rewrite nested IF() Functions at Magna Carta XLS Communications. Each of the 8 nested IF() rewrites from that post will be featured in its own post here.
Other posts will be migrated to this blog before I begin writing posts natively here.