+ Reply to Thread
Results 1 to 9 of 9

Trying to Shorten a Long Nested If Statement

  1. #1
    Registered User
    Join Date
    08-15-2019
    Location
    libya
    MS-Off Ver
    2013
    Posts
    3

    Post Trying to Shorten a Long Nested If Statement

    =if(d6<50,0,if(d6="غ",0,if(e6<50,0,if(e6="غ",0,if(f6<50,0,if(f6="غ",0,if(g6<50,0,if(g6="غ",0,if(h6<50,0,if(h6="غ",0,if(i6<50,0,if(i6="غ",0,if(j6<50,0,if(j6="غ",0,if(k6<50,0,if(k6="غ",0,if(l6<50,0,if(l6="غ",0,if(m6<50,0,if(m6="غ",0,if(n6<50,0,if(n6="غ",0,if(o6<50,0,if(o6="غ",0,if(p6<50,0,if(p6="غ",0,if(q6<50,0,if(q6="غ",0,if(r6<50,0,if(r6="غ",0,sum(d6*$d$4,e6*$e$4,f6*$f$4,g6*$g$4,h6*$h$4,i6*$i$4,j6*$j$4,k6*$k$4,l6*$l$4,m6*$m$4,n6*$n$4,o6*$o$4,p6*$p$4,q6*$q$4,r6*$r$4,)))))))))))))))))))))))))))))))

    Cell q6 has a function that adds a product of (b6 * b4), (c4 * c6), (d6 * d4), and so on (p6 * p4)
    Provided the result is zero in two cases: enter a value less than 50
    Or enter a literal value

    A shortcut is required for this function to function the same as the existing function
    Last edited by AliGW; Yesterday at 01:31 AM.

  2. #2
    Forum Contributor
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    134

    Re: Is there a shortcut to this function?

    Does this work? (I read your original post again and say I misread it so I've edited this post with a different formula)
    Please Login or Register  to view this content.
    Last edited by Gregb11; 08-15-2019 at 11:27 PM.

  3. #3
    Forum Contributor
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    134

    Re: Is there a shortcut to this function?

    BTW, the above formula assumes none of the cells will be a negative number - is that correct? If they can be negative, then how would you want to handle it? Let's say g6 = -23 and h6 = 0, what value would you want to display? And would if they were reversed (g6 = 0 and h6 = -23), would you want a different answer?

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    47,765

    Re: Is there a shortcut to this function?

    Hi, welcome to the forum

    Rather than give a very obscure title, then pasting a formula and expecting members here to just know what you want, please give...
    1. a detailed explanation of what you are doing
    2. what you are working with (a sample workbook would be great)
    3. an explanation of what you expect.

    Just looking at your formula, I can already see a bunch of errors, here are some that stand out...
    =if(d6<50,0,if(d6="غ",0,if(e6<50,0,if(e6="غ",0,...
    In your 2nd (and subsequent) IF statement, you not only have the closing " in the wrong place - it should be before the comma - but you also dont need them there at all anyway.

    =if(d6<50,0,if(d6=0,0,if(e6<50,0,if(e6=0,0,if(f6<50,0....
    Also not sure what that funky character is in that formula...
    غ",0
    ??
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Banned User!
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,187

    Re: Is there a shortcut to this function?

    @Greg, no IFS in excel 2013 Also, looking at the order of the logic tests in the original, I think MIN might need to be MAX

    Perahps

    =IF(SUMPRODUCT((D6:R6<50)+(D6:R6=1000)),SUMPRODUCT(D$4:R$4*D6:R6))

    This is assuming that غ = 1000 and that I have read the formula correctly.

  6. #6
    Forum Contributor
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    134

    Re: Is there a shortcut to this function?

    If "IFS" is not available to the OP, then you could use this formula:

    =IF(MIN(D6:R6)=0,"غ",IF(MIN(D6:R6)<50,0,SUMPRODUCT(D4:R4,D6:R6)))

    However, as FDIBBONS has noted, we need to know exactly what the poster wants. Because, the statement he currently has really doesn't make sense as it says if D6<50, then be 0, but if it's 0, the be غ. Well, it could never by غ because 0 is less than 50 so it would never get to that part of his IF statement.

    For the OP, what my formula says is, if any cell in row 6 is 0, then be غ. If none are 0, but if any are less than 50, then be 0, else, sum the products of the 2 rows.

  7. #7
    Registered User
    Join Date
    08-15-2019
    Location
    libya
    MS-Off Ver
    2013
    Posts
    3

    Re: Is there a shortcut to this function?

    Cell q6 has a function that adds a product of (b6 * b4), (c4 * c6), (d6 * d4), and so on (p6 * p4)
    Provided the result is zero in two cases: enter a value less than 50
    Or enter a literal value

    A shortcut is required for this function to function the same as the existing function

    =IF(B6<50,0,IF(B6="غ",0,IF(C6<50,0,IF(C6="غ",0,IF(D6<50,0,IF(D6="غ",0,IF(E6<50,0,IF(E6="غ",0,IF(F6<50,0,IF(F6="غ",0,IF(G6<50,0,IF(G6="غ",0,IF(H6<50,0,IF(H6="غ",0,IF(I6<50,0,IF(I6="غ",0,IF(J6<50,0,IF(J6="غ",0,IF(K6<50,0,IF(K6="غ",0,IF(L6<50,0,IF(L6="غ",0,IF(M6<50,0,IF(M6="غ",0,IF(N6<50,0,IF(N6="غ",0,IF(O6<50,0,IF(O6="غ",0,IF(P6<50,0,IF(P6="غ",0,SUM(B6*$D$4,C6*$E$4,D6*$F$4,E6*$G$4,F6*$H$4,G6*$I$4,H6*$J$4,I6*$K$4,J6*$L$4,K6*$M$4,L6*$N$4,M6*$O$4,N6*$P$4)))))))))))))))))))))))))))))))

  8. #8
    Forum Contributor
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    134

    Re: Is there a shortcut to this function?

    Wow, I'm unfamiliar with that character - غ - but it does funny things to my screen. When I look at your formula on this web page, it is different than when I paste it into Excel and see it as a formula there. When I look at it on this page, it basically says that if in row 6 the value is zero, to make the value غ. But when I paste it into excel, it says that if in row 6 the value is غ, to make the cell value 0.

    Anyway, so I'm replacing that symbol with the letter "x". Based on what I can now interpret by what you are saying, does this work for you? (I'm still using D6:R6 as the range as that is what you had in the original post, but your last post mentions B6:P6, which is a little confusing).

    =IF(OR(MIN(D6:R6)<50,COUNTIF(D6:R6,"x")>0,SUMPRODUCT(D4:R4,D6:R6))

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    30,365

    Re: Trying to Shorten a Long Nested If Statement

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however the thread title does not really convey what your request is about.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. As you are new here, I have done it for you this time.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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