Question about Reddy Heater-40,000 BTU Kerosene w/T'stat

I have data listed in several colums and need to create a formula to detail the last (most recent) 2nd last and 3rd last piece of data in the column.

I have used the following formula to display the last but cant edit this or create another formula to get the 2nd and 3rd last pieces of data.

=LOOKUP(2,1/(A:A<>0),A:A)

Thanks in advance

Jamie

There isn't enough information to make a formuls for you. I normally just go to where I want to put my formula, hit the Auto-Sum button and modify it to suite what I want. Make sure you don't have formatting set for auto or turned off for the cells you want to work with.

Posted on Jan 16, 2009

Hi,

a 6ya expert can help you resolve that issue over the phone in a minute or two.

best thing about this new service is that you are never placed on hold and get to talk to real repairmen in the US.

the service is completely free and covers almost anything you can think of (from cars to computers, handyman, and even drones).

click here to download the app (for users in the US for now) and get all the help you need.

goodluck!

Posted on Jan 02, 2017

The easy way (but labor intensive) is to put the file names in a column (can be any name you want or the pdf file name), right click on a cell, hit insert hyperlink, and in the address field at bottom, put in the file pdf file name. You would have to do this for each of your 100 files.

If you want to automate it, create a column with the directory path name, a 2nd column with the full file names, a 3rd column where you concatenate the two previous columns to create the full path, and then use the hyperlink command to provide the link:

Folder name Filename Concatenated LINK ../excel/ file1.pdf ../excel/file1.pdf file1.pdf ../excel/ file2.pdf ../excel/file2.pdf file2.pdf ../excel/ file3.pdf ../excel/file3.pdf file3.pdf

Here are the formulas behind this:

A B C D Folder name Filename Concatenated LINK ../excel/ file1.pdf =CONCATENATE(A2,B2) =HYPERLINK((C2),B2)

../excel/ file2.pdf =CONCATENATE(A3,B3) =HYPERLINK((C3),B3)

../excel/ file3.pdf =CONCATENATE(A4,B4) =HYPERLINK((C4),B4)

This should work like a charm and the other advantage is that excel won't rename your path locally depending on weather a file is open or not).

If you want to automate it, create a column with the directory path name, a 2nd column with the full file names, a 3rd column where you concatenate the two previous columns to create the full path, and then use the hyperlink command to provide the link:

Folder name Filename Concatenated LINK ../excel/ file1.pdf ../excel/file1.pdf file1.pdf ../excel/ file2.pdf ../excel/file2.pdf file2.pdf ../excel/ file3.pdf ../excel/file3.pdf file3.pdf

Here are the formulas behind this:

A B C D Folder name Filename Concatenated LINK ../excel/ file1.pdf =CONCATENATE(A2,B2) =HYPERLINK((C2),B2)

../excel/ file2.pdf =CONCATENATE(A3,B3) =HYPERLINK((C3),B3)

../excel/ file3.pdf =CONCATENATE(A4,B4) =HYPERLINK((C4),B4)

This should work like a charm and the other advantage is that excel won't rename your path locally depending on weather a file is open or not).

Feb 21, 2014 | Microsoft Excel for PC

In a column adjacent to your data, enter the formula

=RIGHT(A1,4)

and copy down as far as you need to go. Then, sort on that column.

=RIGHT(A1,4)

and copy down as far as you need to go. Then, sort on that column.

Feb 25, 2012 | Microsoft Office Standard for PC

The easiest way to do this is to use Excel spreadsheet.

This is what you need to do.

Create a label at the top of each column as detailed.

Add a employee to each row under Name of employee

Then against each employee add the data and the formula in the relevant cell under each column.

The first column - Name of the employee

2nd column - salary rate per hour

3rd column - hours worked

4th column - gross pay (formula = salary rate X hours worked)

5th column - tax deduction

6th column - other deductions

7th column - total deductions (formula = tax deduction + other deduction)

8th column - net pay (formula = gross pay - tax - total deductions)

At the last row you can include a total for Gross salary paid, total Tax collected etc.

Once you have setup this spread sheet make a copy of it and save it as a template.

You can then copy this template for each new financial year so you do not have to create a new one each year, you only need to make minor changes for new employees, rates of pay etc.

Another way to make a salary program is to use Access Database. You need more skills to do this, but it can provide greater reporting capabilities.

This is what you need to do.

Create a label at the top of each column as detailed.

Add a employee to each row under Name of employee

Then against each employee add the data and the formula in the relevant cell under each column.

The first column - Name of the employee

2nd column - salary rate per hour

3rd column - hours worked

4th column - gross pay (formula = salary rate X hours worked)

5th column - tax deduction

6th column - other deductions

7th column - total deductions (formula = tax deduction + other deduction)

8th column - net pay (formula = gross pay - tax - total deductions)

At the last row you can include a total for Gross salary paid, total Tax collected etc.

Once you have setup this spread sheet make a copy of it and save it as a template.

You can then copy this template for each new financial year so you do not have to create a new one each year, you only need to make minor changes for new employees, rates of pay etc.

Another way to make a salary program is to use Access Database. You need more skills to do this, but it can provide greater reporting capabilities.

Mar 24, 2011 | Computers & Internet

Hi, Not sure if this is what you have tried:

1: Assuming each run is on a different column

