Calculate if not blank and sum without errors

[aiki100]

Hello everyone - I was hoping to get a little help with this formula (in COL BE2), please:


=IF(AND($AP2="x",$AW2="L",I3>BC2),J3-AY2,IF(AND($AP2="x",$AW2="S",H3<BC2),AY2-J3,IF(AND($AP2="x",$AW2="L",I3<=BC2),BC2-AY2,IF(AND($AP2="x",$AW2="S",H3>=BC2),AY2-BC2,""))))


It does what it is supposed to do, except that if cell AY2 is blank (which it sometimes is), it returns #VALUE , which then messes up the BE column sum.


I would like it to calculate IF cell AY is NOT BLANK. If it is blank, it would like the formula to not evaluate, and return something that unlike a #VALUE , or #N/A, will allow column BE to sum without errors.


Any help much appreciated - thank you all!

 

[RoryA]

Just wrap that whole thing in another IF function:

=IF(AY2="","",your formula)

 

[aiki100]

Just wrap that whole thing in another IF function:

=IF(AY2="","",your formula)

Thank you Rory!

I do see that this should fix it up. But I now seem to have another issue. I see now that that AP2 can either contain an "x", or again, be blank - in the case of the formula in this row, it happens to be blank.

In any event, when entering the formula (now modified with your addition) below:


=IF(AY2="","",IF(AND($AP2="x",$AW2="L",I3>BC2),J3-AY2,IF(AND($AP2="x",$AW2="S",H3<BC2),AY2-J3,IF(AND($AP2="x",$AW2="L",I3<=BC2),BC2-AY2,IF(AND($AP2="x",$AW2="S",H3>=BC2),AY2-BC2,""))))


...excel warns me that I have an error and it points to the "x" indicated below (large) as the source of the error.

=IF(AY2="","",IF(AND($AP2="x",$AW2="L",I3>BC2),J3-AY2,IF(AND($AP2="x",$AW2="S",H3<BC2),AY2-J3,IF(AND($AP2="x",$AW2="L",I3<=BC2),BC2-AY2,IF(AND($AP2="x",$AW2="S",H3>=BC2),AY2-BC2,""))))

Any thoughts? Thanks again so much for the guidance!

 

[aiki100]

Dear Rory -

Have just worked with this some more and it somehow seems correct now - after pasting in a second time, excel offered a modification, and I took it:

[FONT=&quot]=IF(AY2="","",IF(AND($AP2="x",$AW2="L",I3>BC2),J3-AY2,IF(AND($AP2="x",$AW2="S",H3<BC2),AY2-J3,IF(AND($AP2="x",$AW2="L",I3<=BC2),BC2-AY2,IF(AND($AP2="x",$AW2="S",H3>=BC2),AY2-BC2,"")))))

I cannot as of yet discern what is different, but it seems to be good now - thanks to you.

Much appreciated and thank you again -[/FONT]

 

[RoryA]

Glad to help.

FWIW, I think that can be shortened to this:

=IF(AY2="","",IF($AP2="x",IF($AW2="L",IF(I3>BC2,J3-AY2,BC2-AY2),IF($AW2="S",IF(H3< BC2,AY2-J3,AY2-BC2),"")),""))

 

[aiki100]

Rory -
So sorry, got the flu! Just now coming up of air...ugh.

I wanted to say thank you again for the help on this, it is very helpful - thanks for your time.

 

[RoryA]

Glad to help.