VBA or Formula Needed ????

[dkenebre]

The vlookup table automatically updates when a the next destignate
AF:AH orange background is updated.
I now need help with a formula or VBA that will identify from thi
table EC,ED,EE,EF;
1. which digit (0-9) in the EC column has the highest value in the E
column and
place that digit in the next orange row's W cell (for example W436).
2. which digit (0-9) in the EC column has the highest value in the E
column and
place that digit in the next orange row's X cell (for example X436).
3. which digit (0-9) in the EC column has the highest value in the E
column and
place that digit in the next orange row's Y cell (for example Y436).
Then,
4. which digit (0-9) in the EC column has the highest-low value in al
3 columns (ED, EE, and EF)
within the table and place that digit in the next orange row's Z cel
(for example Z436).
Already set everytime a 0-9 digit is entered into AF:AH orang
background cells, which occurs every 2 or 3 rows, the values in tabl
with the 10 rows of data in EC, ED and EF columns are dependent and ar
updated with new values.
These new values are to be compared by the next W,X,Y, and Z row wit
the orange cells AF:AH. Therefore, the W,X,Y and Z cells will retur
different values from one destignated row to the next

Attachment filename: chart4.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=40634

 

[Harlan Grove]

You've now posted several original messages to the Excel newsgroups over the
past few months in which you've referred to attached files. Unless you've
received answers via private email, you're not getting many answers. You need to
learn how to express the *entire* problem in PLAIN TEXT in your messages. Few
sensible people will look at your attached files.

 

[dkenebre]

Thanks Harlan for the feedback. I am not familiar with the lingo but
take another crack at the explanation:
I need a formula or VBA that can do the following:

How can I make cell W look at ED3:ED12 and find the cell with th
highest value.
If has the highest value is in ED3, then W=0, If ED4, then W=1, If
ED5, then W=2, If ED6, then W=3, If ED7, then W=4, If ED8, then W=5
If ED9, then W=6, If ED10, then W=7, If ED11, then W=8, If ED12
then W=9.

How can I make cell X look at EE3:EE12 and find the cell with th
highest value.
If has the highest value is in EE3, then X=0, If EE4, then X=1, If
EE5, then X=2, If EE6, then X=3, If EE7, then X=4, If EE8, then X=5
If EE9, then X=6, If EE10, then X=7, If EE11, then X=8, If EE12
then X=9.

How can I make cell Y look at EF3:EF12 and find the cell with th
highest value.
If has the highest value is in EF3, then Y=0, If EF4, then Y=1, If
EF5, then Y=2, If EF6, then Y=3, If EF7, then Y=4, If EF8, then Y=5
If EF9, then Y=6, If EF10, then Y=7, If EF11, then Y=8, If EF12
then Y=9.

 

[Frank Kabel]

Hi
see below some possible solutions for your problems

Frank

How can I make cell W look at ED3:ED12 and find the cell with the
highest value.
If has the highest value is in ED3, then W=0, If ED4, then W=1, If
ED5, then W=2, If ED6, then W=3, If ED7, then W=4, If ED8, then
W=5, If ED9, then W=6, If ED10, then W=7, If ED11, then W=8, If
ED12, then W=9.

=MATCH(MAX(ED3:ED12);ED3:ED12)-1
Problems may arise then there are more then one maximum value
(duplicates)

How can I make cell X look at EE3:EE12 and find the cell with the
highest value.
If has the highest value is in EE3, then X=0, If EE4, then X=1, If
EE5, then X=2, If EE6, then X=3, If EE7, then X=4, If EE8, then
X=5, If EE9, then X=6, If EE10, then X=7, If EE11, then X=8, If
EE12, then X=9.
=MATCH(MAX(EE3:EE12);ED3:ED12)-1

How can I make cell Y look at EF3:EF12 and find the cell with the
highest value.
If has the highest value is in EF3, then Y=0, If EF4, then Y=1, If
EF5, then Y=2, If EF6, then Y=3, If EF7, then Y=4, If EF8, then
Y=5, If EF9, then Y=6, If EF10, then Y=7, If EF11, then Y=8, If
EF12, then Y=9.

see above

 

[dkenebre]

Thanks Frank, this formula update returns the correct answer, but
whenever the values change in ED3:ED12, it should only change the next
W cell. For example:

If 1st occurrence
ED3:ED12, the highest value was 25 in ED8, ans=5, therefore, W28=5
If 2nd update to ED3:ED12,
the highest value was 23 in ED5, ans=2, therefore, W30=2

How do I accomplish this? I don't want W30 to equal 2, then W28 change
from 5 to equal 2 as well.
Everytime I add a 0-9 digit in a destignated AF cell, the values in
ED3:ED12 change.

 

[dkenebre]

Let look at this another way:
Is there a formula or VBA that calculates which 0-9 digit’s last
occurrence was before all other remaining 0-9 digits, in only the
previous AF cells with orange background. This requirement is the
same for AG and AH cells.
For example,
if #0 last occurrence in a previous orange background AF cell was 1
previous row
and #1 was 2 previous rows, #2 was 3 previous rows, #3 was 4, #4 was 5,
#5 was 6, #6 was 7, and #7 was 10, and #8 was 9 and #9 was 8, then #7
would be the answer, because it last occurrence was before all other
digits in the previous AF orange background cells. It has the highest
number of rows (10).

 

[Frank Kabel]

Hi
to be honest now I'm totally lost
I did not understand your problem. Maybe you can explain your business
problem behind this descriptionso that I / the NG understand what you
want to achive.

Frank

 

[dkenebre]

I am attempting to analyze to activity or inactivity of randoml
generated numbers between 0-9. I want to know a certain point whic
digit between 0 and 9 has not occurred the longest time or in the mos
entries. Does this answer your question

 

[dkenebre]

Frank or anyone,

I am attempting to analyze to activity or inactivity of randoml
generated numbers between 0-9. I want to know a certain point whic
digit between 0 and 9 has not occurred the longest time or in the mos
entries. Does this answer your question?

I want to Show which numbers (0-9) in each position (AF, AG and AH
have been out the longest since occurrence, followed the number o
orange background rows that have elapsed since they last occurred. Ca
anyone help?
so,
Is there a formula or VBA that calculates which 0-9 digit’s las
occurrence was before all other remaining 0-9 digits, in only th
previous AF cells with orange background. This requirement is the sam
for AG and AH cells.
For example,
if #0 last occurrence in a previous orange background AF cell was
previous row
and #1 was 2 previous rows, #2 was 3 previous rows, #3 was 4, #4 was 5
#5 was 6, #6 was 7, and #7 was 10, and #8 was 9 and #9 was 8, then #
would be the answer, because it last occurrence was before all othe
digits in the previous AF orange background cells. It has the highes
number of rows (10) or longest out since last occurrence