# Adjust Simple Formula To Find Last Row

May 8, 2009I am using this formula in Cell J12: =AVERAGE($G$12:$G$85)

It will always be $G$12 but $G$85 needs to be done so that it goes until the last empty row on the worksheet.

I am using this formula in Cell J12: =AVERAGE($G$12:$G$85)

It will always be $G$12 but $G$85 needs to be done so that it goes until the last empty row on the worksheet.

ADVERTISEMENT

I'm looking for the functionality in Excel where you can unhide/hide a group of columns or rows by clicking the plus or minus sign below the ribbon but above the spreadsheet itself. I forget how to do it, but it basically groups the columns together and hides or unhides all of them with one click.

View 3 Replies View RelatedIn need of a formula to trim data:

Examples:

* 5 Extravagant Mak (nz) (100) Need the number plus a dot then space then the name, then minus everything after the name, so this would now read 5. Extravagant Mak thats 5dot space Extravagant Mak

*

11 Frisbee (100) This one would become 11. Frisbee thats 11dot space Frisbee

I've posted a document in which I'm trying to calculate the weekly headcount for employees using their timecard entries.There are many entries per week for each employee, but I want to count them only once per week rather than each time they make a timecard entry. I need to tweak it to deal with one condition that it currently doesn't handle.The person who provided has said he doesn't check in with the forum very often, so I'm putting this out there to a wider audience.

Whenever the value of column W = "UTO" (unpaid time off) I want the formula to ignore that entry (count it as 0 rather than 1) and continue on to the next entry. The way I have the formula now, it counts any "UTO" entries as 0, but then it does not count the subsequent non-UTO entry as 1. I'm not expert enough to fully understand the formula that was provided

--(COUNTIFS(B$2:B945,B945,U$2:U945,U945)=1).

I have added embedded the formula in an IF statement to ignore the "UTO" entries.

=IF([@[Util?]]="UTO",0,--(COUNTIFS(B$2:B945,B945,U$2:U945,U945)=1)) ("Util?" is the header for column W)

I have a formula that displays the 1st monday of a month, I need the formula to take into account which day is selected and then display the first of the selected days date for the month.

=DATEVALUE("1"&G7&G5)+IF(WEEKDAY(DATEVALUE("1"&G7&G5),2)=1,0,8-WEEKDAY(DATEVALUE("1"&G7&G5),2))

Is there any way to adjust it to do this the drop down box for the day will be in F9.

I was using the formula below which was working fine for copying across 20 columns and down however many rows, but now the requirements have changed to 90 columns. I have tested this out with the 90 columns but the Indirect function is bogging down the spreadsheet with the constant recalculating.

=IFERROR(INDIRECT("'"&COLUMNS($A$1:A$1)&"'!P"&ROWS($A$1:$A8)),"")

I rearranged the formula to the one below, but how can I have the reference to the sheet name changed as in the formula above without using the Indirect?

=IFERROR(INDEX('1'!$A$8:$AT$115,MATCH($A3,'1'!$A$8:$A$115,0),MATCH("PAT",'1'!$A$7:$AT$7,0)),"")

The sheet reference will change from '1' through '90'.

I have a system that generates 4 databases with different types of statistical data. Observed defects, Calculated Defect, Capability and Number of points. I have set up a system for telling me what ranges each section is and I am manually changing the ranges to get my expected results.

Is there a formula that I can use that could adjust my ranges, within my formulas?

I have a spreadsheet at work . I am tracking the quality target for the department and I need to create a formula that adjust accordingly to the target set for month end which is 90%.

I have build in the foreacasted numbers for the whole month but I need the formula to indicate the minimum error points needed to achieve the target of 90% for month end when I replace the forecasted numbers on a daily basis with actual numbers.

I have attached the spreadsheet which will be clearer.

Using Excel 2003. I have a very basic SUM formula I am using to show how many forms are still in-house. Pretty simple.

Column D is how many came in.

Column F is how many were completed.

Column E is what is left.

To get the daily count I take the previous day's Column E, add to today's Column D and subtract today's Column F.

=(E2+D3-F3)

Puts the results in E3.

This formula is copied on down the worksheet which results in:

