# Trying to Shorten a Long Nested If Statement

1. ## 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  Register To Reply

2. ## 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.``  Register To Reply

3. ## 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?  Register To Reply

4. ## 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
??  Register To Reply

5. ## 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.  Register To Reply

6. ## 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.  Register To Reply

7. ## 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)))))))))))))))))))))))))))))))  Register To Reply

8. ## 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))  Register To Reply

9. ## 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.)  Register To Reply

#### Thread Information

##### Users Browsing this Thread

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

#### 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