[mso] Re: Excel
- From: "Glenda Wells" <gwells@xxxxxxxxxxx>
- To: <mso@xxxxxxxxxxxxx>
- Date: Tue, 15 Jul 2003 16:28:01 -0400
cool. the first one, the running sum I should have figured out for
myself. picture the large dent in my forehead on that one. the second
problem works perfectly the way you 'splain it. thanks. /g
-----Original Message-----
From: Ray Blake [mailto:ray@xxxxxxxxx]=20
Sent: Tuesday, July 15, 2003 4:29 PM
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: Excel
OK. First one first.
Set up your sheet like this:
A B
1 25 =3DA1
2 15 =3DB1+A2
3 5=09
4 10=09
5 15=09
Now select cell B2 and use the fill handle (accessible if you hover over
the lower right hand corner of the cell until a tiny cross appears) to
drag the formula down to the bottom of the column. This will result:
A B
1 25 =3DA1
2 15 =3DB1+A2
3 5 =3DB2+A3
4 10 =3DB3+A4
5 15 =3DB4+A5
Onto the second problem, then.=20
The LARGE formula will find the nth largest value in a range, amd it's
that function we can use here, thus:
A B C D E F
G
1 mike 95 90 85 70 =3DLARGE(B1:E1,1)
=3DLARGE(B1:E1,2)
2 bill 70 80 80 85 =3DLARGE(B2:E2,1)
=3DLARGE(B2:E2,2)
3 jane 100 95 95 100 =3DLARGE(B3:E3,1)
=3DLARGE(B3:E3,2)
But, I notice that by wanting to keep 100 and 95 for Jane, you actually
want to keep the highest 2 unique values, rather than just the 2
highest, which would have been 100 and 100.
So in column G, you'll have to take into account the possibility of a
joint largest value. Taking cell G1 as an example, you need to change it
to:
=3DIF(LARGE(B1:E1,2)=3DLARGE(B1:E1,1),LARGE(B1:E1,3),LARGE(B1:E1,2))
This function checks to see if the targest and second larget are the
same. If so, it returns the third largest, if not the second. By
extension, you could also cater for three equal highest values, but it
starts getting silly beyond 4 or 5!
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, send an email to
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the quotes) in
the subject line.
Or, visit the group's homepage and use the dropdown menu. This will also allow
you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with
instructions. Once you are a member of the files group, you can go here to
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************
Other related posts:
