Get Actual Number From 2 Cells

[Leathermyth]

Okay, I need to find a number (1, 2 or 3) and I am looking in two different cells for it. Now, Cell 1 might have the information; Cell 2 might have the information or BOTH Cells A AND B might have the information, or either cell (or both) might have different information. I can't do an IF/OR because I don't know which cell will have it, nor can I do an IF/AND for the same reason. I can't assume that they will both have the information -- so I can't just use a solution that includes a "divided by."

for example:

A
1 2
2 0

or maybe

A
1 3
2 3

or

A
1 4
2 1

If A1 or A2 is > 0, but <4, then that number

[simarui]

your description and your sample don't really sync... is there ever an instance where neither cell has the information your looking for? what do you want to return in that case? what about two different, but valid numbers (in your examples the only instance in which both numbers are valid they are also the same...) what do you want to return in that case?

I believe this solves for your very last statement for every example you've provided...

Please Login or Register  to view this content.

(untested)

[Leathermyth]

Okay; I can make this work. I'll just have to have a second formula to "filter" as it pre-supposes that the number in A2 would be between 1 & 3.

However, I'm just lazy enough to wait and see if someone comes up with an "all-in-one" formula. So, I'm not marking this Solved, yet.

Thank you, though; it could very well be this is THE solution!

[simarui]

the "all-in-one" solution is not going to be much harder... but you haven't really given us all the information... what do you want to return in the event that both values are between 1 & 3? what do you want to return in the event that neither value is?

the all-in-one solution will look something like this:

Please Login or Register  to view this content.

(untested)

this assumes that in the event both values are valid you want to return a1. if you can tell us otherwise we can write it otherwise, we just need all the information...

[Leathermyth]

I think that is it, but to answer your question --

2 people are working as a team. However, they will be receiving individual grades as well as a team grade. There are several "units" within the assigned project. In the one given, the Team can choose to do Step 1, Step 2, or Step 3 -- do more than one of these or skip this part and go for the more difficult steps (Steps 4,5, etc.). So, for Individual grade, we are tracking who did what, hence tracking whether person 1 did the chosen step, person 2 did it or (best) both did it. But, there is also the Team grade, and for this we just want to know if the Team did Step 1, Step 2, etc. Now, some might say just enter the information manually, but there are over 120 people, and this is only one of the subsection we intended to use this formula for.

Does that help?

[simarui]

I still don't really know what you're looking for... I think I just gave you the formula for the team grade? I would just about never suggest you should enter the information manually, and can just about guarantee there is a formula for w/e it is you're trying to accomplish.

what do you want to return in this scenario:
A
1 1
2 3

?
And this one:
A
1 0
2 4
?

[Leathermyth]

It is the Team grade I'm looking for. In the first scenario, it would be 3; the second, 0, as the 4 would indicate the team had skipped this subsection.

It might help to know that, in your first example (A1=1, A2=3), the team grade would be that the Team completed all 3 steps. However, the Individual grade from that would tell us that Person 1 (A1) only worked on Step 1, whereas Person 2 (A2) had worked on all 3 steps (yeah; Person 1 wouldn't be getting a very good Individual grade).

I think I'm going to have to go for a 2-cell, 2-formula approach.

So, Cell One (B1 for this example), would be: =IF(AND(A1>0,A1<4),A1,A2)

Then, Cell Two would be: =IF(A2>A1,A2,A1)

Make sense?

[simarui]

If it works for you sounds like you're looking for the max of the two valid numbers, right? this should give you the right answer in all cases we've discussed:

Please Login or Register  to view this content.

That last 0 is what will be returned if neither team completed steps 1-3. A min(a1,a2) there would also work for the example given, but if both teams skipped 1-3 and completed 4 then min() would return 4 instead of 0...


as in the attached.