=(E3+D4-F4), with the results in E4. And so on down Column E.

The problem is that if there is no data entered in Column D or F, the results from E are carried (copied?) as far down as the forumla. i.e., if today's column E results in 4, then that is displayed for as far down as I have copied the formula. What I'd like to do is set the formula so that if there is no data in columns E or F, then there is nothing displayed in Column E.

Trying to work out array formula.

basically if B2 = list of figures in range(F1:f22) then "G14" otherwise "".

{=if(b2=(F1:F22),"G14","")}

doesn't work.

I want to concatenate two Cells into a single cell BUT have the first field left justified and the second cell right adjusted.

A1 = "John Williams", A2= "Single"

A3 = "John Williams Single"

I'm using a template that has formulas using a $ sign to attempt to stop the ranges they refer to changing. The problem is, when I use the template (which involes Access importing some data and adding columns to the sheet the formula refers to in the process) the rows referred to in the formula change in line with the number of rows of data that have been imported. Only the row numbers change not the column headings. So for example:

=( SUMPRODUCT(((Workings!$H$3:$H$1000=Explanation!B9)+(Workings!$E$3:$E$1000="Buy")*(Workings!$J$3:$J$1000))))-998

becomes

=(SUMPRODUCT(((Workings!$H$511:$H$1508=Explanation!B10)+(Workings!$E$511:$E$1508="Buy")*(Workings!$J$511:$J$1508))))-998

So I have a list of names, and I need to keep track of whose paperwork I have. There's a column that says Yes/No.

I would like to link everything to another sheet in the file. So for every name that says "No," I would like that name to appear in the next sheet. And when someone updates the first page to put in a "yes," I want it automatically taken off the second sheet.

So.

I have Sheet 1:....

I have a reasonable grasp of Excel and this has me completely stumped. Three cells hold numeric values (format General though the format seems not to change the result)

C4 = 4082322.25

D4 = 4046689.05

E4 = 35633.2

Another cell contains the simplest of formulae ...

F4 = "=C4-D4-E4"

Clearly the result should be zero.

In fact the result is (to 15 decimals) ...

0.000000000189175

HOWEVER

If I change F4 formula to "=C4-E4-D4" the result is zero.

I'm working on simple array formula between two different sheets.

I'm working on Sheet 1 and sheet 2

Right now I want the result on sheet 2 C9

Sheet 2 C6 has MTR

Sheet 1

c6 MTR

c7 MAR

c8 MTR

E6 2

E7 4

E8 6

Now on Sheet 2 C7 has 10

Now the result Sheet C9 has to get 8

=ARRAYFORMULA(SUM(IF(C6="+Sheet1!$C$6:$C$8",Sheet1!$E$6:$E$8,Sheet2!C7)))

I want this formula to work on both excel and Google Drive but now this formula doesn't work on any one.

I would like to insert a formula in a column, the formula is in column D and must be something like "=C1-B1" for row 1, "=C2-B2" for row 2,...etc. I would like to do it without using a loop cycle in VBA...I have attached an example of the data I'm using..

View 3 Replies View RelatedCode:

fma_rng = .Range("C" & fma_top & ":C" & fma_btm)

rows_blnk = Application.CountBlank(fma_rng)

why I would be getting a 'type mismatch' error with the line in red?

I've got a list of marks (col T), and I want to create a new column subtracting 5 from each value.

Obviously, the formula in this new column is

=T1-5

=T2-5

etc...

But I enter that formula and excel doesn't do any calculating, it just displays "T1-5"

It even seems to recognize that it's a formula, highlighting T1 in the formula and the T1 cell itself, but no resulting value...

I've tried changing the format of the cells (in both columns) to Number and back to General again, but without success.

I've also tried copying and pasting (values only) the whole T column to another column, and tried there, also without any success.

Using this formula for a bank account,

=IF(SUM(C2-A1+B1)=C1, "match", "NO match")

down a column of about 3000 rows, yields only one cell with an unexpected and probably incorrect response in G2 with a "NO Match" - where the math is correct ?

A B C D

63.58 0.00 -54.97 match

64.57 0.00 8.61 NO match

128.21 0.0073.18 match

201.39

Details:

Attachment is included;

