## 7 Q & A posts tagged with formulas

### How can I convert from pipe delimited txt file to excel file?

Then convert that excel file to rss feed?

Or am I making this more difficult than it needs to be?

### Answer

Excel already have Text two Columns wizard, however, you can use this one

http://www.anmars.com/Work/?P=589970000

It is in pure formulas and can be used to extract unlimited columns with certain delimiter

Once you copy and paste the content into column A fill the formulas down to extract them

### How to import into MS Word ,data from two worksheets from Workbook of MS Excel file ?

I have MS-excel Data with a customer sheet with address details on one sheet & Items bought by a customer on other sheet,by using mail merge I am able to generate a letter for each customer by using customer sheet,I want to include details of items bought(from that items sheet).

### Answer

I suggest you having a third worksheet that list the customers and their buying details

Then mail merge it

You might need to use formulas to do so, like VLOOKUP, MATCH, INDEX, IF, COUNTA, etc

use the customerID to grap these info

VBAXLMan

### How do I make a comparison chart using formulas in Excel?

I have an assignment to do where I need to research three different computers and make a comparison chart to find out which is the best computer for the money. The only problem I'm having is that I was never taught how to do this using formulas and/or functions. What formulas would I use to compare different things and find which is the best?

### Answer

Your question is so general

But let me try

You need first to build the table

Based on the data you have, you need to study formulas like

=A1/A2

or

=

besides IF, SUM, AVERAGE

most of these formulas are in Insert > Functions (if you have Excel2003 or earlier)

or in Formulas tab > Insert Functions (If Excel2007)

Let me know if that worked or not

VBAXLMan

### How can I link two separate workbooks in MS Excel 2000?

I am looking to link some formulas so that whatever I enter in January workbook will then go to February automatically then march and so on. I have tried copying and pasting special but that just copies the formulas what I really want is for them to talk to each other. Being not as computer savvy as most the answer in the most basic terms would be highly appreciated.

### Answer

When you do Paste Special

Select "Paste Link"

This will do it

OR

You can go to February sheet, select a cell and type

=

then select the cell you want to link to in January sheet

VBAXLMan

### Excel > Use formulas on the last x rows of data?

I'm keeping track of the total number of wins and losses for a sports team. There's one game a week. I will have 16 rows in my Excel sheet to mark a win or loss. If the team wins that week, I'll put in "W" for that week's row, and "L" for a loss. As you can imagine, a new row will be populated with either a "W" or "L" as the weeks progress.

I'm trying to get a win/loss count of the last 5 games played. So if the team has played 7 games, I want to show the total number of wins and losses of the last 5 games (games 3-7). If 10 games have been played, then show the total number of wins and losses for games 6-10. This is similar to the L10 statistic for baseball, hockey, and other sports, but in this case, I'm trying to do a L5 for the last five games.

How can I achieve this in Excel? I'm going to use COUNTIF for counting the total number of "W" and "L", but I need it to count only the last five rows of data.

Any help is appreciated. THANKS!

### Answer

Do this so that whenever you add new row/week it will automatically updated

Now, if your list of Ws/Ls starts from A2 down, paste this in D1

=COUNTIF( OFFSET(A2, COUNTA(A:A)-6,0,5,1),"W")

To get the number of Ws in the last 5 non empty cells in Column A

And do this in E1

=COUNTIF( OFFSET(A2, COUNTA(A:A)-6,0,5,1),"L")

To get the number of Ls in the last 5 non empty cells in column A

Also assuming you have the cell A1 with text as header

Good luck

VBAXLMan

### I want to know the Ms-excel formulas. to use excel.?

I want to know different formulas in excel like vlookup,hlookup,pivot table,conditioning formats & links ect.,

### Answer

There are a lot of websites that can help you

try these

http://MrExcel.com

http://www.File1.net

VBAXLMan

### How to solve this problem with Excel conditional formatting?

I have a table on an Excel spreadsheet, which table contains only numbers. I want to color a "path" in this table using the following algorithm:

1) Make current the upper left cell and color it.

2) Examine the cell to the right of the current one and the cell beneath the current one. Make current the one that contains the smaller of the two numbers and color it. If the two numbers are equal, make current and color the cell beneath the current one.

3) Repeat 2) until the bottom or the right edge of the table is reached, whichever occurs first.

Can this be done using only the means of Excel's conditional formatting (i.e., no VBA) and how? That is, what formulas for conditional formatting to use?

### Answer

Conditional formatting will "FORMAT" the cell and not change the cell content

To change cell content (or value) you will need a function

Based on your request, IF function would do the trick for you

Now, if your first cell is A1, then paste this into it

=IF( OR(A2="",B1=""),"", A2<B1, A1, IF( A2>B1, B1, A2))

Then fill cell A1 down

Now, Since the Conditional Formatting is to "FORMAT" cells, I don't see where you need it here

let me know

VBAXLMan