+ Reply to Thread
Results 1 to 18 of 18

Nested If Conditions

  1. #1
    Forum Contributor
    Join Date
    07-15-2013
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    129

    Nested If Conditions

    Hi everyone,

    I'm trying to write an excel formula using columns that have blanks and N/A values. Here's the formula: =IF(AND(SUM(AX3:AX12)=0,Calc!AL13<0.33,Calc!AL13<Calc!AL4),1,"")

    It obviously doesn't work because of the error values, so I tried this formula to account for that : =IF(AND(SUM(AX3:AX12)=0,Calc!AL13<0.33,Calc!AL13<Calc!AL4),1,""),IF(ISERROR(IF(AND(SUM(AX3:AX12)=0,Calc!AL13<0.33,Calc!AL13<Calc!AL4),1,"")),IF(AND(SUM(AX3:AX12)=0,Calc!AL13<0.33,Calc!AL13<Calc!AL4),1,""),1)

    But I'm getting a #VALUE error. I feel like I've tried everything! Can someone help me solve this?

  2. #2
    Forum Contributor
    Join Date
    08-15-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    245

    Re: Nested If Conditions

    I'm not positive because I don't have a spreadsheet to test it on, but I believe the error stems from your AND function. If you can validate that this is the case, it would only be happening when not all of your AND conditions are met, so you could use IFERROR with "" as the result.
    <--- If you like the answer, press *.

  3. #3
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Nested If Conditions

    Not entirely sure what you want to do, but one issue is that you have two function 'blocks' separated by a comma. The first block is:
    IF(AND(SUM(AX3:AX12)=0,Calc!AL13<0.33,Calc!AL13<Calc!AL4),1,"")
    and the second is:
    IF(ISERROR(IF(AND(SUM(AX3:AX12)=0,Calc!AL13<0.33,Calc!AL13<Calc!AL4),1,"")),IF(AND(SUM(AX3:AX12)=0,Calc!AL13<0.33,Calc!AL13<Calc!AL4),1,""),1)

    So, it evaluates the first one and comes up with a 1 or a "", then it evaluates the second one, and comes up with a 1 or a "", so you are then trying to evaluate something like '1,""' which gives you the #Value error.

    It seems that the SUM with NAs is giving you a problem. You can try something like this:
    =SUMIF(AX3:AX12,">-9E99") to ignore the NAs
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

  4. #4
    Forum Contributor
    Join Date
    07-15-2013
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    129

    Re: Nested If Conditions

    Sorry, I'm not quite sure what you mean. I've attached the workbook--maybe you could check it out?
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    07-15-2013
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    129

    Re: Nested If Conditions

    The idea is to populate every 10 rows of AX:BH with a 1 if these conditions: Calc!AL13<0.33,Calc!AL13<Calc!AL4 are met in AL:AV

  6. #6
    Forum Contributor
    Join Date
    07-15-2013
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    129

    Re: Nested If Conditions

    Can you recommend a way to combine the two if functions so they're not "blocks"?

  7. #7
    Registered User
    Join Date
    07-19-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Nested If Conditions

    Quote Originally Posted by mkeys4 View Post
    The idea is to populate every 10 rows of AX:BH with a 1 if these conditions: Calc!AL13<0.33,Calc!AL13<Calc!AL4 are met in AL:AV
    Could you rephrase that more clearly please?

    In particular "populate every 10 rows" makes no sense to me.

    Also, it would be helpful (for me anyway) if you'd publish a more succinct example of the problem. Your huge spreadsheet and it's many seemingly off-page references slow my old PC way down!

  8. #8
    Forum Contributor
    Join Date
    07-15-2013
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    129

    Re: Nested If Conditions

    Sure. So the range is AX3:AX12, because I want excel to check if there is a one in the past 10 "days," and if there is, to output a 0, if there isn't, to output a 1.

    As there are now, the ones ans zeros in AX:BH just represent the data in AL:AW, so another way to think about it is, when excel find a number in AL:AW, the next 10 cells should be blank. Does that make sense?

  9. #9
    Forum Contributor
    Join Date
    07-15-2013
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    129

    Re: Nested If Conditions

    I just want a formula that says, "if the sum of the previous 10 cells is 0, then output a 1, if not, output a 0" and ignore the #N/As. But it seems impossible!

  10. #10
    Registered User
    Join Date
    07-19-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Nested If Conditions

    How about something of the form

    =IF(SUMIF(A1:J1,"<>#N/A")=0, "1", "0")

  11. #11
    Forum Contributor
    Join Date
    07-15-2013
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    129

    Re: Nested If Conditions

    The formula should check for 10 rows in the same column, so =IF(SUMIF(AX3:AX12,"<>#N/A")=0,1,0) worked. Any idea how to combine that formula with this: =IF(ISERROR(IF(AND(Calc!AL13<0.33,Calc!AL13<Calc!AL4),1,0)),"",(IF(AND(Calc!AL13<0.33,Calc!AL13<Calc!AL4),1,0)))

  12. #12
    Registered User
    Join Date
    07-19-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Nested If Conditions

    What do mean combine? You just said "I just want a formula that" and I gave it to you. ;-)

    What is that 2nd formula supposed to do? An explicit example (simple spreadsheet) would be wonderful.

  13. #13
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Nested If Conditions

    Going back to your 'base' formula which did not work because of the N/As:
    =IF(AND(SUM(AX3:AX12)=0,Calc!AL13<0.33,Calc!AL13<Calc!AL4),1,"")

    Assuming all you want is to have the SUM work:
    =IF(AND(SUMIF(AX3:AX12,"<>#N/A")=0,Calc!AL13<0.33,Calc!AL13<Calc!AL4),1,"")

  14. #14
    Forum Contributor
    Join Date
    07-15-2013
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    129

    Re: Nested If Conditions

    Pauleyb: That's so close!!! But I'm hoping that the sumif can be just the first logical statement, and the two following calc! statements can be the second and third logical statments.

    I'm sorry really sorry if I'm being unclear

  15. #15
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Nested If Conditions

    Unclear.
    If first logical statement (I assume the SUMIF) is true then what? if false then what?
    If second logical statement (Calc!AL13<0.33?) is true then what? if false then what?
    Ditto for third logical statement...

    Do you need to error check any more? The SUMIF should avoid the N/As, but could the numbers from your Calc page contain errors?

  16. #16
    Forum Contributor
    Join Date
    07-15-2013
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    129

    Re: Nested If Conditions

    So the goal is for excel to run a type of loop. When it hits a one, I'd like the next 10 cells to be blank. Simple goal, but I'm having a lot of trouble executing it.

    The one's and zeros in AX:BH are just representations of the numbers in AL:AV who's formulas are (,IF(AND(Calc!AL12<0.33,Calc!AL12<Calc!AL3),Calc!AL12,""). I'm just replacing the "if false" and "if true" statements with ones and zeros. I.e. =IF(AND(Calc!AL12<0.33,Calc!AL12<Calc!AM3),1,0) . I did this because the isnumber function doesnt work for an array, so ideally if it did, I wouldnt even need the ones and zeros and I could just put formulas in AL:AV that said something like =if(isnumber(Calc!AL3:Calc!AL12),"",Calc!AL12.

    I'm trying to do the same thing =if(isnumber(Calc!AL3:Calc!AL12),"",Calc!AL12 would do if it actually worked.

    Btw, thanks for stiking with me. I feel like I've tried every weird complicated formula and am just banging my head against the wall at this point! How could something so simply be so dificult?

  17. #17
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Nested If Conditions

    ISNUMBER does work with arrays. Does something like this meet your needs:
    =IF(OR(ISNUMBER(P2:P11)),"",P11)

    I put this in Q11. After typing this in, use Ctrl-Shift-Enter to make this an array formula. I tried to open your attachment, but it was referencing another workbook and there was no sheet called Calc so it was full of errors.

  18. #18
    Forum Contributor
    Join Date
    07-15-2013
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    129

    Re: Nested If Conditions

    Hi Pauley,

    Here's the sheet again. The #N/A values aren't because the sheet isn't there--its because there are #N/A values on the actual sheet.

    I'll give your equation a shot!
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Nested if.... then stmts for several conditions
    By ccbank in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-11-2013, 11:51 AM
  2. [SOLVED] Nested if/or function with three conditions
    By old_army90 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-14-2012, 02:41 PM
  3. more than 7 nested IF conditions?
    By krayziez in forum Excel General
    Replies: 5
    Last Post: 06-19-2008, 01:59 PM
  4. [SOLVED] I want to have a nested IF with two conditions
    By Mahendra in forum Excel General
    Replies: 2
    Last Post: 08-31-2005, 08:05 PM
  5. [SOLVED] Nested IF Conditions
    By prkhan56 in forum Excel General
    Replies: 6
    Last Post: 04-04-2005, 09:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1