0

I need help with a statement that is returning #value!

I need to my statement to work out if both K4 and I4 are blank then it will return the value in H4. However if only K4 is blank then I want it to show the value of I4 minus H4.

This is what I have but it isn't working. 

=IF(AND(K4="",I4=""),H4),IF(K4="",I4-H4)
Improve this question

2 Answers 2

Reset to default
1

Can you try the following formula:

=IF(AND(K4 = "", I4 = ""), H4, IF(K4 = "", I4-H4, ""))

Also please check on how to use the nested IF function

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Your formula is returning I4-H4 irrespective of whether it is only K4 which is blank or both K4 and I4 are blank.
0

You have not nested your formulas properly. Also for the second condition, it is better to use "<>" (not equal to) to test whether I4 is not blank.

=IF(AND(K4="",I4=""),H4,IF(I4<>"",I4-H4,IF(K4<>"",K4-H4,"")))
Improve this answer

5 Comments

Thank you so much for your help it is really appreciated. I forgot to say that I also need the statement to show if K4 contains a number then it will show k4-h4. This is proving to be a mind boggler for me!
I tried adding on if(k4>0,k4-h4) to the end of your statement but it didn't work.
It is working only if k4 is not populated. If K4 has a number it is returning a #Value! error
Works fine when I am testing it
I figured what the problem was. Your statement is perfect. Thank you so much for all your help. It is very much appreciated and saved me a lot of stress you are a star!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.