Free Microsoft Excel 2013 Quick Reference

Conditional formulas in excel Results

Microsoft ExcelHow Can I Import a Fixed-Width Data File into Microsoft Excel?How Can I Sort a Spreadsheet By Cell Color?How Can I Check the Value of a Specific Cell in a Bunch of Excel Spreadsheets?How Can I Mark the First Occurrence of a Number in an Excel Spreadsheet?How Can I Read Custom Summary Information Properties for an Excel File?How Can I Change the Color of a Spreadsheet Cell Based on a Range of Values?How Can I Import Multiple Worksheets into an Access Database?How Can I Search For Values in an Excel Worksheet?How Can I Cut a Row From One Excel Spreadsheet and Paste That Row Into Another Spreadsheet?How Can I Determine the Value of the Last Cell in an Excel Range?How Can I Convert an Excel Serial Number to a Date and Then Back?How Can I Insert New Rows Above the Last Row in an Excel Spreadsheet?How Can I Copy Data from Excel to PowerPoint?How Can I Retrieve Hyperlink Information From an Excel Spreadsheet?How Can I Insert Blank Rows (and Formulas) Into an Excel Worksheet?How Can I Update and Then Break All the Links in an Excel Spreadsheet?How Can I Open All the Excel Spreadsheets in a Folder and Run a Specified Macro Found in Each of Those Spreadsheets?How Can I List the Addresses in My Sent Items Folder in an Excel Spreadsheet?How Can I Add a Background Picture to an Excel Worksheet?How Can I Replace Numeric Values in an Excel Spreadsheet?How Can I Copy Data From One Spreadsheet to Another?How Can I Replace Text in an Excel Spreadsheet?How Can I Set the Print Area in an Excel Spreadsheet?How Can I Delete Specified Rows in an Excel Spreadsheet?How Can I Color Every Other Row in an Excel Spreadsheet?How Can I Copy Selected Columns From a CSV File to an Excel File?How Can I Change the Background Color of Spreadsheet Rows That Have a Date in a Specified Cell?How Can I Export an HTA Table to Excel?How Can I Delete All the Worksheets in a Spreadsheet Except for the First Worksheet?How Can I Assign a Background Color to Cells in a Spreadsheet and Then “Sum” Those Cells?How Can I Sort a Row in an Excel Spreadsheet?How Can I Remove the Password When Opening an Excel Spreadsheet?How Can I Import an Excel Spreadsheet Into an Access Database?How Can I Save a Table in an Access Database as a Spreadsheet?How Can I Use a Blank Row to Separate Data in an Excel Spreadsheet?How Can I Specify the Number of Decimal Places to Display in an Excel Spreadsheet?How Can I Configure Excel to Autosave Every 5 Minutes?How Can I Save an Excel Chart as a Picture?How Can I Change the Font Color in Excel If a Specified Condition is Met?How Can I Convert an Excel Spreadsheet to XML?How Can I Set the Default File Path in Excel to a User’s Home Directory?How Can I Compare a List of Names in One Excel Column to a List of Names in Another Column?How Can I Convert a Tilde-Delimited File to Microsoft Excel Format?How Can I Search Active Directory for User Names Stored in an Excel Spreadsheet?How Can I Add a Total Row to an Excel Spreadsheet?How Can I Create a Custom Date Format in Microsoft Excel?How Can I Keep the Screen From Updating While Running an Excel Script?How Can I Use Information in an Excel Spreadsheet to Rename a Set of Folders?How Can I Determine the Background Color of a Spreadsheet Cell?How Can I Make the First Character in a Cell Uppercase and All the Other Characters Lowercase?How Can I Copy a Worksheet to a New Spreadsheet?How Can I Use Windows PowerShell to Automate Microsoft Excel?How Can I Tell If a Specified Worksheet Exists in an Excel Workbook?How Can I Add a COUNTIF Formula to an Excel Spreadsheet?How Can I Copy All the Comments From an Excel Worksheet to a Word Document?How Can I Change All the Lowercase Letters in an Excel Worksheet to Uppercase Letters?How Can I Create a New Excel Spreadsheet at Midnight Each Night?How Can I Select a Column of Data in Excel and Then Paste that Data into a Text File?How Can I Convert a Number to a Date in Excel?How Can I Locate and Replace Information for a Specific Item in a Spreadsheet?How Can I Save an Excel Spreadsheet, and Then Save a Copy as HTML?How Can I Format an Excel Spreadsheet So It Retains Leading Zeroes?How Can I Determine the Last Row in an Excel Spreadsheet?How Can I Remove All the Formatting from an Excel Spreadsheet?How Can I Change the Footer in an Excel Spreadsheet?How Can I Add Additional Worksheets to an Excel Workbook?How Can I Sort Worksheets in a Workbook?How Can I Import a Fixed-Width File into Microsoft Excel?How Can I Add a Hyperlink to an Excel Spreadsheet?How Can I Build an Array from a Column of Data in Excel?How Can I Copy Column C of One Worksheet to Column A of a Second Worksheet?How Can I Center Text in an Excel Cell?How Can I Change the Background Color of a Cell Depending on the Date?How Can I Delete Data from a Spreadsheet Yet Keep All the Formatting?How Can I Open an Excel Workbook and Retrieve the Names of All the Worksheets?How Can I Tell if an Excel Workbook is Open and, If It Isn’t, Open It?How Can I Insert a Column into a Spreadsheet?How Can I Save a Single Excel Worksheet to a CSV File?How Can I Password-Protect an Excel Spreadsheet?How Can I Make Changes to and Then Re-Save an Existing Excel Spreadsheet?

