nested if gives #value

[KDS14589]

I'm been working on a nested formula, with the other parts working but after a week I've worked out my problem is here and it still gives me #Value . After a full day trying several solutions I'm about to 'scream'.

=IF(OR(AND(AG18="bank",COUNTIF(command.banktype,AN18)>0),AND(AG18="credit",COUNTIF(command.credittype,AN18)>0),AND(AG18="debt",COUNTIF(command.debttype,AN18)>0),AND(AG18="merchant",COUNTIF(command.merchanttype,AN18)>0)),D18&" ( "&AG18&" : "&AN18&" )","wrong type"),IF(AND(OR(AG18="bank",AG18="credit",AG18="debt"),S18>0),D18&" - "&S18&" ( "&AG18&" : "&AN18&" )","needs an account id.")

if anyone can help I'll incorporate it into my final formula.

 

[Oaktree]

You're getting a #VALUE error because of the comma after the ")" after "wrong type".

"wrong type") closes off the initial IF statement, so you effectively have =IF(...),IF(...) and Excel doesn't know what you mean.

FWIW, I find it to be helpful if I use line breaks to line up long formulas like those. When you're editing the formula in the formula bar, if you press ALT+ENTER, you can continue your formula on the next line. It doesn't change its functionality, it just makes it a little easier to follow.

=IF(
OR(
  AND(AG18="bank",COUNTIF(command.banktype,AN18)>0),
  AND(AG18="credit",COUNTIF(command.credittype,AN18)>0),
  AND(AG18="debt",COUNTIF(command.debttype,AN18)>0),
  AND(AG18="merchant",COUNTIF(command.merchanttype,AN18)>0)),
D18&" ( "&AG18&" : "&AN18&" )",
"wrong type"),
IF(AND(OR(AG18="bank",AG18="credit",AG18="debt"),S18>0),D18&" - "&S18&" ( "&AG18&" : "&AN18&" )","needs an account id.")

 

[DanteAmor]

The formula is not complete.
I do not see sense.


A cell cannot have two values ​​at the same time:


AND(AG18="credit",COUNTIF(command.credittype,AN18)>0),AND(AG18="debt",COUNTIF(command.debttype,AN18)>0)

You could explain with some examples what you want to do and what you expect from the result.

 

[Oaktree]

The formula is not complete.
I do not see sense.


A cell cannot have two values ​​at the same time:


AND(AG18="credit",COUNTIF(command.credittype,AN18)>0),AND(AG18="debt",COUNTIF(command.debttype,AN18)>0)

You could explain with some examples what you want to do and what you expect from the result.

Hi, Dante. I think you overlooked the ")" before the second comma in the portion of the formula you quoted. With that, and as part of the OR function that precedes it, the test is effectively:

[AG18 = "Credit"] AND [COUNTIF(command.credittype...) > 0]

OR

[AG18 = "Debt"] AND [COUNTIF(command.debttype...) > 0]

I can't speak to whether that calculation is correct, but it's not checking for two different values of AG18 at the same time as your concern suggested.

 

[DanteAmor]

Hi, Dante. I think you overlooked the ")" before the second comma in the portion of the formula you quoted. With that, and as part of the OR function that precedes it, the test is effectively:

[AG18 = "Credit"] AND [COUNTIF(command.credittype...) > 0]

OR

[AG18 = "Debt"] AND [COUNTIF(command.debttype...) > 0]

I can't speak to whether that calculation is correct, but it's not checking for two different values of AG18 at the same time as your concern suggested.

Hi @Oaktree, You're right, I confused the formula

Maybe the first part of the formula can be simplified in this way, and with the explanation we can put the 2 parts together.

=IF(COUNTIF(INDIRECT(LOOKUP(AG18,{"bank","credit","debt","merchant"},
{"command.banktype","command.credittype","command.debttype","command.merchanttype"})),AN18),
D18&" ( "&AG18&" : "&AN18&" )","wrong type")

 

[KDS14589]

THANKS
I worked on it so long I overlooked simple things. Now I can work on it some more past this problem.
thanks again

 

[DanteAmor]

Youre welcome, thanks for the feedback.

 

[KDS14589]

Success on the finial formula

Thanks to everyone for their help in solving the difficult parts