IF OR AND Functions

Occasional Contributor

I have set up a drop down menu with four values (revenue, expenses, capital infusion, and return of capital). I also have a column where amounts will be entered.


I want to ensure that only positive values can be entered for revenue and capital infusion and only negative values can be entered for expenses and return of capital.


Is there a formula that can do that?


Or I was attempting to build a IF/OR/AND formula for a check column, such that IF cell B8 was either "Revenues" OR "Capital Infusion" AND cell H8 was >0, then "ok", otherwise "Error".  


I cannot figure this formula.



7 Replies


=IF(AND(H8>0,OR(B8="Revenues",B8="Capital Infusion")),"ok","Error")

Is this what you are looking for? 

Thanks very much. That works. Is there a way to add to it also that if B8 is "Expense" or "Return of Capital" and H8 is <0 that that is ok?

Cell B8 has four options, two need to have cell H8 positive to be ok and the other two need to have cell H8 negative in order to be correct.


In a cell in row 8, you could use


=IF(OR(AND(OR(B8={"Revenue","Capital Infusion"}),H8>0),AND(OR(B8={"Expenses","Return of Capital"}),H8<0)),"OK","Error")


In a Data Validation rule of type Custom for H8 and below, you could use formula


=OR(AND(OR(B8="Revenue",B8="Capital Infusion"),H8>0),AND(OR(B8="Expenses",B8="Return of Capital"),H8<0))


Thanks very much. Excel is indicating there is an error in the formula - which I cannot locate.


I forgot a quote. I have corrected my previous reply.

Thanks - but I am now getting "Error" in all scenarios.
My bad - it is working. Thank you very much!!