Forum Discussion
Writing a formula to ignore a blank cell
HansVogelaar Hi, I hope you day is going well. I want to use a formula for the following:
If cells G22, H22, and I22 are greater than the value in F22 then calculate in J22 (ABS((G22-H22)/((G22+H22)/2)) or else display "Value < MDLx5"). My issue is sometimes I22 may be left blank; in this circumstance I need the above calculation described for J22 to happen accept it would be for G22 and H22 (I just need it to ignore I22 if left blank but still perform the calculation.
on a side note I need J22 (where the formula would be) to be blank if G22 is blank (I don't know if we can fit that in the formula somewhere). I hope this helps, thank you for taking the time to look at this for me.
How about
=IF(G22="","",IF(AND(G22>F22,H22>F22,OR(I22>F22,I22="")),ABS((G22-H22)/((G22+H22)/2)),"Value < MDLx5"))HansVogelaar thanks, its almost right. There is one thing missing from the formula:
If I22 has a value I need it to be included in the calculation, but if its blank I need it to only look at G22 and H22 for the calculation (the second part of this sentence seems to be taken care of in the formula). I tried changing the range in the calculation from H22 to I22 but then it would configure a blank space in the calculation (not sure what value it is using for the blank space). Thanks again for looking at this for me.
Your expression ABS((G22-H22)/((G22+H22)/2)) doesn't involve I22 in any way, so you'll have to tell us how to use I22.
HansVogelaar My appologies, I was thinking it had I22 in there. If I22 is blank then the calculation should read: ABS((G22-H22)/((G22+H22)/2)), if I22 has a value then the calculation should read: ABS((G22-I22)/((G22+I22)/2)). Let me know if that makes sense and if you need anything else.