Forum Discussion
Solved
IF AND function
Hello, I need help with a couple formulas.
In one case, If cell $C18 and $X18 both have data, then apply the formula but if either is blank, then leave the cell with the formula blank. So this is what I've typed in cell AB18.
=IF(AND($C18="","",$X18="",""),X18-Z18) and I get this... #VALUE
Now a different situation....if C18 is blank, but X18 has data, then I want to apply a formula in AA18 but if both are blank, then leave AA18 blank.
Thanks in advance.
Dennis
Dennis, if with one column
=X3-IF(LEN(C3)=0,Y3,Z3)
I received 3 responses, none really worked as I intend. I did not explain my problem very well. I have attached my spreadsheet, which may help.
This is a golf scoring sheet for my golf club.
Our members have the option of playing from white tees, which is where most play.
Some choose to play from the red tees.
The handicaps are different...column Y is white and Z is red.
The Net score from red tees is X - Z
The net score from white is X - Y
So the formula I have in column AC works fine when someone plays from white tees.
The formula in column AD also works when someone plays for red tees.....our scorer enters r in column C to enable the calculation.
But here's what I would like to accomplish....when someone plays from red tees, I would like the white score to disappear and show only the red score.
An even better solution, if it's possible, is to have only 1 column for net score instead of 2 columns. So the column AC would calculate X-Y when column C is empty, and it would calculate X-Z when column C contains r...or R....or any data for that matter. Column AE is formatted to highlight those who play from red, column AC is formatted to highlight scores of 72 or less, to more easily spot the lowest scores and the winners.
Dennis, if with one column
=X3-IF(LEN(C3)=0,Y3,Z3)
Thanks for your help Sergie, your solution worked and 1 column is a much better option than having 2 different columns.
Dennis
Hi Dennis,
In you formula for AND you have 4 parameters - 2 conditions and 2 empty strings. With AND you shall combine conditions only and depends on result to return empty string or formula.
In general it's more reliable to check if the cell is empty/blank checking the length of the cell, I'd suggest
=IF((LEN($C18=0))*(LEN($X18)=0),"",X18-Z18)
where multiply (*) is equivalent of AND.
Similar is for your second check.
Dennis,
for your first question try:
=IF(COUNT(C18,X18)=2,X18-Z18,"")
Your second question is not complete. What if C18 and X18 both have data?
