Formula to return 100% not 0%

[kcroft88]

Hi, I have a formula that is basically looking at another cell and returning the % number from that cell:

=INDEX('Yield from PPM-2001'!$C$4:$L$40,MATCH($A14,'Yield from PPM-2001'!$A$4:$A$38,0),MATCH(E$3,'Yield from PPM-2001'!$C$3:$L$3,0))

If the cell it looks at has a yield of 0% then it obviously returns 0%. is there something I can add to the above formula for it to return 100% instead of 0%?

Regards,

 

[steve the fish]

Question first. If it returned 20% what would you want? Also is it possible to return greater than 100%?

 

[joeu2004]

Perhaps:

=IFERROR(1/(1/INDEX('Yield from PPM-2001'!$C$4:$L$40,MATCH($A14,'Yield from PPM-2001'!$A$4:$A$38,0),MATCH(E$3,'Yield from PPM-2001'!$C$3:$L$3,0))), 100%)

formatted as Percentage with no decimal places.

I don't know your data. But what if it actually contains 0%, which is not intended to be interpreted as 100%?

The point is: perhaps it is better to fix the table in the 'Yield from PPM-2001' worksheet so that it contains 100% wherever it is intended.

If you cannot figure out how to do that, post the formula(s) in the table in the 'Yield from PPM-2001' worksheet.

Caveat: The double inversion 1/(1/...) can create an infinitesimal difference. For example, if the table intersection contains the constant 87%, the formula results in 87% plus about 1.11E-16. These infinitesimal differences, which Excel does not display because formatting is limited to 15 significant digits, might cause some lookup errors. For example, MATCH(1/(1/...),{0.87},0) returns #N/A when the table intersection contains the constant 87%.

 

[joeu2004]

Too late to edit....

Caveat: The double inversion 1/(1/...) can create an infinitesimal difference. For example, if the table intersection contains the constant 87%, the formula results in 87% plus about 1.11E-16. These infinitesimal differences, which Excel does not display because formatting is limited to 15 significant digits, might cause some lookup errors. For example,
MATCH(1/(1/...),{0.87},0) returns #N/A when the table intersection contains the constant 87%.

Work-around: Change the expression to ROUND(1/(1/...),4) to explicitly round to 2 percentage decimal places, for example. Note that 87.34% is 0.8734. Change 4 to whatever precision that you want.

 

[kcroft88]

Thanks Joeu2004 it worked a treat! I almost had it looking at your solution as I almost did the Iferror the same except my last number was 100 WITHOUT the % symbol on the end. for my knowledge what does the 1/(1 actually mean / do?

Chees again

 

[joeu2004]

1/x produces an Excel error (#DIV/0) when "x" is exactly zero. Consequently, IFERROR returns its second parameter (100%).

1/(1/x) = x when "x" is not zero. Since there is no error, IFERROR returns its first parameter, "x". It's an algebraic trick.

The more traditional way to accomplish this is:

=IF(INDEX(...)=0, 100%, INDEX(...))

Off course, that makes for a very long formula, in your case; and it requires doing the calculation twice.

But an advantage is: we can allow for when INDEX(...) returns a value that appears to be zero, but it is not exactly zero. For example:

=IF(ROUND(INDEX(...),4)=0, 100%, INDEX(...))

returns 100% when INDEX(...) appears to be zero when rounded to 2 percentage decimal places, which might be how your cell is formatted.