Hello, I would like to know whether it is possible to hide either rows or
columns by writing a command in a cell? Overall, I would like rows and
columns to be created, deleted, or hidden under specific conditions. Is this
possible? If so, how?
Thanks

I have a calendar in excel that I want to change the format of the header for
each month, when that month is the current month.

Right now my conditional format formula is =Q31=MONTH(TODAY())

The problem is that as long as I am in (for example) November of any year
the condition reads true. It does not distinguish between 11/2005, 11/2006,
11/2007.

I'm stuck at this point... thinking the conditional format formula should
look like:
=q31=AND(MONTH(TODAY()),YEAR(TODAY()))

But that's not working.

Can you help? Thanks in advance.

Scott

Automatic data extraction, calculating and copying results using condition (numerical and/or other) to another Excel workbook (containing not constant count of worksheets).

Basically what I want to do is to make as automatic as could be data extraction from alphanumeric strings (in ““source” ” workbook, raw data worksheet column “H” – added as an example), calculate final results based on several criteria in alphanumerical excel cells. [The row count in “source” is not constant – it may vary.]

And then automatically search in “result” workbook trough all containing worksheets and copy results from “source” to “ABN” column in “result” workbook worksheets (basing on reference in “source” – in this case Account No. column and the same reference in “result” workbook worksheets in column B).

How far I am:
1)found the way to extract needed numbers from alphanumerical strings in separate columns (basing on conditional and mid formulas);
2)made condition based calculations;
3)using vlookup, if and reference numbers copied results to “result” test workbook, but it’s all still more or less manual work;

Even this seemingly tiny advancement saved me 6 hours of work (for 1-2 “result” workbook worksheets, where I had to make manual calculations for every reference cell, and then manually write the result according to reference in “source” data reference.

Problems encountered (couldn’t deal with):
1)the reference numbers contain duplicate values in the same column (so vlookup takes only first encountered referenced result and applies to all duplicates in “result” workbook), but different results (which are needed in final worksheets), I was thinking about implementing second condition (basically possible, but couldn’t find the way to make vlookup use it) or add the rule that if no result found in first row all 3 columns then search in next row (in array of repeating references), then it would be possible to order results in different columns, but as I’ve no idea how should this rule look, so I was thinking maybe it’s possible to implement dropdown list for those duplicate reference results (to be able at least manually choose which result goes where – but as I’m not too skilled in excel couldn’t figure that out);
2) in “result” workbook repeating references are in the same worksheet (by meaning that reference numbers are specific to each worksheet);
3)it may happen that reference number in “source” workbook doesn’t contain according reference in “result” workbook worksheets, and/or reference number in “result” workbook worksheets doesn’t have according reference to “source” worksheet (happens frequently), in this case results (in ABN column) should be replaced with 0;

