IF and AND nested Formula

[voltrader]

Hey all,

I am trying to construct an If and AND formula that includes 3 arguments each argument has its own solution. I am having issues with the syntax of the formula if anyone can help -greatly appreciated. Below is the formula I put together so far.

=IF(AND(C171>D171,D171>0),((D171-C171)*(-1))/D171),1,if(and(C171<D171,D171>0,((C171-D171)*(-1))/D171),2,if(and(c171>=0,d171<0,((H165)+(I165*-1))/(I165*-1),3,0,)))

 

[shawnhet]

Hi voltrader,

Why don't you describe what you would like to accomplish?

Parsing that above is a little difficult (at least for me).

Cheers,

 

[voltrader]

Hey thanks for the reply,

I am trying to nest a formula that provides 3 arguments, and each argument has its own solution. Here is the latest formula syntax I tried:

=IF(AND(C27>D27,D27>0,(C27-D27)/D27),1,IF(AND(C27<D27,D27>0,(D27-C27)/D27),2,IF(AND(C27>=0,D27<0,(D27-C27)*-1)/(D27*-1),3,0)))

I am thinking if I need to use a different conditional function rather than IF, AND? The formula I put together is to determine which mathematical formula is needed to solve for 3 different outcomes (there are 3 different formulas one for each outcome). I have 2 givens an Actual, and Estimate number, and I am trying to display the % difference of the actual vs. estimate as either a -, or + number. ie. if actual is 10, and estimate is 5 then the total will be displayed as 50%. I have the 3 separate formulas in order to reduce the (-) (+) confusions.

Thanks,
Elliot.

 

[shawnhet]

Hi again,

If you want to display the % difference, I think this may get you pretty close. I'm not really sure what condition your middle condition is testing for so I changed it to C27=0 - I would recommend thinking about this condition a bit more explicitly.

=IF(AND(C27>D27,D27>0),(C27-D27)/D27,IF(C27=0,(D27-C27)/D27,IF(AND(C27>=0,D27<0),(D27-C27)*-1/(D27*-1),0)))

I would try subbing in different values for C27 and D27 and check to see where the output differs from what you would like.

Cheers,

 

[voltrader]

Yes that works! I tweaked some parts of the formula especially the middle condition as you pointed out I made an error while formatting it .

=IF(AND(C162>D162,D162>0),(C162-D162)/D162,IF(AND(C162<D162,D162>0),(D162-C162)/D162,IF(AND(C162>=0,D162<0),(D162-C162)*-1/(D162*-1),0)))

The only issue I am having is the -% numbers are being returned as +, so I will have to play around with the mathematics and figure out what is wrong.

Thank You,
Elliot.

 

[voltrader]

For some reason when I copy and paste the formula, the middle conditions paste incorrectly it should read IF(AND(C162<D162,D162>0),(D162-C162)/D162,

 

[voltrader]

It does not recognize it here either so I will space it out:
IF(AND(C162<D162,D162>0), then use calculation (D162-C162)/D162,

 

[voltrader]

This is weird It is still displaying it incorrectly I will try once more:
after the IF(AND( it should read C162 < D162, D162 > 0

 

[ukmikeb]

HI

Try posting the formula using the code (#) tag at the top of the box.

ie Select the Formula in your message and then click # tag.

hth

 

[shawnhet]

Hi again Elliot,

I still do not get what your middle condition is trying to test for. Perhaps you can explain it in words - currently this

IF(AND(C1620), then use calculation (D162-C162)/D162

Will always be true unless C1620=0(in which case it will always be flase) and I cannot see why you are referring to C1620 when you are in C162 - it looks as though you are just adding a 0 to the end of the cell number.

Anyways, based on my interpretation of what you have given here I am going to recommend the following expression -

=IF(D27=0,"",(C27/D27)-1)

I would then recommend subbing in different values for C27 and D27 to check whether it gives you the wrong answer at some point and tell us exactly when it gives an answer you are not looking for and what the answer should be. It is pretty hard to tell what is wrong with your formula if we don't know what it is trying to do.

Cheers,