# IF OR AND Functions

Occasional Contributor

# IF OR AND Functions

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.

Thanks

7 Replies

# Re: IF OR AND Functions

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

Is this what you are looking for?

# Re: IF OR AND Functions

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.

# Re: IF OR AND Functions

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

# Re: IF OR AND Functions

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

# Re: IF OR AND Functions

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

# Re: IF OR AND Functions

Thanks - but I am now getting "Error" in all scenarios.

# Re: IF OR AND Functions

My bad - it is working. Thank you very much!!