Choosing the best cell

[citrusfish]

Hi

This post has two requests

I have the data shown below:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Target 1[/TD]
[TD="align: center"]Target 2[/TD]
[TD="align: center"]Value of Target 1[/TD]
[TD="align: center"]Value of Target 2[/TD]
[TD="align: center"]Best Target[/TD]
[TD="align: center"]Best Value[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]-0.78[/TD]
[TD="align: center"]2.22[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

I would like to research two possible options as I am not sure which will suit my purpose best:

1. Return in F2 the value of C2 or D2 which is closest to 0 and then copy the corresponding Target depending on whether Value Target 1 or Value Target 2 was the closest. So it would return -0.78 in F2 and 5 in E2.


2. Slightly easier I think. I want to return in F2 the Positive value from C2 or D2 and then copy the corresponding Target depending on whether Value Target 1 or Value Target 2 was the closest. So it would return 2.22 in F2 and 4 in E2.

I hope it is OK to ask two questions in one.

Many thanks for any help you can give.

Citrusfish

 

[jtakw]

Hi,

If your real data is as straight forward as your sample:

Book1
	A	B	C	D	E	F	G
1	Target 1	Target 2	Value of Target 1	Value of Target 2	Best Target	Best Value
2	5	4	-0.78	2.22	5	-0.78	<--Question 1
3	5	4	-0.78	2.22	4	2.22	<--Question 2
Sheet369

Cell Formulas
Range	Formula
E2	=IF(ABS(C2)D2),A2,B2)
E3	=IF(ABS(C3)>ABS(D3),A3,B3)
F2	=IF(ABS(C2)D2),C2,D2)
F3	=IF(ABS(C3)>ABS(D3),C3,D3)

 

[citrusfish]

Re: Choosing the best cell [SOLVED}

Thank you. Just what I needed. Never used the ABS function

 

[jtakw]

Re: Choosing the best cell [SOLVED}

You're welcome, and in re-reading your Question 2, I've made a mistake in my answer for question 2, we need to take Out the ABS function like follows:

Book1
	A	B	C	D	E	F	G
1	Target 1	Target 2	Value of Target 1	Value of Target 2	Best Target	Best Value
2	5	4	-0.78	2.22	5	-0.78	<--Question 1
3	5	4	-2.78	2.22	4	2.22	<--Question 2
Sheet369

Cell Formulas
Range	Formula
E2	=IF(ABS(C2)D2),A2,B2)
E3	=IF(C3>D3,A3,B3)
F2	=IF(ABS(C2)D2),C2,D2)
F3	=IF(C3>D3,C3,D3)