Nesting IF (1 Viewer)

[MGumbrell]

I wish to nest more than 8 If Statements 3 times ie.

Range A1 needs to test that the value 5:-

01 - Does not appear in a Row of 9 columns - say B1:B9
02 - Does not appear in a Column of 9 rows - Say A2:A10
03 - Does not appear in an array of a 3x3 grid - Say an array named "grp1"

Is this at all possible?

The result is, if it should find a value of 5 in these locations that the result is a blank cell in A1.

Regards, Matt

 

[Sergeant]

You are limited to 7-deep on the if's in one cell. You can use the Match function to check for the presence of a value in a row OR column range of cells. I can't think right now how you might check a 2-d range of cells???
You can also build the ifs in segments in different cells and then unite the product in another cell.

 

[shades]

Technically, you can nest up to 49 IF's in Excel (although you are limited to 1,024 characters in a formula), using name formulas for a set of nested IFs.

BUT, it is a nightmare! I developed a solution for someone which was nested up to 13 IFs, and it has worked for that person flawlessly for 3 years for a critical company-wide report. But the lookup table is far better, more convenient, easier to trouble-shoot, and can be expanded as far as the need.
________
Easy vape

 

[Ian Mac]

Technically, you can nest up to 49 IF's in Excel (although you are limited to 1,024 characters in a formula), using name formulas for a set of nested IFs.

BUT, it is a nightmare! I developed a solution for someone which was nested up to 13 IFs, and it has worked for that person flawlessly for 3 years for a critical company-wide report. But the lookup table is far better, more convenient, easier to trouble-shoot, and can be expanded as far as the need.

Technially you are not correct, you can only nest 7 IFs

=IF(etc,IF(etc,IF(etc and so on

Not:

=IF(etc,IF(etc,TRUE,FALSE),IF(etc,TRUE,FALSE))
which is only 2 nested IFs in each part of the formula.

However, to the original question.

Can you please expand a little on your specific requirement as, Shades does correctly say a lookup table may be what you need.

Cheers,

 

[MGumbrell]

I would be more than happy to use a lookup table but I was struggling with needing to check three ranges to determin the value of a cell.

i.e

If a number from 1 - 9 does not appear in 1 of 9 cells in a row &
If a number from 1 - 9 does not appear in 1 of 9 cells in a colum &
If a number from 1 - 9 does not appear in a range of cells

then

the numbers from 1 to 9 that do not appear will be indiacted in another cell or cells.

I hope that this explaination is clear.

Thank you, Matt

 

[Sergeant]

Sounds like SuDoku???
Why don't you do it in VBA?
If it is SuDoku, check these links...
http://www.andypope.info/fun/sudoku.htm
http://www.dicks-blog.com/

 

[MGumbrell]

Thank you

I had thought of VBA but its going to take along time to work out the code generated on the links.

Look forward to the challenge though.

Thank you.