Free Microsoft Excel 2013 Quick Reference

- FAQ & How To....For Microsoft Excel
- Can I hide rows in Excel 2002 using a formula in a cell?
- Conditional Format (month and year) = Today()
- Automatic data extraction, calculating and conditional copying results to 2n workbook
- Conditional formatting and colored cells
- How to Return text in cell if certain criteria is met?
- Conditional Formatting Formula Will not work
- Conditional Problem Solving
- Conditionally summing cells based on conditions in other rows
- Excel 2007 Text Function
- Fill Cell Contents On Multiple Conditions (Formula or VBA)
- Highlight rows / columns using VBA & Conditional Formatting problem
- Merging Table Cells with Text under Certain Conditions
- Clear rows based on conditional formula
- Conditional Output
- Looking a Way to Kill Extra Space at the end of value
- Conditional formating using array formula??
- Do I need array formula or sumproduct for counting?
- "Drop the lowest" in computing average
- Sumif with 3 conditions

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

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

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 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.

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 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?

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?

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!

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

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

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

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!

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

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.

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é

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

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

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.