multiple IF in nesting formula

New Contributor

=IF(H20=x,"trash",IF(H20="","",IF(H20=0,"keep", IF(H20>0.01,"sold"))))

 

I am working on my budget, however, I wanted to set 3 "keys" in cell H20, however, this will not allow me to function properly the way I am expecting it to do, so I believe that what I typed in the function, are wrong.

 

When I type x, it will pop the word up in a cell "trash"

When I type 0, it will pop up the word in a cell "keep"

when I leave the a cell blank, it will leave a cell blank,

when I type a number that are above 0.01, it will pop the word up in a cell "sold"

 

Can you help, please? 

5 Replies

The only issue I see, based on your description, is that the x should be in quotes:

=IF(H20="x","trash",IF(H20="","",IF(H20=0,"keep", IF(H20>0.01,"sold"))))

 

And, perhaps, if it is possible the value could be 0.01 then you may need a false argument for the last if statement (or change > to >=)?

I GOT IT!! THANK YOU. one more thing though..

how do I make the blank cell, leave out nothing as in blank, in this nesting function? Because when I leave it empty, it marked as in "sold" when I was asking for it to be blank.

Can you see if I typed out anything wrong about this one?

=IF(D5="X","POSTED",IF(D5>=0.01,"SOLD",IF(D5="","",IF(D5=0,"KEEP"))))
EDIT: CORRECTION:

I TYPED Y AS IN TRASH, AND IT MARKED AS SOLD.

=IF(D4="X","POSTED",IF(D4>=0.01,"SOLD",IF(D4="","",IF(D4=0,"KEEP",IF(D4="Y","trash")))))
Excel treats any text value as greater than any number. So, testing for 0.01 needs to be at the end. Also, note that testing for blank "" must come before testing for 0 (as an empty cell will register as both blank and zero, but cell containing a zero 0 will not register as blank).

@isaacolvey 

As variant

=LOOKUP(D4,{0,0.01,"","X","Y"},{"KEEP","SOLD","","POSTED","trash"})