How do I exclude multiple low values from a formula?
I am trying to create a worksheet in excel to calculate my students' grades.
I need to exclude the two lowest marks from the string of values. I know that
using MIN I can remove the single lowest value, but how do I remove multiple
3,4,5,6,7 are the grades and the formula subtracts the two lowest grades of
=sum(your range) - min(your range) - small(your range,2)
give you the sum ex lowest two
divede by count(your range) - 2 to get average if desire
Message posted from http://www.ExcelForum.com
...formula based on continuous relative cells
I want formula where the value is the sum of the first cell i select plus
every 6th cell after it in the row and also minus every 5th cell in that row.
..."Millionaire Retires and Gives His Multi-Million Dollar Business Formula To You"
Millionaire Retires and Gives His Multi-Million Dollar Business Formula To You.
You'll even get a copy of his million dollar website by visiting
Only the first 52 people to visit the website will be accepted.
MAF Anti-Spam ID: 20060719221729Z2h0PvD7
Posted Via Usenet.com Premium Usenet Newsgroup Services
** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **
...Sort birthdays by month
Is there a way to sort a date field by month instead of year? It seems to
always sort by year whenever I change the format or try other things..
...Sort number as text
I have an excel 2000 file in column I, I has cost center
numbers, the range of numbers are as follows. 000, 050,
555, 850. I have made the cell text cells so they will
keep the preceding 0's. However when I sort them, they
come out in the following order. 555, 850, 000, 050.
To display numbers with preceeding 0's you don't need to change cell format
to text. Format the cell as Custom "000" instead.
When you need to convert numbers to strings somehow, use formula
=TEXT(CellReference,"000") in some adjacent column. NB! You can't use
converted value...help on sorting data
I have a soccer grid result. So I would like the column corresponding
to the points of the teams sorted descending. But I want the other
column to follow. And I want the first column (rank) to remain still
and not affected by the sorting operation. How can I do that ?
Just select the columns you want to sort, and then sort.
Microsoft MVP - Excel
Pearson Software Consulting, LLC
"Sam" <email@example.com> wrote in message
> I have a soccer ...sort text from the right-to-left
I need to sort several text data by their suffix, I mean, the characters
from the right-to-left order.
I think you need to give some examples of the data.
mvpearl omitthisword at verizon period net
"Santistas" <firstname.lastname@example.org> wrote in message
> I need to sort several text data by their suffix, I mean, the characters
> from the right-to-left order.
> Any help?
> I think you n...What instead of an array formula part 2
Ive posted this question before (orignal post below) but i did not describe
my problem correctly so i will try again.
Our reporting system will generate a "RAW" Excel file, the excel document i
have created uses the formula below to add up figures and give a total for a
particular mix of categories.
e.g. of the RAW data headings
Company Type DataType Branch Division Dept Sub Dept
this data is then "pivoted" and filtered with the array formula
The rows are determined by the type col
The Cols are determined by a combination of Type...how do i write a formula and keep in in formula form, so it DOESN.
i have to take a computer test and one of the questions is to write a formula
i've previously used but write it so the actual formula is shown on the sheet
and when its printed so how do i do i write the formula because everytime i
do it it calculates but i need it to stay in formula form with the = sign too?
Precede it with an apostrophie:
>i have to take a computer test and one of the questions
is to write a formula
>i've previously used but write it so the actual formula
is shown on the sheet
>and w...I wrapped a two line cell. I sorted but the wrapped cell didn't g.
I am putting a list of names on excell. Some have to go on two lines in one
cell. I wrapped that cell. But when I sort data from ascending to
descending the wrapped cell does not go with that info. Any suggestions
When I format a cell with Wrap Text, and sort the list, the formatting
stays with the cell. Are the values typed in your list, or are they
links to other cells?
Mike's cells wrote:
> I am putting a list of names on excell. Some have to go on two lines in one
> cell. I wrapped that cell. But when I sort data from ascending to
> descending the wrapped cell does...READ THIS UPDATED POSTING: Problem using fill with formulas
(Previous posting contained an error in the formula.)
I have a column. I want to fill in the formula from B1 all the way
down to B200. I select B1 and drag my mouse down to B200 and select
Fill Down. The formula is inserted in every cell. However, I have:
when I want the second instance to be:
In other words, I want the first cell reference (A) to change, but not
the cell references in the yahoo.xls worksheet. Because they are
changing, I need ...Listview/Treeview
I am trying to do a listview or treeview in Access 2003 that allows the
users to sort the entries using drag and drop.
It should work the way Internet Explorer handles its favorites or Windows
handles drag and drop in its Start Menu.
You can drag one entry in between others and the drop target is represented
by a line.
Is there a way to replicate the behaviour of sorting via drag and drop in
All I can do now (die to some help in the german group) is to drag one
element on top of another element but that makes sorting a mess. I need a
way to drag one element BETWEEN ...changing formula
You could use the Find-Next button in that dialog to step over those that
you don't want to change. Not perfect, but probably quicker than manually.
Other than that it is VBA.
"Alien" <email@example.com]> wrote in message
> Thanks, But I only want to change the values in one cell. I did that
> and it made 1070 replacements when I only wanted it to make about 15
> On Fri, 8 Aug 2003 13:23:48 +0100, "Bob Phillips&q...How can you set a cells data using a formula?
I would like to set a cells data using a formula... is this possible?
For example is there a function to set cell A1 to have the value "Bob"
"Moloth" <Moloth@discussions.microsoft.com> wrote in message
>I would like to set a cells data using a formula... is this possible?
> For example is there a function to set cell A1 to have the value "Bob"
Note that a function can only r...Control formula calculation
I have created a "form" where a user enters 2 separate numbers. I wan
each of these numbers to input to a running total and the total t
subtract from another number... this is what I have come up wit
(understand it is a circular ref ..)
User input 2 User input 2
Prior input 0Prior input 0
Total input 2
Message posted from http://www.ExcelForum.com
...New at Formulas
I'm not really sure how to explain this, but I'll do my best.
Cell A1 = 10 - Buy
Cell B1 = 20 - Current
Cell C1 =SUM(B1-A1) <-- which would be 10 - UnProfit
Cell D1 =SUM(B1-(C1*10%)) <-- which would be 19 (10% of 10 is 1, 20
minus 1 is 19) - Trailing Stop
Cell E1 =IF(B1<=D1,"YES") <-- if 20 <= 19 show YES, which it wouldn't
because it's not.
This is fine and works, but what I want to be able to do is store the
amount in D1 somewhere so that, if I change B1 to equal 15, the
formula in E1 is actually working this out on the previous amount in
D...Sort calculated field
In a query I have a field that calculates the age.
Age: IIf([CloseDt] Is Not Null,"CLOSED",(Date()-([BCBSreceived])))
I need to shows these in Decending, I assume it's a format issue, because it
How can I make a calculated field sort?????
Sort on this field instead. You can uncheck it so that it doesn't show. Still
show your field below.
SortOn: CDate(IIf([CloseDt] Is Not Null,#1/1/1800#,[BCBSreceived]))
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
"Dan @BCBS&q...formulas to change font
I am new to this - I would like to know how to change the font color
conditional to a formula result.
for example =if((A1<than 0,"font red", "font blue")
I am familiar with some very simple formulas - but I would like to
understand how to perform the above.
Please don't laugh - but I would like to be directed to the right place to
learn how to do it?
If you want to change font color in cell A1, first change the font color to
Type "0" (zero) in cell A1. Along the top of your excel you will see an A
with a color under it. Typical...Help please with formulae!!!
I'm hoping someone can help me... all of a sudden my
excel worksheets won't copy formulae from one cell to the
next - only the value copies.
If I manually insert the formula for a total, any changes
within the data range are not reflected in the total.
Can you please help??
A sheet behaves like that if it's protected and the formula cells are marked
Locked and Hidden. Try unprotecting the sheet.
HTH. Best wishes Harald
Followup to newsgroup only please
"Jen" <Jen@discussions.microsoft.com> skrev i melding
news:0dad01c425f9$8c8d2040$a4...Problem with VBA returning the contents of a long formula.
I have run across a problem with getting a formula from VBA.
Excel specifications state that a worksheet formula can have a maximum
length of 1024 characters including the leading "=".
>From the simple test that I did, it looks like VBA will not return
formulas that are longer than 1022 characters including the leading
And an even stranger result - I found a formula of length 901
characters including the leading "=" which could not be entered on a
I have included the VBA code that I used to create the examples.
Any ideas as to what is going...Become formula.
A B C D E
1 1 1 1 1
2 3 4 5 6
3 6 10 15 21
4 10 20 35 56
5 15 35 70 126
6 21 56 126 252
7 28 84 210 462
8 36 120 330 792
9 45 165 495 1287
10 55 220 715 2002
"A:A" is serial numbers.
"B1"=SUM(A$1:A1), "B2"=SUM(A$1:A2), "B3"=SUM(A$1:A3),...
Drawing "B1" to fill other cells.
I want a formula/function, when call it by (X,Y),
Formula/Function counting that Cells(X,Y) value.
without list all process by Worksheet.
One way ..
Assuming ...Sort by Invoice Number/Payment Number
In the Inquiry -> Purchasing -> Transactions by Vendor window
Many of our customers would like the associated INV type and PMT type to
sort together, not sort by document number, document date or document type.
The invoice # and check # are usually different, as are the invoice date and
the check date.
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the sug...Sort text cells with diacritics in Excel
Hello, I need to sort text cells with diacritics, like words with acute (Á)
and carron (Č).
eg. I have words Acam, Ácam, Acom, Ačom... and I need to sort them like
this: Acam, Acom, Ačom, Ácam... Please help, thanks.
Why would Ačom come before Ácam?
Is this type of sort a common need in your profession/industry/location?
Are you able to use VBA code (macro)?
Portland, Oregon USA
wrote in message
Hello, I need to sort t...Testing for an Integer result in a formula
I'm writing a nested IF statement in which one step needs to test the value
of one cell divided by another cell, and determine if it is an integer before
it completes the calculation.
So, IF cell R10 / I13 = INTEGER, then I'll perform a Future Value calc. IF
NOT = I want to do nothing.
I tried ISEVEN, but when I turned on the Analysis Took Pak nothing happened.
My spreadsheet still did not recognize the ISEVEN.
Hope this helps.
On Dec 4, 3:23=...sum formula not working, producing incorrect answers Excel 2003
I am a pretty competent user of Excel, but on some occassions, a simple
formula like SUM will not work. Whether doing it using the Sum (Sigma) icon
or creating the formula in the formula bar (ex. =A12+A13) it is not working.
I have tried clearing and deleting the cells and recreating them, it doesn't
matter. If I redo the formula with the actual number values in each cell it
will work but it is as if it isn't reading the values in the cells properly.
I have tried looking at the cell properties and can not find anything
obvious. Please help. Thank you. - CG
It might be a ...