Previous Topic Next Topic

Nestle fomula nightmare!!

Closed
Zaps11 - Updated on Mar 27, 2017 at 05:40 PM
 Zaps11 - Apr 8, 2017 at 05:52 PM
Sorry - fixing mini typo

Hello - any help!! Going bald ripping hair out trying to resolve!

I want to return a value "Q1" if a date falls between 2 dates, another value "Q2" if a date between 2 dates and so on an so forth until each date value is evaluated to see if it falls into the dates set for Q3 and Q4. But I'm stuck as WHEN one a logic is seen to be 'true' EXCEL stops evaluating if OTHER conditions are ALSO met. I'm stuck, can anyone help?

Sorry if this is basic question
Zaps11

2 responses

Answer 1 / 2
ibraomer Posts 2 Registration date Tuesday March 28, 2017 Status Member Last seen March 28, 2017
Mar 28, 2017 at 04:54 PM
Hi,
Please type the following:

in Column E


E F G
1/1/2016 3/31/2016 Q1
3/31/2016 6/30/2016 Q2
6/30/2016 9/30/2016 Q3
9/30/2016 12/31/2016 Q4


Type list of dates in column A
Then create this formula in Column B

=IF(AND(A2>=$E$2,A2<=$F$2),$G$2,IF(AND(A2>$E$3,A2<=$F$3),$G$3,IF(AND(A2>$E$4,A2<=$F$4),$G$4,$G$5)))

Note:
Cell A2 downwards has the list of dates.
Cell C2 downwards contains the formula

For more details please go ahead
Zaps11
Apr 8, 2017 at 05:52 PM
Thanks ibraomer - i was able to adopt the formula. Much appreciated. Nightmare over.
Zaps11
Apr 8, 2017 at 05:52 PM
adapt the formula!
Answer 2 / 2
ibraomer Posts 2 Registration date Tuesday March 28, 2017 Status Member Last seen March 28, 2017
Mar 28, 2017 at 05:22 PM
View the attached excel file at the link below

https://www.dropbox.com/s/zycjgtypblf5u0y/NESTED_IF.xlsx?dl=0