IF statement with returning value > if value blank, place a zero
Hi, I am trying to fill in a row with numbers from a pivot sheet I created. If the pivot value is blank, I need a zero to be placed in the field the blank value is returning to:
IF(NOT(ISBLANK(INDEX({Pivot sheet Active value range}, MATCH([Identity ID]@row, {Pivot sheet Identity ID range}, 0)))), "0").
This formula is bringing back a blank value when it has a value in the pivot sheet,
and zero for all others. I need to have the "Active value" on the pivot sheet to
show, and place a zero if nothing is there.
I've tried it as IF(ISBLANK) and IF(NOT(ISBLANK) and can't get it to work. I don't see any settings on the pivot workflow that forces zeros into all blank fields, so the formula seems to be the only option.
Does anyone see the error in my formula or know of a way to have zeros appear on the pivot sheet when no value is reported?
Best Answer

The way your formula is currently written, you are essentially saying "If the output of the INDEX/MATCH is not blank then output a zero."
What you want to say is "it the output of the INDEX/MATCH is not blank, then output the INDEX/MATCH, otherwise output zero."
=IF(NOT(ISBLANK(INDEX({Pivot sheet Active value range}, MATCH([Identity ID]@row, {Pivot sheet Identity ID range}, 0)))), INDEX({Pivot sheet Active value range}, MATCH([Identity ID]@row, {Pivot sheet Identity ID range}, 0)), "0")
Answers

The way your formula is currently written, you are essentially saying "If the output of the INDEX/MATCH is not blank then output a zero."
What you want to say is "it the output of the INDEX/MATCH is not blank, then output the INDEX/MATCH, otherwise output zero."
=IF(NOT(ISBLANK(INDEX({Pivot sheet Active value range}, MATCH([Identity ID]@row, {Pivot sheet Identity ID range}, 0)))), INDEX({Pivot sheet Active value range}, MATCH([Identity ID]@row, {Pivot sheet Identity ID range}, 0)), "0")

Thank you! I couldn't figure out why that was happening  it worked!

Help Article Resources
Categories
Check out the Formula Handbook template!