As I’m not familiar with VBA/Macros, but I have heard that with VBA you can do almost everything. So my idea is VBA, where you can choose file one as “source” , make all needed extractions and calculations (automatically), and choose file two where to put these results.

Maybe you have some idea, and could give me some helping hand with this one? Thanks in advance.

Couldn't add trough add attachments.

http://www.filedropper.com/result_3
http://www.filedropper.com/source

<a href=http://www.filedropper.com/result_3><img src=http://www.filedropper.com/download_button.png width=127 height=145 border=0/></a><br /><div style=font-size:9px;font-family:Arial, Helvetica, sans-serif;width:127px;font-color:#44a854;> <a href=http://www.filedropper.com >file upload storage</a></div>

<a href=http://www.filedropper.com/source><img src=http://www.filedropper.com/download_button.png width=127 height=145 border=0/></a><br /><div style=font-size:9px;font-family:Arial, Helvetica, sans-serif;width:127px;font-color:#44a854;> <a href=http://www.filedropper.com >upload files online</a></div>

Excel 2003 system.

i've looked through several pages of past threads and found some that are similar to this, but none that are exactly the same, i swear. please don't chastise me for posting a repeat.
i have cells C26:C30 containing percentages. these 5 percentages should add up to 100%, (or simply 1 in excel think).
how would I write a formula in C25 that says:
IF SUM(C26:C30)=1, turn C25 green, when SUM(C26:30) is <1<, turn C25 Red.

How would i go about doing this? thank you very much in advance.

I can't seem to figure out the forumla to return the text in a cell if certain criteria is met. What I am trying to do is look at a list of invoice numbers in column A and if there is an exact invoice number match in column B then return the text in Column C. If there is no match then return "N/A". My condition formula would be in Column D (my invoice #'s have letters so excel is not reading them as values)

Example:

A B C D
P105-2345 P105-0000 Champs, inc. N/A
P105-2222 P105-0550 McDonalds N/A
P105-0000 P105-1111 Wendy's Champs, inc.

What would be my formula in column D so it retuns the customer name for the corresponding invoice in column B if column A = column B?

Your help is much appreciated!

I use XP professional Operating System and Excel 2003

I am referencing a cell in order to change the background color of a row of cells if the number in column B is = to the number in a specific cell.

The formula used in the conditional formatting is =$B73=$B$10

The reference cell $B$10 contains the following formula

=INT(MAX('Assumptions & Inputs'!F8:F9))

The conditional formatting worked fine when I used that formula.

I had to change the formula in cell $B$10 so that it would meet a new criteria. When I did that the conditional formatting would not change the color of the row.

The new formula that I put in the reference cell $B$10 is

='Assumptions & Inputs'!$D$8+MAX('Assumptions & Inputs'!$F$8-'Assumptions & Inputs'!$D$8,'Assumptions & Inputs'!$F$9-'Assumptions & Inputs'!$D$9)

How do I make the conditional formatting work?

Outstanding Debt (A1) is given
Interest Rate (A2) is given
Period (A3) is given
Interest Payment (A4) is calculated in a formula using the values in A1, A2,
and A3
Principal Payment (A5) IS WHAT I AM TRYING TO SOLVE FOR
Outstanding Debt #2 (B1) is A1 - A5
Interest Payment #2 (B4) is calculated in a formula using the values in B1,
A2 and A3

