I have what I thought was a simple formula I wanted to return a value based on the condition (TRUE or FALSE) of two cells. They are either both true, both false, one is false and the other true, or one is true and the other false.

IF(AND(ER18=TRUE,ET18=TRUE),ER22),IF(AND(ER18=FALS E,ET18=TRUE),ER23),IF(AND(ER18=TRUE,ET18=FALSE),ER 24),IF(AND(ER18=FALSE,ET18=FALSE),ER25)

This is giving me a result of "#VALUE!"
I do this so seldom, that I'm sure I've got it wrong but, it's 1AM and I'm not seeing it.

Any help, very very much appreciated!
Paul

Hi Paul,

Am Thu, 7 Jul 2016 22:27:58 -0700 (PDT) schrieb Paul Doucette:

IF(AND(ER18=TRUE,ET18=TRUE),ER22),IF(AND(ER18=FALS E,ET18=TRUE),ER23),IF(AND(ER18=TRUE,ET18=FALSE),ER 24),IF(AND(ER18=FALSE,ET18=FALSE),ER25)

try:
=IF(AND(ER18=TRUE,ET18=TRUE),ER22,IF(AND(ER18=FALS E,ET18=TRUE),ER23,IF(AND(ER18=TRUE,ET18=FALSE),ER2 4,IF(AND(ER18=FALSE,ET18=FALSE),ER25))))


Regards
Claus B.
--
Windows10
Office 2016

Hi Paul,

Am Fri, 8 Jul 2016 09:42:55 +0200 schrieb Claus Busch:

IF(AND(ER18=TRUE,ET18=TRUE),ER22),IF(AND(ER18=FALS E,ET18=TRUE),ER23),IF(AND(ER18=TRUE,ET18=FALSE),ER 24),IF(AND(ER18=FALSE,ET18=FALSE),ER25)

try:
=IF(AND(ER18=TRUE,ET18=TRUE),ER22,IF(AND(ER18=FALS E,ET18=TRUE),ER23,IF(AND(ER18=TRUE,ET18=FALSE),ER2 4,IF(AND(ER18=FALSE,ET18=FALSE),ER25))))

a little shorter:
=INDEX(ER22:ER25,MATCH(--ER18&--ET18,{"11","01","10","00"},0))


Regards
Claus B.
--
Windows10
Office 2016

On Friday, July 8, 2016 at 4:09:03 AM UTC-4, Claus Busch wrote:
Hi Paul,

Am Fri, 8 Jul 2016 09:42:55 +0200 schrieb Claus Busch:

IF(AND(ER18=TRUE,ET18=TRUE),ER22),IF(AND(ER18=FALS E,ET18=TRUE),ER23),IF(AND(ER18=TRUE,ET18=FALSE),ER 24),IF(AND(ER18=FALSE,ET18=FALSE),ER25)

try:
=IF(AND(ER18=TRUE,ET18=TRUE),ER22,IF(AND(ER18=FALS E,ET18=TRUE),ER23,IF(AND(ER18=TRUE,ET18=FALSE),ER2 4,IF(AND(ER18=FALSE,ET18=FALSE),ER25))))

a little shorter:
=INDEX(ER22:ER25,MATCH(--ER18&--ET18,{"11","01","10","00"},0))


Regards
Claus B.
--
Windows10
Office 2016

So, you are like, a genius. :-) And you,ve made my day! THANK YOU!
Paul

Hi Paul,

Am Fri, 8 Jul 2016 05:27:58 -0700 (PDT) schrieb Paul Doucette:

So, you are like, a genius. :-) And you,ve made my day! THANK YOU!

you are welcome. Always glad to help.


Regards
Claus B.
--
Windows10
Office 2016

Wizard!!!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

Hi Garry,

Am Fri, 08 Jul 2016 09:22:59 -0400 schrieb GS:

Wizard!!!

I don't like nested IF formulas. Easily you lose track of things.
With the latest update of Excel 2016 there is the function IFS where you
can insert 127 arguments. What a nonsense.


Regards
Claus B.
--
Windows10
Office 2016

On Friday, July 8, 2016 at 10:47:48 AM UTC-4, Claus Busch wrote:
Hi Garry,

Am Fri, 08 Jul 2016 09:22:59 -0400 schrieb GS:

Wizard!!!

I don't like nested IF formulas. Easily you lose track of things.
With the latest update of Excel 2016 there is the function IFS where you
can insert 127 arguments. What a nonsense.


Regards
Claus B.
--
Windows10
Office 2016

127?!? Agreed. Too convoluted. I need to learn how to use the Index and Match functions. It appears much more straightforward... but I will have to do some studying. Right now I only know that it works, not how.
Many thanks, Paul

Hi Garry,

Am Fri, 08 Jul 2016 09:22:59 -0400 schrieb GS:

Wizard!!!

I don't like nested IF formulas. Easily you lose track of things.
With the latest update of Excel 2016 there is the function IFS where
you can insert 127 arguments. What a nonsense.


Regards
Claus B.

I totally agree! I've even taken to using INDEX/MATCH in VBA because it
obviates extra loops...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus