Selecting and copying a value, but if cell blank go to next cell

[marc01]

Hi all,

Having a slight issue in trying to achieve what I would like with the following data:

Federer Serving Djokovic Serving Winner Error Shot type Winner 1 Unforced error 0 Winner 1 Unforced error 0 Winner 1 Winner 0 Winner -1 Winner 0 Unforced error -1 Winner 0 Unforced error -1 Unforced error 0 Unforced error -1 Unforced error -2 Winner -3 Unforced error -2 Winner -1 Winner 0 Unforced error 1 Winner 2 Winner 3 Winner 4 Winner 3 Winner 4 Unforced error 5 Winner 4 <tbody> </tbody> <strike></strike> I would like to first check if the shot type is a winner or an error. If it is a winner, then copy and paste the value in column B into the Winner column(D), and put #NA () in the Error column. If the shot type was error then the value is copied in to the error column(E), and #NA () in to the Winner column (D). I had thought I had achieved this with the following functions: For column D IF(A3="Winner",B3,NA())​ For column E IF(A3="Error",B3,NA()) However, this won't work when the server changes and the value shifts to being in column C.​ <strike></strike>
Can anyone help? (sorry I hope this makes sense!)

<tbody>
</tbody>

 

[DanteAmor]

For column D

=IF(B3<>"",IF(A3="Winner",B3,"#NA()"),IF(A3="Winner",C3,"#NA()"))

For column E

=IF(B3<>"",IF(A3="Unforce error",B3,"#NA()"),IF(A3="Unforce error",C3,"#NA()"))

 

[jtakw]

Hi,

Is this what you mean:

Book1
	A	B	C	D	E
1	Shot type	Federer Serving	Djokovic Serving	Winner	Error
2	Winner	1		1	#N/A
3	Unforced error	0		#N/A	0
4	Winner	1		1	#N/A
5	Unforced error	0		#N/A	0
6	Winner	1		1	#N/A
7	Winner	0		0	#N/A
8	Winner	-1		-1	#N/A
9	Winner	0		0	#N/A
10	Unforced error	-1		#N/A	-1
11	Winner	0		0	#N/A
12	Unforced error	-1		#N/A	-1
13	Unforced error	0		#N/A	0
14	Unforced error	-1		#N/A	-1
15	Unforced error	-2		#N/A	-2
16	Winner		-3	-3	#N/A
17	Unforced error		-2	#N/A	-2
18	Winner		-1	-1	#N/A
19	Winner		0	0	#N/A
20	Unforced error		1	#N/A	1
21	Winner	2		2	#N/A
22	Winner	3		3	#N/A
23	Winner	4		4	#N/A
24	Winner	3		3	#N/A
25	Winner	4		4	#N/A
26	Unforced error		5	#N/A	5
27	Winner		4	4	#N/A
Sheet394

Cell Formulas
Range	Formula
D2	=IF(A2="Winner",IF(B2,B2,C2),NA())
E2	=IF(A2="Unforced error",IF(B2,B2,C2),NA())

 

[marc01]

Hi both,

Many thanks for your quick replies!

Both methods seem to work which is ideal! From an understanding point of view I can see what the formula from Dante is doing, however can't get my head around IF(B2,B2,C2)…..are you able to help this excel newbie comprehend this?

 

[jtakw]

You're welcome.

IF(B2,B2,C2), for an IF statement, Excel is looking for TRUE/FALSE, any Number Other than 0 is evaluated as TRUE, 0 and Blank as FALSE, so essentially, it's "shorthand" to

IF(B2<>0,B2,C2) or IF(B2<>"",B2,C2)

In your case, if B2 is 0, this part of the formula with return 0 Regardless of B2 or C2 is Blank.

 

[marc01]

Thanks for taking the time to explain.
I think I get it....in theory when B2 does contain 0, the formula see's the argument as being FALSE (as B2 is actually showing 0) so returns the value in C2 which is blank thus showing 0 as the answer. Is that the correct way of interpreting it?

 

[jtakw]

Yes, that is correct.

If you want the formula to be more "user understandable", you can swap out IF(B2,B2,C2) with IF(B2<>"",B2,C2), then it will return the 0 in B2 (again, given the way your data is, you'll get the correct result either way).

 

[marc01]

phew....my head was beginning to hurt!
again many thanks for your assistance!

 

[jtakw]

You're welcome, glad to be of assistance.