The problem is that I am trying to find the largest number for A5 with the
following conditions:

A5 must be multiple of 5,000
The sum of A4, A5 and B4 must come as close to 6,172,000 without exceeding
that number.

Any ideas on how I might accomplish this in Excel?

I've designed a simple spreadsheet to track grades using Excel 2003.

Each specific assignment/quiz grade for a student is in a separate column.

The beginning row of each class contains the highest grades possible for
each assignment/test/etc.

Sample:

Name Rank Cumulative Offset Gr1 Gr2
G3 G4.

1 Possible Score 260
100 20 40 100

2 Student 1 196 40
80 20 E 96

3 Student 3 206
86 20 30 90

4 Student 2 190 60
90 E E 100

..

To determine the grade for each student, I've created a formula (in the
"Rank" Column) that calculates a percentage of the cumulative highest score
possible.

This works fine, except sometimes I excuse a student from an assignment. To
compare their total scores to the cumulative highest score possible would be
unfair, so to accommodate for this, I've added a column labeled "Offset".
For each student in this situation, I put an "E" (for "excused") in
appropriate column. Then I manually put the corresponding high score in the
Offset column for any student who has been excused from a given assignment.

Here's the formula I'm using: =ROUND(((100*C2)/($C$1-D2)),0) ----- Where
C2 is the total of all scores for a given student, $C$1 is the cumulative
highest score possible, and D2 is the offset value if any. (Finally I use
VLOOKUP to insert a letter grade in another column.)

QUESTION:

Is there a way to accommodate this "Excused" situation with a some sort of
conditional formula so I don't have to manually assign an "Offset" for those
students. (maybe with SUMIF?) For example: If "E" is a student's score
for a given assignment, then subtract the highest possible score for that
assignment. Or if a cell is not "E" then include the highest possible score.

Any suggestions would be greatly appreciated.

Thanks!

Sounds a dumb question, but i cant find this simple answer
What I want to do is test the condition in 2 cells, and if they are true place the word ERROR into another cell

