using 2 multi conditional if then statements based on value of another cell

New Member

I have searched thru the treads and tho there are a few threads related to multiple if then statements in one cell nothing seemed relavent or worked.

I have a value of either 1 or -1 in column N

I need to use formulas

if(n3=-1,IF(Q3<D4,O3-D4,IF(P3>E4,N3-E4,0)
if(N3=1,IF(Q3>E4,Q3-N3,IF(P3<D4,D4-N3,0)

I have tried using OR between the two formulas but always get 0 returned,
the formulas both work independently.

Is there a function I am not aware of, or am I using the Or function wrong?

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

AliGW

Banned
As I have just said on the other forum, try this:

=IF(AND(N3=-1,IQ3 < D4),O3-D4,IF(AND(N3=-1,P3 > E4),N3-E4,IF(AND(N3=1,Q3 > E4),Q3-N3,IF(AND(N3=1,P3 < D4),D4-N3,0))))

New Member
had to change a few cells around, but yes that did work, and I believe I understand the concept of why it was not working and why your formula does work.

Thank you.

mikerickson

MrExcel MVP
I'm not sure why you are using OR, but you could use a formula like

=IF(N3=-1, oneFormual, IF(N3=1, anotherFormula, "bad entry in N3"))

in your case that might result in

=IF(N3=-1, IF(Q3E4,N3-E4,0), IF(N3=1, IF(Q3>E4,Q3-N3,IF(P3...) , "bad entry in N3"))

With the... being replaced by that part of that formula that doesn't show up on the board.

Last edited:

mole999

Well-known Member
@Trader1588, it has been noted that you have cross posted with another forum. There is a requirement that those actions are made implicit so that members don't do work for you at one site when you could already have a working resolution elsewhere. Please read and follow the forum rules which you agreed to when you joined this site.

That said the reason that the formulas won't show is due to html manipulation in the forum, just add a space either side of the < marker and that should ensure you can be clear

Replies
1
Views
446
Replies
1
Views
370
Replies
0
Views
320
Replies
3
Views
218
Replies
5
Views
842

1,190,764
Messages
5,982,782
Members
439,796
Latest member
Chassee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back