Nest an IF Formula

Detrie Zacharias

With the help of the community, I have two formulas that I would like to combine into one if possible.

Formula 1

=IF(Ancestors@row = 0, COUNTIF(Ancestors$1:Ancestors@row, Ancestors@row = 0), "")

Formula 2

=IF(LEN(RowID@row) = 1, "000" + RowID@row, IF(LEN(RowID@row) = 2, "00" + RowID@row, IF(LEN(RowID@row) = 3, "0" + RowID@row, RowID@row)))

My failed try

This throws an #INCORRECT ARGUMENT SET error

=IF(ANCESTORS@row = 0, COUNTIF(ANCESTORS$1:ANCESTORS@row, ANCESTORS@row = 0, "", (IF(LEN(RowID@row) = 1, "000" + RowID@row, IF(LEN(RowID@row) = 2, "00" + RowID@row, IF(LEN(RowID@row) = 3, "0" + RowID@row, RowID@row))))))

Paul Newcome

Taking another gander, this may be what you are looking for...

=IF(Ancestors@row = 0, IF(LEN(COUNTIF(Ancestors$1:Ancestors@row, Ancestors@row = 0)) = 1, "000" + COUNTIF(Ancestors$1:Ancestors@row, Ancestors@row = 0), IF(LEN(COUNTIF(Ancestors$1:Ancestors@row, Ancestors@row = 0)) = 2, "00" + COUNTIF(Ancestors$1:Ancestors@row, Ancestors@row = 0), IF(LEN(COUNTIF(Ancestors$1:Ancestors@row, Ancestors@row = 0)) = 3, "0" + COUNTIF(Ancestors$1:Ancestors@row, Ancestors@row = 0), COUNTIF(Ancestors$1:Ancestors@row, Ancestors@row = 0))), "")

Paul Newcome

How exactly are you trying to combine them? What is the desired end result?

Andrée Starå

Hi Stavros,

Have you tried to change the order of the formulas IF parts?

I hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Paul Newcome

Taking another gander, this may be what you are looking for...

=IF(Ancestors@row = 0, IF(LEN(COUNTIF(Ancestors$1:Ancestors@row, Ancestors@row = 0)) = 1, "000" + COUNTIF(Ancestors$1:Ancestors@row, Ancestors@row = 0), IF(LEN(COUNTIF(Ancestors$1:Ancestors@row, Ancestors@row = 0)) = 2, "00" + COUNTIF(Ancestors$1:Ancestors@row, Ancestors@row = 0), IF(LEN(COUNTIF(Ancestors$1:Ancestors@row, Ancestors@row = 0)) = 3, "0" + COUNTIF(Ancestors$1:Ancestors@row, Ancestors@row = 0), COUNTIF(Ancestors$1:Ancestors@row, Ancestors@row = 0))), "")

Detrie Zacharias

Thanks all... With a little bit of manipulation, I got this to work as expected

Andrée Starå

Excellent!

Happy to help!

Paul Newcome

Excellent! Happy to help! 👍️

Paul Newcome

Excellent! Happy to help! 👍️