Formula Find Latest Version

[darkfreedoom]

The image is just example of what i need.
I would like to know is there any formula that can return value as shown in the RED cells?

for example, WORD, in the table we can see that the latest version is "3".
in column "G", instead of enter the value "3" manually, i would like to create a formula to return the value "3".

any expert can help me?

OS: Window XP SP3
Excel: 2007

 

[Aladin Akyurek]

The image is just example of what i need.
I would like to know is there any formula that can return value as shown in the RED cells?

for example, WORD, in the table we can see that the latest version is "3".
in column "G", instead of enter the value "3" manually, i would like to create a formula to return the value "3".

any expert can help me?

OS: Window XP SP3
Excel: 2007

G3, just enter and copy down:

=LOOKUP(9.99999999999999E+307,B3:F3,$B$2:$F$2)

 

[Michael M]

Try

=COUNTIF(B3:F3,">=0")

 

[darkfreedoom]

G3, just enter and copy down:

=LOOKUP(9.99999999999999E+307,B3:F3,$B$2:$F$2)

thx, it works.
But may I know what
9.99999999999999E+307
stand for?

 

[Aladin Akyurek]

thx, it works.
But may I know what
9.99999999999999E+307
stand for?

Here are some links on LOOKUP(BigNum,lookupvector,[resultvector]...

http://www.mrexcel.com/forum/showthread.php?t=102091
http://www.mrexcel.com/forum/showthread.php?t=18954
http://www.mrexcel.com/forum/showthread.php?t=310278 (Post #7)

Hope this helps. <!-- / message --><!-- sig -->

 

[darkfreedoom]

Try

=COUNTIF(B3:F3,">=0")

thx, it works if i have every version of it.
But it won't work if i skip a previous version.

say, i have version 2, and 3 but dun have version 1.
your formula will return "2", instead of "3"

thx anyway. i learn a new formula from you.

 

[Michael M]

My apologies.....I wondered why Aladin had such a difficult formula for a simple count..
You'll notice that the version No is also the number of entries in your table, which confused me.....obviously not so with Aladin.

 

[darkfreedoom]

it seems like my example does not fit my work
here i post a part of my work here. Hope expert will help.

same problem,
I would like to know is there any formula that can return value as shown in the RED cells?

Note: The 3 yellow cells are merge.

here's the example excel file.

 

[Aladin Akyurek]

For A4, you conclude to Rev. B. What supports that conclusion, put otherwise, on what is that based?

 

[darkfreedoom]

For A4, you conclude to Rev. B. What supports that conclusion, put otherwise, on what is that based?

those yellow cells in Row1