Formula in table with referencing problem

[ddub25]

Why does this formula return #REF ! when entered into a table where the formula is referencing cells outside of the table and inside of the table?

=IF(H75="A",
IF(Z75<S6,K75*Z75,
IF(AND(Z75>(S6-1),Z75<(T6-1)),S75*Z75,
IF(AND(Z75>(T6-1),Z75<(U6-1)),T75*Z75,
IF(AND(Z75>(U6-1),Z75<(V6-1)),U75*Z75,
IF(AND(Z75>(V6-1),Z75<(W6-1)),V75*Z75,
IF(AND(Z75>(W6-1),Z75<(X6-1)),W75*Z75,
IF(AND(Z75>(X6-1),Z75<(Y6-1)),X75*Z75,
IF(Z75>=(Y6),W75*Z75,
0)))))))),0)

 

[Dryver14]

This line seems a little wrong, if Z75 what?

IF(Z75<s6,k75*z75,
IF(AND(Z75>(S6-1),Z75<(T6-1)),S75*Z75,</s6,k75*z75,

 

[ddub25]

The formula works fine outside of the table and returns the correct result so there is no issue with the syntax. When placed inside the table it returns #REF !. I assume it is a problem with the formula no being able to reference values outside of the table when the formula is placed inside the table.

 

[Dryver14]

Not too sure but it seems to have an issue referencing from inside what does the formula show in the table if you press F2

 

[ddub25]

Strangely it doesn't allow the formula to remain. I copy the formula from a cell outside of the table (highlighted and copied from the formula bar) and then paste into the cell inside the table (again pasting directly into the formula bar). As soon as I hit return the formula coverts to #REF !.

 

[Dryver14]

does it allow you to type the formula in?

 

[ddub25]

I have not tried, that would take me a long time, and I can't see why it would. The table just doesn't like the formula whether copied and pasted or written by hand.

 

[ddub25]

I have had this problem many times before, when trying to enter a formula into a table. The formula works fine when placed outside of the table, but as soon as I place it inside the table, it can't reference those values outside of the table. It's a long time since I have written formulas in Excel and can't remember how I solved the problem, but it was done by changing the formula to enable referencing of values outside of a table. Something like wrapping the formula or parts of the formula in a SUM function or something like that. Tried that but cannot get it to work.

 

[Dryver14]

Sorry I can't help you more, I have tried to replicate it but can't, I have created table, made it a named ranged, what ever I do I can't make it fail.

Good luck with it.

 

[ddub25]

ok, thanks for your help and going to the trouble of testing it.