Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 40
Default Nested IF AND returning #VALUE!

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default Nested IF AND returning #VALUE!

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
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default Nested IF AND returning #VALUE!

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
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 40
Default Nested IF AND returning #VALUE!

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
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default Nested IF AND returning #VALUE!

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


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,182
Default Nested IF AND returning #VALUE!

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

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default Nested IF AND returning #VALUE!

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
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 40
Default Nested IF AND returning #VALUE!

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
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default Nested IF AND returning #VALUE!

Hi Paul,

Am Fri, 8 Jul 2016 10:13:15 -0700 (PDT) schrieb Paul Doucette:

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.


the INDEX describes a range and MATCH will find the row number. The
different result of --ER18&--ET18 are into the array in the MATCH
formula.


Regards
Claus B.
--
Windows10
Office 2016
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default Nested IF AND returning #VALUE!

Hi Paul,

Am Fri, 8 Jul 2016 10:13:15 -0700 (PDT) schrieb Paul Doucette:

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.


from the beginning on:
The formula for INDEX is
=INDEX(range, row, column)
So you only have one column column is not needed.
You have to find the row of your range. Therefore I converted your
boolean values with the double minus to numeric values.
--TRUE=1
--FALSE=0
Then I combined both cells with the ampersand
--TRUE&--TRUE="11"
--FALSE&--TRUE="01"
and so on.
Using the ampersand the result becomes text. That is why it is in quotes
into the array.
=MATCH(---ER18&--ET18,{"11","01","10","00"},0)
looks for your combination of your cells
If you have in ER18 TRUE and in ET18 FALSE you get "10".
MATCH finds this value in third place of the array.
So your expected output is the third row of your range which is ER24.


Regards
Claus B.
--
Windows10
Office 2016


  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,182
Default Nested IF AND returning #VALUE!

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

  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 40
Default Nested IF AND returning #VALUE!

On Friday, July 8, 2016 at 2:22:49 PM UTC-4, Claus Busch wrote:
Hi Paul,

Am Fri, 8 Jul 2016 10:13:15 -0700 (PDT) schrieb Paul Doucette:

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.


from the beginning on:
The formula for INDEX is
=INDEX(range, row, column)
So you only have one column column is not needed.
You have to find the row of your range. Therefore I converted your
boolean values with the double minus to numeric values.
--TRUE=1
--FALSE=0
Then I combined both cells with the ampersand
--TRUE&--TRUE="11"
--FALSE&--TRUE="01"
and so on.
Using the ampersand the result becomes text. That is why it is in quotes
into the array.
=MATCH(---ER18&--ET18,{"11","01","10","00"},0)
looks for your combination of your cells
If you have in ER18 TRUE and in ET18 FALSE you get "10".
MATCH finds this value in third place of the array.
So your expected output is the third row of your range which is ER24.


Regards
Claus B.
--
Windows10
Office 2016


Claus; Thank you for explaining. I would never have figured it out on my own. Again, genius... or at least approaching it. I will attempt Index and Match rather than If's next time!!! I greatly appreciate your time and kindness!!!
Best, Paul
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Nested If not returning correct values murkaboris Excel Discussion (Misc queries) 5 April 16th 09 06:03 PM
nested IF(OR Vlookup returning zero instead of value in target cel ncjefffl Excel Worksheet Functions 7 October 31st 08 10:20 PM
Nested If statements returning a sum calculation arkage Excel Worksheet Functions 1 April 20th 07 08:59 PM
Nested if returning error 1004 tom[_7_] Excel Programming 2 February 2nd 06 09:50 PM
Nested "if" not returning expected value Michael E W Excel Worksheet Functions 4 September 5th 05 04:50 AM


All times are GMT +1. The time now is 11:44 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"