Forum Discussion
Solved
Nested If function with multiple criteria
Can someone help me resolve an If formula in excel 2010 please? I have a workbook with multiple worksheets. I'm trying to copy the cell value that is not blank from one worksheet called Payment Calc to another with my If statement. My formula in the destination cell reads;
=IF('Payment Calc'!B12<>"",'Payment Calc'!B12,IF('Payment Calc'!B27<>"",'Payment Calc'!B27,IF('Payment Calc'!D19<>"",'Payment Calc'!D19,'Payment Calc'!D33))).
Unfortunately, when B12, B27 and D19 are all blank, D33 will not populate in the cell with the formula. The first three cells work fine when one of them has a value. I've researched the problem accross multiple resources and have been unable to resolve on my own. Thanks in advance if someone can help with a solution.
I guess the issue is with D19 cell. MAX() of three blank cells (with "") returns 0 (zero), not blank. Perhaps you have custom format for the cell and it shows you empty cell, not blank.
I copied your D19 value into another workbook, it gives zero.
Hi
the formula appears to work fine when I replicate it.
When you say the other cells are "blank" can you check by doing
=ISBLANK(B12)
=ISBLANK(B27)
= ISBLANK(D19)
They should all show TRUE if truly blank
They are all have formulas and they all read as False. That said, the first three conditions do result in their values being copied into the destination cell when they have data. For some reason the final condition will not populate even if I adjust the formula. So strange.
I've attached my workbook in hopes you can replicate the issue. The destination cell with the If statement in question is in D10 on the Credit Report tab. The Payment Calc tab has the four cells the If statement is targeting. Two of the cells have imbedded If formulas and the other two have imbedded Max formulas.
Hi,
In your file in formula for Credit Report D10 is just D33 if all blanks. It shall be 'Payment Calc'!D33 - as in your first message
