• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 237
  • Last Modified:

Excel Nested IF OR Statement

Hi! I'm trying to create a nested IF/OR statement and it works for the first 2 conditions, but for the 3rd, if gives me "FALSE" instead of the value. Can anyone spot my error? I'm going crazy trying to find it....
=IF(IF(OR(AG1=1,AG1=4,AG1=7,AG1=10),IF(ISBLANK(Forecast!E2),"",IF(Forecast!E2>-100000,Forecast!E2,"")),
IF(OR(AG1=2,AG1=5,AG1=8,AG1=11),IF(ISBLANK(Forecast!F2),"",IF(Forecast!F2>-100000,Forecast!F2,"")))),
IF(OR(AG1=3,AG1=6,AG1=9,AG1=12),IF(ISBLANK(Forecast!G2),"",IF(Forecast!G2>-100000,Forecast!G2,""))))
0
McQMom
Asked:
McQMom
1 Solution
 
Patrick MatthewsCommented:
McQMom,

I think will be easier if you describe what the logic should be using sentences.

A sample file (with any sensitive data removed/obfuscated) would be helpful too :)

Patrick
0
 
McQMomAuthor Commented:
Let me try to do that. Should be just a couple of minutes...
0
 
nutschCommented:
Try

=IF(ISBLANK(Forecast!E2),"",IF(MOD(AG1,3)=1,IF(Forecast!E2>-100000,Forecast!E2,""),IF(MOD(AG1,3)=2,IF(Forecast!F2>-100000,Forecast!F2,""),IF(MOD(AG1,3)=0,IF(Forecast!G2>-100000,Forecast!G2,"")))))

Thomas
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
McQMomAuthor Commented:
Here you go. This will be where our quarterly forecasts will be located. It will be updated and changed each month/qtr. Experts-Exchange-forecast-templa.xlsm
0
 
nutschCommented:
Or

=IF(ISBLANK(Forecast!E2),"",IF(OFFSET(Forecast!$E$2,0,MOD(MOD(AG1,3)-1,3),1,1)>-100000,OFFSET(Forecast!$E$2,0,MOD(MOD(AG1,3)-1,3),1,1),""))

even though I think the mod(mod( structure can be improved

Thomas
0
 
McQMomAuthor Commented:
Thanks - let me give it a try
0
 
barry houdiniCommented:
Hello Thomas (not for points)
I think you can replace this part

=MOD(MOD(AG1,3)-1,3)

with

=MOD(AG1-1,3)
0
 
byundtCommented:
Brute force formula:
=IF(AND(OR(AG1={1,4,7,10}),Forecast!E2<>"",Forecast!E2>-100000),Forecast!E2,
IF(AND(OR(AG1={2,5,8,11}),Forecast!F2<>"",Forecast!F2>-100000),Forecast!F2,
IF(AND(OR(AG1={3,6,9,12}),Forecast!G2<>"",Forecast!G2>-100000),Forecast!G2,"")))
0
 
McQMomAuthor Commented:
Perfect! And much cleaner! Thank you.....
0
 
nutschCommented:
With barry and Brad's input, I'd finalize with

=IF(AND(OFFSET(Forecast!$E$2,0,MOD(AG1-1,3))<>"",OFFSET(Forecast!$E$2,0,MOD(AG1-1,3))>-100000),OFFSET(Forecast!$E$2,0,MOD(AG1-1,3)),"")

Thomas
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now