Nested IF formula to return a blank cell if value is unfound

Question

I need some help with a formula I am using to pull data from another worksheet that has a formula in it. Once the source cell has a value assigned my nested if formula works correctly however it is returning a value when the source cell is blank (still as the formula in it waiting to get the data).

Using the formula below returns a value that is not correct instead of a blank cell:

=IF('Training Wrap Up'!G5=100%,5,IF('Training Wrap Up'!G5>89%,4,IF
    ('Training Wrap Up'!G5>79%,3,IF('Training Wrap Up'!G5>69%,2,IF
    ('Training Wrap Up'!G5<69%,1,"")))))

Using the following formula returns a blank cell until the source cell has data then returns the true value but I have a wider range than just the "=" formula below:

=IF('Training Wrap Up'!G5=100%,5,IF('Training Wrap Up'!G5=90%,4,IF
    ('Training Wrap Up'!G5=80%,3,IF('Training Wrap Up'!G5=70%,2,IF
    ('Training Wrap Up'!B6<69%,1,"")))))

I would like to have a blank cell until G5 has a value showing.

pnuts pnuts · Accepted Answer · 2015-10-13T04:40:02

You might wrap it in a condition that tests whether G5 is blank before proceeding, say:

=IF(ISBLANK('Training Wrap Up'!G5),"",IF('Training Wrap Up'!G5=100%,5,IF('Training Wrap Up'!G5>89%,4,IF('Training Wrap Up'!G5>79%,3,IF('Training Wrap Up'!G5>69%,2,IF('Training Wrap Up'!G5<69%,1,""))))))

As your formula, this also returns nothing when G5 = 69%. I'm guessing that may not be the intention and offer a shorter version that returns 1 for 69%:

=IF('Training Wrap Up'!G5="","",LOOKUP('Training Wrap Up'!G5,{0,0.6901,0.7901,0.8901,1},{1,2,3,4,5}))

Note that LOOKUP is working its way through the possibilities and to avoid say 89% returning 4 I have made that break point 89.01% - to return 3 as your formula. If you want to use the shorter version you may need to adjust by an even smaller amount than 0.01% - or might get away with fewer decimal places.

Nested IF formula to return a blank cell if value is unfound

1 Answers