Software

Ask something
Question Locked

MS Excel problem

0 helpful
4 replies

by devildrummer302

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have ten cells in Excel containing information. In nine of the cells is the same info and one cell is different. Is there a formula to tell Excel to recognise the content of the different cell and enter this information into a another cell.

4 replies

by jesus_c

right click on the correct cell and select copy.Then right click on the wrong cell and select paste.It copies the formula and not the contents

by locolobo

I'm not sure what your looking for. I didn't see a formula to pick the lone ranger cell out of the crowd. This is klugy, Excel only allows 7 layers of nesting, ergo you need 2 formulas. So here's my take:

Cell range: c7:c16

The first formula in cell c18:

=IF((C8-C7)=0,IF((C9-C8)=0,IF((C10-C9)=0,IF((C11-C10)=0,IF((C12-C11)=0,IF((C13-C12)=0,IF((C14-C13)=0,IF((C15-C14)=0,0,(C15)),(C14)),(C13)),(C12)),(C11)),(C10)),(C9)),(C8))

I told you it was klugy, or is that UGly?

That covered 9 of the cells, to get the tenth in cell c19:

=if(c18=0,c16,0)

This is probably a case for a macro or VB program to parse your range and spit it out. This example assumes you only have one cell that is different. This seems a poor way to do data validation.

If that's not what your asking for, sorry. Perhaps you can explain it a little more.

by loewenr

The easiest way to solve your problem is:

I took 10 cells A6 to J6 with the scenario you had listed and I found that the formula:

=IF(MAX(A6:J6)<>MEDIAN(A6:J6),MAX(A6:J6),MIN(A6:J6))

will give you the result you are looking for. It compares the maximum number with the median number. If they are the same then it takes the smallest number in the list. If they aren't then they take the largest.

Hope this helps.

This discussion is closed to new replies.