temp2.xlsx

in a bank account file, where "C2" is the previous balance, "C1" is the current balance, "A1" is any check written, "B1" is any deposit applied.

I have used Excel for a number of years and have gotten on ok on my own working out simple formulas myself but I have been stuck on this one for a while. Is it possible to enter a value into a cell and have a predefined formula that works with the data that I enter?? I would like to enter a value between 1-10 and the cell then takes this number and multiplies it by a value in another cell. Eg.

I have a cell that displays £10 (let's say D6)

In another cell I enter 8

I would then like the cell to do the formula =8*D6 automatically, as this value can be different each time (1-10)

I'm getting a mismatch error on my If activecell.value > 5 Then portion of this code. I'm trying to say if the value in column AG is greater than 5 then create an email address from the first and last name. I've even made sure to copy and paste the formula results as values.

Code:

Sub CheckPFPDates()

Dim OutApp As Object

Dim OutMail As Object

Dim cell As Range

Dim emails As String

Dim salution As String

Dim currenttime As Integer

[Code] ........

I have a data example

Cell A1 which contain text I.E = "Words"

I have link B1 To cell A1 I.E = A1

Now what I want is I want the last two words or any alphabet between alphabet

to be bold

W O R D

Example

WORD or WORD

Any alphabet between word to be bold upon my requirement.

Instead of linking that cell to another cell , i want any value when entered into that cell.

That alphabet automatically becomes bold as this will not be requiring linking to that cell

product1

product1

product2

product1

product2

product4

product1

product2

product1

product4

Need the simple formula to list only unique records of above data

unique list is

product1

product2

product3

I have a row with numbers

1

2

3

5

6

7

8

What is the formula to get then on another column as

1

2

3

5

6

7

8

I have two spreadsheets of data, one column contains an employees name, the other is a value.

Employee Amount

Bob 10

Frank 12

Katie 14

Katie 55

Simon 5.2

I would like to do a lookup that states if name is Katie and value is 55 and is in sheet 1, return "Yes", else return "No"

The name and value conditions will read off the other spreadsheet.

I am trying to implement a simple macro. Here is my

Function Age(DOB)

Age = Int((Now - DOB) / 365)

End Function

Sub Macro1()

Age (Selection)

ActiveCell.Offset(0, 1).Select

******

End Sub

The function basically calculates the persons age using their date of birth. What I need to know is how to copy the result of the function into the cell next to the selected one. (The code above only selects the adjacent cell).

Report I generate once a month that checks to see if tasks are done on or before their due date. Sometimes it works, and others it doesn't...

The basic formula is: [Code] .....

If they closed their task by or before the due date, the condition should be true and it should output "Compliant" beside their task. Otherwise their "Over Due".

The forumla works except when they close their task ON the due date. No matter how I tweak the forumla, it thinks the condition is false as if it cannot tell when the two dates match.

I am trying to use FIND and an array formula to find the position of text in a range of cells (A2 and A3 in the example) which could be one of a number of options (C1:D1 here). But the array formula throws up the following error: "A value used in the formula is of the wrong data type". The simplest illustration of the problem is as follows. The formula in B2 is

Code:

{=FIND(($C$1:$D$1),A2)}

and $C$1:$D$1 contain REF and ATM respectively. [/CODE]

REF

ATM

203047 05AUG 08.55 OKEHAMPTON ATM

#VALUE!

CO-OP GROUP 380611 REF 191 7553375222 BCC

22

We see that B2 has a #VALUE! error - wrong data type. But for some reason B3 is ok returning 22!

This is my formula with concatenation: =K3/K1*100 & "c" & " Each"

This is the result of the values in K3 divided by K1: 6.41666666666667c Each

I would like to limit the decimal places to two: 6.41c Each.

I have tried to format the cell and none of the formatting options have any effect.

If possible I would like to use the rounding function but I do not know how to combine the formula + text + rounding.

formula for -- If Text = 1 and Text =-1 adjust running total

example:

1312 (total)

SS1311 (new total)

LA1310 (new total)

LB1309 (new total)

LS1310 (new total)

LS =+1

SS, LA, LB =-1

Copyrights 2005-15 www.BigResource.com, All rights reserved