solving nested IF statements

  • Thread starter Thread starter Derrick
  • Start date Start date
D

Derrick

I have too many nested if Statements! i need help figuring out how to reduce
them:
my problem:
to ensure the validity of a calculated formula, i am checking to see if a
cell is blank or = 0 before imputting the formula, and then the formula has a
lot of nested ifs as it is.

so, something like: if(A6=0, "A6", if(B6=0, "B6", if(C6=0,"C6",FORMULA)))
but much larger.
anyone help? - make it simplier, more efficient?
 
Hi,
could you please provide an example of your data and what you want to
achieve , it will help
 
This may? be one way
=IF(ISNA(MATCH(1,A6:C6,-1)),B2,INDEX(6:6,MATCH(1,A6:C6,-1)))
 
see below.
so, something like: G6 =if(A6=0, "A6", if(B6=0, "B6",...
if(E6=0,"E6",FORMULA)...))
A6:C6 are variables i want to check. if empty, or =0, their cell name should
appear.
after checking, the final formula will be in the ValueIfFalse block of the
last 'variable check' if statement.

better example:
a6 = 100
b6 = 20
c6 = 10
d6 = 0
e6 = 15
:
Since d6 = 0, G6 = "D6"
if
a6 = 100
b6 = 20
c6 = afds
d6 = 20
e6 = 15
:
Since C6 is not a number, G6 = "C6"

finally, if
a6 = 100
b6 = 20
c6 = 10
d6 = 4
e6 = 15
:
G6 will equal the correct number, using a nested formula. which also has
lots of ifs.
this is simplified, but hopefully more throrough than before.
 
This will return address of cell that equals zero, or is blank, and if none
is found, will perform your formula. Adjust range sizes as needed.

=IF(SUMPRODUCT(--(A6:E6=0)+ISBLANK(A6:E6))=0,YourFormula,ADDRESS(6,MIN(IF(ISBLANK(A6:E6)+(A6:E6=0),COLUMN(A6:E6))),4))

It looks bulky, but there's only 1 nested function before your main
function. Also, this is an array function, so you'll need to commit it using
Ctrl+Shift+Enter, not just Enter.
 
Ah, throwing a number check into the mix as well? Adjusting my formula, it
becomes:

=IF(SUMPRODUCT((A6:E6=0)+ISBLANK(A6:E6)+ISTEXT(A6:E6))=0,YourFormula,ADDRESS(6,MIN(IF(ISBLANK(A6:E6)+(A6:E6=0)+ISTEXT(A6:E6),COLUMN(A6:E6))),4))
 
help me out. something is off.. and giving me a different cell name.

Ive changed it slightly to match my cells
-----------
=IF(ISNUMBER(MATCH(TRUE,INDEX(G6:N6=0,0),0)),ADDRESS(6,MATCH(TRUE,INDEX(G6:N6=0,0),0),4),"sdfg")
-----------
right now i have numbers <>0 in all, except N6 = 0. it returns "H6"
close....but no cigar

can you explain how it works?
IF(this, then, else)
MATCH(lookup_value,lookup_array,match_type)
INDEX(array,row_num,column_num)
ADDRESS(row_num,column_num,abs_num,a1,sheet_text)

1. why the ...,index(G6:N6=0,0),... the =0,0 part.
2. why the ...,0),0),4),"sdfg") the ,4 part
thanks for your help!
 
and by committing the formula you mean when im done writing it all and want
to move on to the next cell?
i ahve to do that every time i change the formula right?

also, what happens if i dont?
 
Luke. thanks!!

One more thing, basically just a modification - lets say there is some space
inbetween the cells - ie not an array
so: cells a6,b6,c6, ,e6.
and d6 has words.
can i change this formula to match?
 
Correct. You should only have to input the formula once, why would you be
changing it often? If you don't input it as an array, it would cause the
second IF function to return erroneous results, since it would be eviluating
a single variable, as opposed to an entire array.
 
See my response following Eduardo's comment. I modified my formula to check
for text as well, following your example's structure.
 
? sorry, your message isnt displayed.

NBVC said:
Since you are not starting at column A, you will need to offset the
formula to look starting at column G

=IF(ISNUMBER(MATCH(TRUE,INDEX(G6:N6=0,0),0)),ADDRESS(6,MATCH(TRUE,INDEX(G6:N6=0,0),0)+COLUMN(G6)-1,4),"sdfg")


--
NBVC

Where there is a will there are many ways.
'The Code Cage' (http;//www.thecodecage.com)
Click to expand...
 
ok. Changing it only during the process of figuring out the formula.. once
im done im done changing it.
 
nevermind i see it at the bottom.

ok . so this works for an array, or a column that is continuous. if i have
cells inbetween, (see my 2nd response to Luke, and luke's response to Eduardo
for better details) where i dont want to check for numbers, but texts, and so
its not as prettily organized, can we modify this formula?

so far im leaning towards using this formula, as its working the best so
far. hahah
sorry luke. time to step up ur game :P
im a bit confused to the syntax still, but i think i can work around it.
 
Back
Top