nested if gives #value


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.")


Use ctrl shift enter
And send me file 
My excel whatsapp group link
After register your details 
Group link display
beepetark Nov 12, '19 at 7:03 am
The #VALUE! error appears when a value is not the expected type. This can occur when cells are left blank, when a function that is expecting a number is given a text value, and when dates are evaluated as text.

As we do not know what you have formated the cell as or what any of the refenced cells contain or their type I suspect you will not get an answer here without providing more information. Have you tried the FX assistant beside the formula bar?
k1w1sm (rep: 197) Nov 12, '19 at 5:58 pm
I did not understood single word
beepetark Nov 23, '19 at 1:47 pm
Add to Discussion


Selected Answer

The formula can't be reconstructed without access to the worksheet on which it is supposed to work but the following term in it is definitely wrong.

D18&" - "&S18&" ( "&AG18&" : "&AN18&" )"

I can't guess what D18, S18, AG18 or AN18 contain but if both D18 and S18 are numbers then D18&" - "&S18 should beD18 - S18 and the following " ( "&AG18&" : "&AN18&" )" can't be constructed as valid syntax, like 100 - 50(P13:O26).

Starting with the assumption that " ( "&AG18&" : "&AN18&" )" is a range, AG18 and AN18 should hold cell addresses, resulting in something like (P13:Q26). So, perhaps S18 holds a function name like "SUM". Perhaps you are trying to construct something like SUM(P13:Q26). If this is so I must tell you that, to the best of my knowledge, it isn't possible to take the function name from a cell value. It needs to be in the formula and then you can assign the range using INDIRECT(). In the example below AG18 and N18 would hold text like "P13" and "Q13".

=D18-SUM(INDIRECT(AG18&" : "&AN18))

As a useful alternative you might use the ADDRESS function to construct the range address.


Here the numbers 16 and 17 represent the columns P and Q while AG18 and AN18 hold row numbers. This construct would enable you to calculate the start and end rows using functions. The column numbers could also be determined by some calculation if that is helpful in your taks.



all the cells contain text
after MANY attempts i got it to work
THANKS for your suggestion though
KDS1489 (rep: 6) Nov 16, '19 at 8:48 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login