D
dkenebre
Create a formula that will look into ten different cells and record the
cell names of the 3 highest values from those cells into another cell,
when each of those highest values are not equal. The ten cell names in
respective order are called 0, 1, 2, 3, 4, 5, 6, 7, 8 and 9.
· 2. Whenever the ten cells have 5 cells that are equal to the highest
value, then record those cell names in another cell. ie. 0=7, 1=5, 2=7,
3=4, 4=7, 5=7, 6=1, 7=2, 8=3, 9=7 ans. 0,2,4,5,9
· 3. Whenever the ten cells have 4 cells that are equal to the highest
value, then record those cell names in another cell. ie. 0=7, 1=5, 2=7,
3=4, 4=7, 5=7, 6=1, 7=2, 8=3, 9=2 ans. 0,2,4,5
· 4. Whenever the ten cells have 3 cells that are equal to the highest
value, and 1 cell that is next to the highest value then record those
cell names in another cell. ie. 0=7, 1=5, 2=7, 3=4, 4=7, 5=6, 6=1, 7=2,
8=3, 9=2 ans. 0,2,4,5
· 5. Whenever the ten cells have 2 cells that are equal to the highest
value, and 2 cells that are equal to the next highest value then record
those cell names in another cell. ie. 0=7, 1=5, 2=7, 3=4, 4=6, 5=6,
6=1, 7=2, 8=3, 9=2 ans. 0,2,4,5
· 6. Whenever the ten cells have 2cells that are equal to the highest
value, and 1cell that is next to the highest value then record those
cell names in another cell. ie. 0=7, 1=5, 2=7, 3=4, 4=6, 5=5, 6=1, 7=2,
8=3, 9=2 ans. 0,2,4
· 7. Whenever the ten cells have 1cell that has the highest value, and
4 cells that are equal to the next highest value then record those cell
names in another cell. ie. 0=7, 1=5, 2=6, 3=4, 4=6, 5=6, 6=1, 7=2, 8=6,
9=2 ans. 0,2,4,5,8
· 8. Whenever the ten cells have 1cell that has the highest value, and
3 cells that are equal to the next highest value then record those cell
names in another cell. ie. 0=7, 1=5, 2=6, 3=4, 4=6, 5=6, 6=1, 7=2, 8=3,
9=2 ans. 0,2,4,5
· 9. Whenever the ten cells have 1cell that has the highest value, and
2 cells that are equal to the next highest value then record those cell
names in another cell. ie. 0=7, 1=5, 2=6, 3=4, 4=6, 5=2, 6=1, 7=2, 8=3,
9=2 ans. 0,2,4
· 10. Whenever the ten cells have 1cell that has the highest value, and
1 cell that has the next highest value and more than 3 cells that are
equal to the 3rd highest value, then record only the one highest and
one next highest cell names in another cell. ie. 0=7, 1=5, 2=6, 3=4,
4=5, 5=5, 6=1, 7=5, 8=3, 9=5 ans. 0,2
· 11. Whenever the ten cells have 2cells that are equal to the highest
value, and more than 3 cells that are equal to the next to the highest
value, then record only the two highest cell names in another cell. ie.
0=7, 1=5, 2=7, 3=4, 4=6, 5=6, 6=1, 7=6, 8=3, 9=6 ans. 0,2
· 12. Whenever the ten cells have 1cell that has the highest value, and
1 cell that has the next highest value and 2cells that are equal to the
3rd highest value, then record those 4 cell names in another cell. ie.
0=7, 1=2, 2=6, 3=4, 4=5, 5=5, 6=1, 7=1, 8=3, 9=2 ans. 0,2,4,5
· 13.Whenever the ten cells have 1cell that has the highest value, and
1 cell that has the next highest value and 3cells that are equal to the
3rd highest value, then record those 5 cell names in another cell. ie.
0=7, 1=2, 2=6, 3=4, 4=5, 5=5, 6=1, 7=5, 8=3, 9=2 ans. 0,2,4,5,7
There are 13 possible conditions within this formula.
The values in the ten cells are always a single digit number between 0
and 9 in each cell.
The other cell will always have between 2 and 5 single digits with
values between 0 and 9.
In the attached sample sheet, cells G through P are used as the ten
cell names (0-9) with single digit values to be compared to each other
for the highest values, and cell Q is the other cell where the answer
and the formula is placed.
Thanks
File Attached: http://www.exceltip.com/forum/attachment.php?postid=292659 (chart to be formatted.xls)
cell names of the 3 highest values from those cells into another cell,
when each of those highest values are not equal. The ten cell names in
respective order are called 0, 1, 2, 3, 4, 5, 6, 7, 8 and 9.
· 2. Whenever the ten cells have 5 cells that are equal to the highest
value, then record those cell names in another cell. ie. 0=7, 1=5, 2=7,
3=4, 4=7, 5=7, 6=1, 7=2, 8=3, 9=7 ans. 0,2,4,5,9
· 3. Whenever the ten cells have 4 cells that are equal to the highest
value, then record those cell names in another cell. ie. 0=7, 1=5, 2=7,
3=4, 4=7, 5=7, 6=1, 7=2, 8=3, 9=2 ans. 0,2,4,5
· 4. Whenever the ten cells have 3 cells that are equal to the highest
value, and 1 cell that is next to the highest value then record those
cell names in another cell. ie. 0=7, 1=5, 2=7, 3=4, 4=7, 5=6, 6=1, 7=2,
8=3, 9=2 ans. 0,2,4,5
· 5. Whenever the ten cells have 2 cells that are equal to the highest
value, and 2 cells that are equal to the next highest value then record
those cell names in another cell. ie. 0=7, 1=5, 2=7, 3=4, 4=6, 5=6,
6=1, 7=2, 8=3, 9=2 ans. 0,2,4,5
· 6. Whenever the ten cells have 2cells that are equal to the highest
value, and 1cell that is next to the highest value then record those
cell names in another cell. ie. 0=7, 1=5, 2=7, 3=4, 4=6, 5=5, 6=1, 7=2,
8=3, 9=2 ans. 0,2,4
· 7. Whenever the ten cells have 1cell that has the highest value, and
4 cells that are equal to the next highest value then record those cell
names in another cell. ie. 0=7, 1=5, 2=6, 3=4, 4=6, 5=6, 6=1, 7=2, 8=6,
9=2 ans. 0,2,4,5,8
· 8. Whenever the ten cells have 1cell that has the highest value, and
3 cells that are equal to the next highest value then record those cell
names in another cell. ie. 0=7, 1=5, 2=6, 3=4, 4=6, 5=6, 6=1, 7=2, 8=3,
9=2 ans. 0,2,4,5
· 9. Whenever the ten cells have 1cell that has the highest value, and
2 cells that are equal to the next highest value then record those cell
names in another cell. ie. 0=7, 1=5, 2=6, 3=4, 4=6, 5=2, 6=1, 7=2, 8=3,
9=2 ans. 0,2,4
· 10. Whenever the ten cells have 1cell that has the highest value, and
1 cell that has the next highest value and more than 3 cells that are
equal to the 3rd highest value, then record only the one highest and
one next highest cell names in another cell. ie. 0=7, 1=5, 2=6, 3=4,
4=5, 5=5, 6=1, 7=5, 8=3, 9=5 ans. 0,2
· 11. Whenever the ten cells have 2cells that are equal to the highest
value, and more than 3 cells that are equal to the next to the highest
value, then record only the two highest cell names in another cell. ie.
0=7, 1=5, 2=7, 3=4, 4=6, 5=6, 6=1, 7=6, 8=3, 9=6 ans. 0,2
· 12. Whenever the ten cells have 1cell that has the highest value, and
1 cell that has the next highest value and 2cells that are equal to the
3rd highest value, then record those 4 cell names in another cell. ie.
0=7, 1=2, 2=6, 3=4, 4=5, 5=5, 6=1, 7=1, 8=3, 9=2 ans. 0,2,4,5
· 13.Whenever the ten cells have 1cell that has the highest value, and
1 cell that has the next highest value and 3cells that are equal to the
3rd highest value, then record those 5 cell names in another cell. ie.
0=7, 1=2, 2=6, 3=4, 4=5, 5=5, 6=1, 7=5, 8=3, 9=2 ans. 0,2,4,5,7
There are 13 possible conditions within this formula.
The values in the ten cells are always a single digit number between 0
and 9 in each cell.
The other cell will always have between 2 and 5 single digits with
values between 0 and 9.
In the attached sample sheet, cells G through P are used as the ten
cell names (0-9) with single digit values to be compared to each other
for the highest values, and cell Q is the other cell where the answer
and the formula is placed.
Thanks
File Attached: http://www.exceltip.com/forum/attachment.php?postid=292659 (chart to be formatted.xls)