Run1 Run2 Run3 Run4, etc...and the maxium lenght of each column is 9 values, but each colum has a different lenght of numbers..

Then you can use:

=MAX(COUNTIF(A2:A10,"<>0"),COUNTIF(B2:B10,"<>0"), {keep adding countif for each colum).

If all runs are on the same colum, then using Pivot tables is easier. If you have a copy of your data, please post it so that I can give you the exact formula ;)

1: Assuming each run is on a different column

Run1 Run2 Run3 Run4, etc...and the maxium lenght of each column is 9 values, but each colum has a different lenght of numbers..

Then you can use:

=MAX(COUNTIF(A2:A10,"<>0"),COUNTIF(B2:B10,"<>0"), {keep adding countif for each colum).

If all runs are on the same colum, then using Pivot tables is easier. If you have a copy of your data, please post it so that I can give you the exact formula ;)

Oct 21, 2009 | Microsoft Excel for PC

=SUM(Cell1,Cell2,Cell3)/sold_price

Where Cell1, Cell2 and Cell3 are the cell references for the 1st, 2nd and 3rd loan fields and the sold price is the field reference for the cell that has the value for the sold price in it.

Where Cell1, Cell2 and Cell3 are the cell references for the 1st, 2nd and 3rd loan fields and the sold price is the field reference for the cell that has the value for the sold price in it.

Apr 18, 2009 | Microsoft Excel for PC

Add another colum, say D with result of B*C

Do a list of the different cat, and use sumif

For cat 0101 (in cell F1)

formula to put in G1

=sumif($A$1:$A$7000;F1;$D$1:$D$7000) where F1 contain 0101

expand the formula for other cat. (G2, G3, etc)

You can use a assistant to extract the distinct cat from A1:A7000, sorting them and copy the result in F (Menu Data-> Filter -> elaborate filter)

Do a list of the different cat, and use sumif

For cat 0101 (in cell F1)

formula to put in G1

=sumif($A$1:$A$7000;F1;$D$1:$D$7000) where F1 contain 0101

expand the formula for other cat. (G2, G3, etc)

You can use a assistant to extract the distinct cat from A1:A7000, sorting them and copy the result in F (Menu Data-> Filter -> elaborate filter)

Feb 16, 2009 | Microsoft Excel for PC

I'm assuming you'd like to assign a numerical value to cardinal references (1st, 2nd, 3rd, 4th, etc.).

Best way to do this is to create a quick lookup table on a separate sheet of the same .xls document. Down at the bottom of the page, click on Sheet2 and create a quick table where column A has 1st, 2nd, 3rd, etc. and column B has your values.

Now click back to Sheet1 where your data is and in A2 put this formula:

=VLookup(A1, Sheet2!$A$1:$B$x, 2, FALSE)

Note: replace the lower case x in the formula above with the number of the last row of data in your lookup table on Sheet2.

You can copy and paste this formula down the column to calculate your other values.

Hope that helps!

Terry

Best way to do this is to create a quick lookup table on a separate sheet of the same .xls document. Down at the bottom of the page, click on Sheet2 and create a quick table where column A has 1st, 2nd, 3rd, etc. and column B has your values.

Now click back to Sheet1 where your data is and in A2 put this formula:

=VLookup(A1, Sheet2!$A$1:$B$x, 2, FALSE)

Note: replace the lower case x in the formula above with the number of the last row of data in your lookup table on Sheet2.

You can copy and paste this formula down the column to calculate your other values.

Hope that helps!

Terry

Aug 30, 2008 | Microsoft Excel for PC

The solution I've used in similar situations is to create a 3rd column C with the items in column A and column B concatenated.

C2 = A2 & B2

C3 = A3 & B3

C4 = A4 & B4

etc.

Then use COUNTIF function: =COUNTIF(C:C,"FredRed Ball")

Hope this helps.

C2 = A2 & B2

C3 = A3 & B3

C4 = A4 & B4

etc.

Then use COUNTIF function: =COUNTIF(C:C,"FredRed Ball")

Hope this helps.

May 27, 2008 | Microsoft Excel for PC

Hi, probably the easiest way would be to create a macro and attach it to a button which would automatically sort the required column in descending order. When Excel does this it reorders the whole list so that the names correspond correctly. To do this you simply go to the tools menu and select macros and "record new macro" then click any cell in the results that make that person coming first etc, then click the sort descending button on the toolbar, then click stop record.

Afterwards create a button from the "toolbox controls" and then view the code for both the button and the macro and copy the macro code and paste it into the "click" option for the button.

If you need an example or anything else give me a yell.

Regards

SeaJade

Afterwards create a button from the "toolbox controls" and then view the code for both the button and the macro and copy the macro code and paste it into the "click" option for the button.

If you need an example or anything else give me a yell.

Regards

SeaJade

Mar 05, 2008 | Microsoft Excel for PC

at first select the 1st page data and select data-subtotal from the upper menu list.

then enter the formula as =sum(1stpageSubTotal,2ndpageSubTotal,3rdpageSubTotal) then Press Enter.

If not solved pls get me that data as to reference.

thanks

then enter the formula as =sum(1stpageSubTotal,2ndpageSubTotal,3rdpageSubTotal) then Press Enter.

If not solved pls get me that data as to reference.

thanks

Dec 04, 2007 | Microsoft Excel for PC

115 people viewed this question

Usually answered in minutes!

×