Specific IF function

[YourHero]

So I’ve got a variation on an often asked question. I’ve got a sheet where I need one numerical column to match another BUT, while one column uses 0-9 values, the other needs 0 and 1 to return a value of 1 while the remaining values are exact matches.

Column i needs to mirror Column e, except I need the zero in E5 to appear as a one in I5. The formulas I’ve tried either turn blank cells into zero (messes up the math for adjacent cells) or give me an error 504 or a #N/A message

I’ve been trying to make this work for far too long, currently the only thing that works reliably is if I manually input values into column i

Let’s see what can be done.

[karolus]

Start with:

=IF(E5=0;1;E5)

and pull down.

[YourHero]

That’s where I started, but a blank cell in E becomes a 1 in i, this is problematic.

[karolus]

so use:

=IF(ISBLANK(E5);"";SUM(E5=0;E5))

[YourHero]

You’re further highlighting my challenge. Trickle down errors. Now the empty fields are being tallied by adjacent formulas (column h, in this case) as if they were zeros.

[karolus]

please upload a real example.ods instead stupid screenshots!

[karolus]

@YourHero

See how this works, data input

it does what you ask for!

[YourHero]

No, column H displays as a zero when column i is empty. This zero is tallied as an incident and displayed in H4, skewing the data in F2 . Ideally an empty (except for the formula) column i should have no impact on columns F G and H.

[ady]

You are (keep) adding conditions to what exactly you are after. We cannot know what is “wrong” unless you express the desired result, or IOW the full set of conditions to be met.

Based on the partial info, I would throw out there one possible suggestion for cell H5 (that should then be filled down):

=IF(LEN(I5)>0;IFS(I5=0;"";I5<7;"";I5>6;0);"")

It is just one “quick and dirty” potential formula; I don’t really know whether it really resolves the case for you, and whether it can be improved (although, probably, it can be).

[YourHero]

Other than having to input manually to column I, the sheet functions perfectly.

[ady]

Then combine both suggestions.

When you manually introduced the values in column I, then some cells in that column are really empty/blank. By adding the formula =IF(ISBLANK(E5);"";SUM(E5=0;E5)) you no longer have empty/blank cells, so the resulting column H takes the "" result in column I and adds the “extra” zeroes. With the suggested =IF(LEN(I5)>0;IFS(I5=0;"";I5<7;"";I5>6;0);"") for cell H5 (and filling down), the prior results for column H should be restored.

[YourHero]

I was working too hard…simple resolution. column H =IFS(I5=1,1,I5=0,1,I5>1,"")
Column I = Column E. Just had to get column H to treat the Zero as a 1.

[YourHero]

Ok, here it is, everything does what I need. Was a combination of the suggestions and a few little tweaks to my way of thinking. Thanks for the input, apologies for my foggy presentation of the issue.

Sample - Final Form.ods (16.5 KB)