So if A1 = True
and A2 -True
Them place error into A3
BUT i dont want to put the formula into A3
what I am trying to use is this.....
=IF(AND(A1="NO",A2="NO,A3="ERROR"
But I cant find the correct formula, I CANT PUT FORMULA IN A3

Anyone got any good ideas?

ta

G

I'm working on a travel requirements spreadsheet. I'm quitting my job soon and it will probably be several years before they can find someone qualified to fill my job. My tasks will be divvied up among several people and I'm trying to develop a series of tools (Excel and otherwise) that will allow them to get by in the interim.

One of these items is a travel requirements worksheet. Essentially it's a table of people with relevant information. I have a number of calculations already to calculate various cost estimates. What I need now is essentially to fill the contents of one cell to specify which reimbursement form to complete based off city of residence AND distance. I thought I had a good idea of how to proceed with formulas, but the longer I tried to do the formula the more confused I've gotten.

"Table2" contains the data we're working with (this table may expand or shrink depending on personnel assignments). Multiple columns, but the only columns I'm focused on for this are:
'N' - Where I need the data to fill to (text specific based on other columns); [Header:VOUCHER_VER]
'J' - The city they live in; [Header:ADDRESS_CITY]
'M' - The distance (miles) from their home to permanent work site ("SAN ANTONIO"). [Header:DISTANCE_WS]

What I'm trying to do is get the following result for each individual (which will update if they change address info):

* If greater than or equal to 50 miles ('M'), regardless of city ('J'), then fill, "Zone 2 (Complete Voucher)" in 'N'.
* If less than 50 miles ('M') AND city ('J') is "SAN ANTONIO", then fill, "Zone 1A (Local, No voucher)" in 'N'.
* If less than 50 miles ('M') AND city ('J') is NOT "SAN ANTONIO", then fill, "Zone 1B (Mileage Only, No Voucher)" in 'N'.

Does that make sense? I do not care if it is a formula or requires VBA (I need to expand my horizons, anyway, and VBA might help reduce the chance of them overwriting a formula typed directly in the cell). I also intend to apply conditional (fill) formatting to each row based on which of the three options are filled in 'N' to help them better identify a person's status at-a-glance.

I'm certain this is possible. I'm just apparantly not quite up to the task all by myself.

--------------------------------------------------------------------------------

This VBA & conditional formatting highlights the row & column of the cell you select in the sheet, quite useful when dealing with a lot of columns & rows.
I recognize the symptoms of my problem, in Excel 2003 if your VBA was messed up, the conditional formatting worked but you had to leave the worksheet and return for the formatting to update, this is what it is doing now but I cannot get the VBA and/or conditional formattine to cooperate in Excel

Steps I used to insert VBA:

Press Alt F11
locate file     
select MS Excel Objects
double click ThisWorkbook
left pull down menu - select workbook
right pull down - SheetSelectionChange
Add "Application.ScreenUpdating=true

VBA FORMULA SHOULD LOOK LIKE THIS
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Application.ScreenUpdating = True
End Sub

Conditional Formatting steps I used:

SELECT WHOLE WORKSHEET FOR THIS CONDITIONAL FORMATTING FORMULA
FORMULA IS:
=OR(AND(CELL("row")=ROW(),COLUMN()<=CELL("col")),AND(CELL("col")=COLUMN(),ROW()<=CELL("row")))

Can anyone tell me why this will work in Excel 2003 and not in Excel 2007. I have repeatedly checked accuracy in both versions, no joy in the 2007 version.

Thanks in advance,
Mark

Hi Everyone,
I hope I'm posting this in the right section of this forum.
Have been using Excel Spreadsheets with simple formulas in a while at work but now I was going to use Excel in a way I do not know how to get it
working for me. I have no clue about VB Scripts or Makros in general or creating formulas with LookUp or Check if a cell value if True or False (Cell is
filled or empty).

Here is what I need and I hope someone in this forum can help me out.

From a supplier I will get a spreadsheet including the the following Information which I need to convert into an url including trailing slash (/) for an online store.

Column A = Value of Main Cartegory (e.g. Holidays)
Column B = Value of Subcategory_1 (e.g. Christmas Decor)
Column C = Value of Subcategory_2 (e.g. Candle Holders)

Those raw data are without any trailing slashes.
Now I want to create a column e.g. Column D (product_url) which should be a summary of column A thru C but if there is only a Value for Main Category
there should be no trailing slash,
if there is a Value for Main Category and a Value for Subcategory_1: no trailing slash after Subcategory_1 but trailing slash between Main Category and Subcategory_1.

The result should look like this:
Holidays (Main category)
Holidays/Christmas Decor (Main category & Subcategory_1)
Holidays/Christmas Decor/Candleholders (Main category & Subcategory_1 & Subcategory_2)

Is it possible to create something like that in Excel at all since all values will only be text and the trailing slash is actually functional for Excel calculations but in this case it would have to be treated as "text".

Any help will be greatly appreciated since I am talking about more than 3500 products (and increasing) I will have to create the right url's for.
Thank You
verdecove

I'm looking for my commandbutton2 to execute the code below on click. What this will do is search range L17:L116, and see if the hlookup value is less than hte 2nd hlookup value. If both of these are true in ANY row from 17 to 116, the row in the ranges B17:J116 and L17:N116 (column K is locked) will clearcontents. That's the first objective. The second objective is to eliminate any blank rows. So any rows that are cleared by this criteria, all other rows that have values will be shifted up right after. I still need all rows 17 to 116 to be able to enter data, so the row can't be deleted. The blanks will essentially get moved to the bottom. And I've disabled the cut function, so it'll have to copy and paste somehow. And if a row has ANY info in B-J or L-N, it can't be cleared, must be completely empty. This is Excel 2007. Thanks!

Upon pushing the button, all rows that satisfy this code will be cleared. I have no clue how to transform this worksheet code into VB!



I am halfway through setting up a Spreadsheet and I am completely stuck!

eg. I have a drop down list in B2 (Excellent, Average & Poor). I want a formula in C2 that recognises when Excellent is chosen it then takes the information from C6, when Average is chosen it then takes the information from C7, when Poor is chosen it then takes the information from C8.

I know I can do a formula where I can put a value in for each but i need it to be IF(B2="Excellent","C6",IF(B2="Average","C7",IF(B2="Poor","C8")))

I need it to be the value what is in C6 rather than the letter and number C6.

Thank you

Chris

I have 2 columns. Values in column A also exist in column B, but not all. I have conditional formula in column C that displays 'TRUE' or 'FLASE' if identical value from Column A exist in column B.

But the problem is some values in columns B contain extra space at the end of value therefore condition formula shows 'FLASE' when infact value from columns A exist somewhere in Column B but extra space make Excel think it is different.
i.e.

A1 value = U S A
B82 value = U S A

'U S A' does exist in column B but I will get false value in C1 because of extra space at the end.

So What I trying to accomplish is delete any extra space ONLY at the end

I tried CNTRL + H but It will replace ANY SPACE whereever it finds but I want to delete only at the end.

Any help will be appreciated.

Hi,

I have the following table:

col/ A B C
Row
1 Canada 1 ==> CF would be
BLUE
2 Canada 5 ==> CF would be
BLUE
3 Australia 1 ==> CF would
be GREEN
4 NewZealand 4 ==> CF would be
BLUE
5 NewZealand 6 ==> CF would be
BLUE
6 FrenchPolynesia 3 ==> CF would be
GREEN

I would like to use a conditional format in the cells in column B to
highlight every change in country, say alternate BLUE and GREEN
I am able to use the helper column A with this array formula:

{=ISEVEN(SUM(IF(FREQUENCY(IF(LEN(B$1:B1)>0,MATCH(B$1:B1,B$1:B1,0),""),IF(LEN(B$1:B1)>0,MATCH(B$1:B1, B$1:B1,0),""))>0,1)))}

which gives me a result of TRUE and FALSE every time there is a change.
Conditional formats is easy to use then. However, I would like to know if
it's possible to use a formula within the CF in cells of Column B. I tried
the array formula within the cell B1, but Excel wanted nothing of it!

Does anyone have a simple (or even complicated) solution ?

Thank you in advance

André

I'm sure something like this has been answered here before, but I haven't
found it yet after an admittedly short search...

My worksheet has a list of tasks across the top (in row 6) and then days
down the left side (in column a). I had a simple =counta(b8:ai8) in the last
column to give me the total tasks completed. (I was just typing an X or
whatever into each cell to indicate it was done.)

Well, now there are three types of days. On "M" days, only the 9 "M" tasks
need to be done. On "B" days, "M" and "B" tasks need to be done. And on all
other days, all 34 tasks need to be done. So I added a row (7) to indicate
what category each task is, "m" "b" or blank. And then than I inserted a
column (B) so I could indicate what kind of day it is.

In other words:

A B C D E F
6 Type task1 task2 task3 task4
7 m b m
8 Wed03/22 b X X
9 Thu03/23 m X X
10 Fri03/24 X X X
11 Sat03/25 m X

I thought I could just use an if statement:
=IF(B8="m",COUNTIF(C8:AJ8, ???? ),IF(B8="b",COUNTIF(C8:AJ8, ????
),COUNTA(c8:AJ8)))
but I can't figure out what to include as the conditional statement in the
Countifs. I want to compare the code for each task (in row 7: b m or blank)
to the code for the current day (in column B) and only count "M" tasks on "M"
days, count "M" and "B" tasks on "B" days and count all tasks on "blank" days.

Can I use sumproduct for that? (It's been a while since I read about
Sumproduct, but I never really understood it to being with...) Or do I need
an array formula? Or something else?

Hope I've explained it well enough. Thanks for any help you can give!
Karin

By the way, when I got a new computer, I didn't copy over all my bookmarks
to various helpful Excel sites, so need to compile a new set of "favorites".
If you have any suggestions, please let me know. (I'm a huge MVPs fans!)
Reply to this or send to karin (at) charterinternet (dot) com

Hello,

First, let me say that I have a solution to this problem but I am looking
for a better one. Second, I apologize if this gets a little long.

I keep scores for homework assignments in a spreadsheet, recording not the
percentage but the numerator (points achieved) and the denominator (points
possible). A student's homework score is computed by summing all the
numerators and dividing by the sum of all the denominators. This way a
perfect score on a 50-point problem set improves your homework score more
than a perfect score on a 10-point problem set would.

But if only it were that easy! We have a policy of "dropping" the n lowest
problem sets, where n is usually 2 or 3. This is to give the students some
slack. My colleagues and I have tried various interpretations of
"lowest"--lowest by percentage, lowest by z-score relative to the rest of
the class; and other hacks. I've finally decided that the "lowest" score is
the one that improves your total score the most if you neglect it. That is,
for each problem set, sum the numerators skipping this one, sum the
denominators skipping this one, and compare that to the original homework
score. I'm trying to find the best way to do this in Excel.

Let's suppose the numerators are in A1:Z1 and the denominators are in A2:Z2.
Then the change in homework score by dropping the problem set in column G
(say) is (SUM(A1:Z1) - G1)/(SUM(A2:Z2) - G2). I put these changes in
AA1:AZ1. Then in BA1 I have IF(RANK(AA1,$AA1:$AZ1)<=3,0,1), and so on down
to BZ1. I'm sure I don't have to put these in their own cells, but it helps
with debugging and conditionally formatting the original scores.

The adjusted numerator is then SUMPRODUCT(A1:Z1,BA1:BZ1), and the adjusted
denominator is SUMPRODUCT(A2:Z2,BA1:BZ1). Then I have to hide 52 columns of
intermediate values. :-)

It seems like there ought to be a one-cell formula to compute the adjusted
numerator. I'm not that good with array functions, though. I tried
something like

SUMPRODUCT(A1:Z1,IF(RANK((SUM(A1:Z1)-A1:Z1)/(SUM(A2:Z2)-A2:Z2),
(SUM(A1:Z1)-A1:Z1)/(SUM(AZ:Z2)-A2:Z2))<=3,0,1)

But this gives a non-descriptive error (not to mention the fact that the
first and second arguments are identical!).

Additional complications:

* I need to break ties so exactly three are dropped. I do this by
adding to the change cell (SUM(A1:Z1) - G1)/(SUM(A2:Z2) - G2) a small number
times the column number. That's klunky but it works.

* I sometimes need to override policy and force a problem set to be
dropped. Currently my IF(RANK(AA1,$AA1:$AZ1)<=3,0,1) formula also
short-circuits to 0 if AA1 has the string "DROP" in it. Can I instead look
for a comment attached to the cell?

Thanks for any answers, advice, or clues.

--Matthew Leingang

--
Matthew Leingang
Remove caps for correct email address

I have the following sheet (in Excel 2000)
year type province budget
2002 1 10 100
2002 1 10 150
2002 2 11 200
2003 2 11 250
2003 3 12 300
2003 3 12 350

to get the total budget per year+type+province I used
=SUM(IF((A2:A7="2002")*(B2:B7="1")*(C2:C7="10");D2:D7)). This formula is,
with one extra argument, identical to the explanation in Help. However it
returns 0 and not 250.
Using the exact formula as stated in Help also returns 0. Note that I use ;
instead of , due to the different thousand and decimal separator in
continental Europe.
A solution to this (simple?) problem is highly appreciated.


No luck finding an answer? You could always try Google.