Tracker
HOME    TRACKER    Excel

## I am having a few issues stringing along a formula. A1 5 I need A2 to say "If A1 is between 0 and 2 add 0, if greater than 2 but less than 4 add .30, if greater than 4 but less than 6 add .70 and so on. A2 looking at A1 if 0 through 2 add 0 if 2 through 4 add .30 if 4 through 6 add .70 if 6 through 8 add 1 if 8 through 10 add 1.3 if 10 through 12 add 1.6 Basically these are employee break times that I need to automatically figure into our labor hours. Every 2 hours they get "x" amount of break time. So if they have over 2 hours of work they will get 1 break, if they work over 4 hours then I factor in a lunch and the previous break ect..

Related Forum Messages:
This task joins a string together based on a number of characters per cell in the range.

I want to isolate one range, Col N, and add an IF condition to it.

There may be other issues preventing this from happening, e.g. the number of IF that exist in the complete formula. I will isolate the current cell and its requirements and then post the entire formula at the end for reference....

Date Formula & Add If Statement To The Existing Formula
I currently use the following formula: =workday(H21,H23,J20:J34) which calculates my date based the date entered in cell H21. While testing I realized that I needed to add another variable to the formula for a specific scenario. When my Date in H21 is less than todays date I require another workday formula to be calculated. How can I add this if statement to the existing formula?

=IF(ISBLANK(VLOOKUP(1,'D-Sort-R'!A96:\$N\$450,4,0)),"",VLOOKUP(1,'D-Sort-R'!A96:\$N\$450,4,0))

I've almost got what I need for a mind bending series of sorts that go from worksheet to worksheet. I need to add ISNA along with the ISBLANK above.

I have this macro which runs great, but I would like to add a date formula to it before it auto fits the columns. I need cell E5 to Increase its date by 1 month to the last day of month from G5. For examply if g5 reads 2/28/07 I want a formula that will make e5 3/31/07 for every sheet.

Maybe something like this =DATE(YEAR(g5),MONTH(g5)+2,0). but I don't know how to put it in the macro to run on every sheet (except the ones I excluded.)

Sub PayrollAnalysisMacro()
Dim wrkSheet As Variant

For Each wrkSheet In ThisWorkbook.Sheets
If wrkSheet.Name <> "Download" And wrkSheet.Name <> "Recap by DC MTD" And wrkSheet.Name <> "Recap by DC YTD" Then
With wrkSheet
.Range("G5:H71").Insert Shift:=xlToRight
.Range("E5:F71").Copy .Range("G5:H71")
.Range("G5:H71").Value = .Range("G5:H71").Value
.Columns("G:BE").EntireColumn.AutoFit
End With
End If

Do Not Want To Add An IF Formula If Value Is Blank
I have an IF formula that says if there is no data on an specific cell, then blank the cell. My problem is that when I try to count cells with data on the cells that have the formula, it is counting the blanks too. How can I make those blanks cells not to count?

Formula To Make Every 50 Add 1
If i wanted to tack on something to the end of a formula so that it would make every 50 add 1 (example: 200 would convert to 204, because 200 divided by 50 is 4).

If i have the following chart, what formula do i need to add all the entries EXCEPT and entry OS. i have the formula to add the entries but i need to exclude any "OS" entries. Like the following, but i want it to total 6. (minus the 2 OC entries)

=COUNTA(D6:D15)

TS
TS
VR
IS
IS
OS
VR
OS
8 (total)

How To Add Formula To Existing Macro
I have a data input worksheet, which uses the following code to fill in the missing zeros when cells are empty.

Insert A Row And Add A Formula
I have a spread sheet that I need to insert rows into and then in those same rows, add formulas that display percentages compared to the grand total row. To make it a little more complicated, this spread sheet changes from day to day, so the rows and columns will be different each time. So the code will have to recognize text, then insert the row, then do the formulas in the blank row. The only constant will be that it will always start on Row 5. I have no idea how to start or begin to do this, so I attached a file with a page showing where I am at as an example, and then anther page showing what I want ....

Add Clicked Cell Value To Formula
The idea is that once macro is started it will add the value of any clicked cell into the formula of the original cell.
ie.
1.start macro
2.click cell a5 (value=36)
3.type '+'
4.click cell b7 (value=21)
5.click enter to end macro
Result will be '=36+21' in the formula bar. The cell will then show the answer 57.

I need a formula that will make a 4 digit number by adding leading zeros. If the number is already 4 digits then don't add leading zeros, do nothing. all numbers will be 1,2,3,4 digits long.

For Example,

if number is 1 then make it 0001
if number is 11 then make it 0011
if number is 111 then make it 0111
if number is 1111 then leave it 1111

Formula To Add Within A Range Of Cells
I am trying to utilize a formula to add, within a range of cells, any cells that contain P/8 or P/5 or so on. The "P" would stand for PTO and the number behind the slash would designate the number of hours used for PTO. Is there a way to do this. In my chart I need to distinguish time between, PTO, Legacy, Holiday, etc.

Formula To Copy Down But Add Rows To It
Is there a way to create a formula to add for instance 4 rows to it. I am brining figures from a worksheet titled std inventory to another worksheet. The std inventory worksheet is set up by weeks with 4 lables. So the figures I want to bring in for weeks ending are in increments of 4 rows. For instance I am working on a sheet which I am inputting formulas which read:
= 'std inventory'!M50

My next formula down I want to input would be
= 'std inventory'!M54

Is there a way to copy and paste special this formula but have it add the 4 rows on easily?
So the next formula would be

= 'std inventory'!M58

Add Formula To Range In Macro
I'm working with 4 sheets:

Map1: contains a mapping table from old values to new values ( Name)
Map1: contains a mapping table from old values to new values (Location)
Input: contains a table with 3 columns, Amount/Old Name/Old Location
Output: contains a table with 3 columns, Amount/New Name/New Location

I need to create the sheet called Output, which will have the same Amount column as Input sheet but will do a vlookup to get New Name and Location values using the Map1 and Map2 sheet.

Add Formula To A Text Box
I am trying to insert a formula into a text box but I having problems with the "wording portion" of the formula.

I am trying to pull total sales from cell A1

Am I using this formula in the textbox

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.

Using Wildcards Within Formula (add Together Will Change)
=SUMPRODUCT(--('Trial Balance'!A1:A100="6120"),--(ISNUMBER(MATCH('Trial Balance'!B1:B100,{"00.00.01","00.01.01"},0))),'Trial Balance'!G1:G100)

Is there a way to use this formula, but instead of looking in Column B, and having to list 00.00.01, 00.01.01, that it only looks at the last two digits of the ##.##.##.

Here is the issue. Each month, the numbers that make up what I need to add together will change. However, all I am really doing, is combining any accounts that have the same sub account (Column B) ending in the same digits of either 00, 01, 02, or 08. So I could have 01.01.01, 01.00.01, 01.02.01, etc. and I am just focusing on the .01 on the end. I would like to say, look at all the sub accounts in B, and only add (G:G) on those that the last two digits equal .01.

Formula To Add A Number To A Cell
I need a formula to add the number 1 into cell J9 when cell d9 is no longer blank.
I'm sure it's really easy- but I can't figure it out.

here's the situation and what i'm trying to do:

each row/record is as follows:

column A contains zip code
column B contains zip code population
column C contains city name
columns D contains state name

there are multiple duplicates in each column.

i am trying to calculate the population for each city and place that data in each row in column E.

in order to do so, i need to add the populations (column B) of all UNIQUE zip codes (column A) for each city (column B) / state (column D).

Sumif Formula: Three Ranges Add Together
The section I have highlighted in orange has three ranges added together how would I show this in a simplified formula?

=sumif(DailySalesAnalysis!\$C\$1:\$GA\$1,A25,DailySalesAnalysis!\$C\$85:\$GA\$85)+sumif(DailySalesAnalysis!\$C\$1:\$GA\$1,A25,DailySalesAnalysis!\$C\$44:\$G A\$44)+sumif(DailySalesAnalysis!\$C\$1:\$GA\$1,A25,DailySalesAnalysis!\$C\$41:\$GA\$41)

I need to create a formula for a series of ranges that have a variable sheet name (which is located on sheet Backend!E15) and when it creates the formula will reference the exact same cell on the variable sheet.
this is what i have so far...

Option Explicit

Sub formulaset()
Dim Cell As Range
Dim target As String
For Each Cell In Range("b4:al132")
Application. ScreenUpdating = False
Cell.FormulaR1C1 = "=INDIRECT(CONCATENATE(BackEnd!E15,""!"",target))"
Application.ScreenUpdating = True
Next Cell
End Sub

but this is the answer I am getting in the first cell of the range...

=INDIRECT(CONCATENATE(BackEnd! 'E15',"!",target))

as you can see I am having trouble getting the target address to lock in. To make things worse, its needs to be in " " so the concatenate creates the corect address link.

Multiple Plus Formula: Add Scores Of Which There Are None
I have a grid set up to add scores of which there are none. The boxes each have a formula, ie: =IF(FIXTURES!D1="","",FIXTURES!D1 ) this produces a blank square until a score is registered. At the end of each column, down and across there is a formula for adding up the scores. Downwards is: = SUM(C2:C25), this works fine.

Across however is: =B3+D3+F3+H3+J3+L3+N3+P3+R3+T3+V3+X3+Z3+AB3+AD3+AF3+AH3+AJ3+AL3+AN3+AP3+AR3+AT3+AV3 This doesn't work, just gives the answer TRUE. It did work before the original formula was added.

Add Formula Reference To Another Worksheet
I have a worksheet that changes monthly but has the same fields always. I have created a few macros, and one that is a 'RunAll' for ease of use. Sometimes there is an error and I have to go back and fix some data, So I am looking for a way to change the "SelectBzzAgent" Macro to link data to a new sheet instead of copying. I have attached my spreadsheet.

How do I apply a formula to every cell in a given column?

I'd like my workbook to store this formula:
=c<current_row> + d<current_row>
in every cell of column P as a default.

This means that even if I do "insert row", I wouldn't have to copy this formula into the new cell.

I can't remember any formula to do the task. I have a time series with quarterly sales. The dates are in format given below.

01/01/1990 - first quarter of 1990
02/01/1990 - second quarter of 1990
03/01/1990 - Third quarter of 1990
04/01/1990 - fourth quarter of 1990

I need to add the sales of all first quarters , all second quarters..and so on.
Is there any formula that an elegant way or I have to do it manually?

I have a workbook setup to give me the nutritional break down of all of the foods I consume. I can get the subtotal of each day. What I need is to also get the subtotal of each meal. I can't seem to get this meal subtotal without adding it to the daily total twice (once in the listing and once in the subtotal). I was thinking of trying to add an exception to the sumif formula in columns I, K & M but I don't know if that is possible. I have included a cut down version of my workbook to illistrate the problem. I will gladly supply a full vertion to anyone who would like a copy for thier own use.

Code To Add Formula To Cell
I tried putting a formula into the below code but without any success.

mylogoff. Offset(0, 3).FormulaR1C1 = "=IF(WEEKDAY(RC[-1]=""6"",""17:30:00""+0,""18:00:00""+0)

It says application defined or object defined error.

I want to sum values in A1 to A6, so i use =Sum(A1:A6), i add rows aftere A6 what i shall put in the sum formula to include newly the added rows

Create Formula To Add 0.2 To Cell Value If Its Greater Then 0
I want to create a formula which will add 0.20 to the value of a cell if its greater then 0.
Basically i have a list of cells some of which will have a value and i want to sum this list and add 0.20 if there is a number in the cell.
I am trying to create a quoting system and basically its a list of rooms etc with the wall lengths and i am adding 0.20 to each wall to allow for excess but for example if there are no measurements in bedroom5 i dont want the 0.20 added.

WORKDAY Formula Replacement W/o Use Of Analysis Add-In
My boss has made it clear I can't require our people to use the add-in.

I have searched under WORKDAY as well as under Barry Houdini, since someone said he has come up with plenty of replacements for the Analysis add-in... but I am not finding it.. can someone help?

Using the add-in, this is what I have:

myCell.FormulaR1C1 = "=WORKDAY(RC[1],-RC[2])"

Add Nested Formula To Cell On Another Worksheet
One of my macros is a tied to a worksheet_change. The macro runs and gives me the result that i want, however excel flips to the sheet that the sub is running for.

example:

Sheet1, Change the cell
excel flips the visible to sheet2
macro runs

Its kinda of annoying. I would like excel to stay on sheet1 where i am trying to finish data entry. I suspect it is how i have writen the macro. Any help at all on how to clean it up.. or a better way to write the same thing would be very appreciated.

Sub TermPlacement()

' Term_Placement Macro
' Uses the Value of Term to insert a formula repeatedly in a colum.

Dim sNFormula As String
Dim rTerm As Range
Dim rStart_Cell As Range
Dim rNpayment As Range

'The reference cell located directly above the first cell in column
Set rStart_Cell = Worksheets("Floor Plan").Range("\$B\$13")

Find Empty Cell And Add Formula
I have a spreadsheet that is pulled automatically from an access database.
The information that is pulled is invoices that have been paid for each of the temps working for our company.
Each month I have to give a figure per cost code on how much they are accruing so that they can see how much is still outstanding.

Right so what I am trying to do is have a macro take the temps charge rate then * 40 hours and place the amount into the empty cell.
As you will see in the attachement some of the cells are coloured. The purpose of the colouring is show that a invoice has been processed and paid for that week so no value is needed in that cell.

Then total all the cells at the end.

Formula To Add Up Hours That Were Attended For Various Individuals
I'm trying to figure out how to write a formula to add up hours that were attended for various individuals. My data is coming in from scanning their barcodes, so I'd rather not manually add their hours. I have these columns: Date, Time, First, Last, Member ID, CEU. My hope is that I can take the Member ID and write a formula that adds up the CEU column.

This issue I'm having is how to deal with the variables of someone only attending two sessions, while another attends four? I'm not sure how to do this so I'm seeing if anyone knows if this can this be done?

Automatically Add Cell Formula With Code
Cell b2 contains the formula
=IF( COUNTIF(B\$1:B2,B2)=1,MAX(A\$1:A1)+1,"")
I want cell b3 to contain the formula
=IF(COUNTIF(B\$1:B3,B3)=1,MAX(A\$1:A2)+1,"")
I can do this within the spreadsheet by simply highlight and drag down to autofill and excel updates the formula references as I need

However - because the sheet gets very large (its a sort of rough database)
I dont fill in all the formulae on a blank sheet, but each time a record is entered using a VBA form I have the following working code copy the formula

ActiveCell.Offset(nextline, 0).Copy
ActiveCell.Offset(nextline + 1, 0).Select
ActiveSheet.Paste

However I dont want to use 'Active' - because I have to work out what cell is selected before this is executed and then reselect it after, which also causes the screen to flash when the active sheet changes so I tried the following which does not work

Worksheets(" Analysis").Range("A2").Offset(nextline, 0).Copy
Worksheets("Analysis").Range("A2").Offset(nextline + 1, 0).Paste

because 'object does not support this property or method' on the Paste.

I can save the code in a string and modify the string to generate the new code, but I cant find a way to assign the new formula to the next cell.

Variable = A Sum- Formula To Add The Cells
ActiveCell = "=(SUM(R3C5:R[0]C[-3]))"

In a macro I use that formula to add the cells of column E (or 5) from Row 3 down to the row of the active cell which is in column H (or 8), (the active cell would be H9 in this instance); and the result goes to the active cell as the formula =SUM (\$E\$3:E9).

But I want the result to go directly to a numeric variable, not to a cell. Something equivalent to this:

a=(SUM(R3C5:R[0]C[-3])), which is not correct.

What would the correct syntax be?

Creating A Formula To Add 30% To Worksheet Figures
I have a late report due @ work.... I have people who have worked for 4 days to get me numbers i need and they are not right.....

Anyways I have a spreadsheet with a whole lot of numbers on it... I need to add 30% to each number. What is the easiest way to set this up.

Formula To Automatically Add Values Each Week
I need a formula on Cell C3 on the attached Sheet1.

This should add numbers from the Actual columns as they are updated; i.e., as soon as I populate 'Actual' columns such as F, I, L, O, R... Cell C3 should add up the numbers automatically. This way I don't have to update the Cell C3 manually each week I populate the Acutal columns.

Formula To Add Letters With Numeric Values
formula to add letters but with a numeric value. this is for a schdule sheet. where w would equal 7.5 and x would be 0.

i am using this
=SUMPRODUCT(--(ISTEXT(B3:H3)))*7.5
reads the w and adds up ok but need to be able to put w for work and x for off days and still add the total hours

Formula To Automatically Add New ID Number To Code
I would like to add something (formula) to the following code to automatically add a new ID number with each new entry. Some of the entries will have the same name but each time a new entry is added I want the ID number to increase by 1. Assume that the first entry is 0001.

Automatically Add Formula To Next Cell In Column
I have a list of names in column 'A' and a lookup formula in column 'B'. Rather than drag and copy the formula to succeeding rows I would like to have the formula added to column 'B' when a value (name) is entered into column 'A'.

The list in column 'A' is auto sorted by alphabet and the lookup reference in column 'B' need to stay with the value (name) in column 'A'. Mabe some conditional format/formula that says if their is a value in cell A2 to copy the formula in A2 into A3 and so on down the list.

Formula To Add Amount Of Rows Used In A Column
I know a bit about excel nut not a hugh amount! i need to know a formula that adds the number of rows used in a column.

NAME
rob
dave
jim
darren
lee

i need a formula that will tell me there is 6 rows used in that column (not including the title).

FORMULA TO ADD AMOUNT BASED ON TWO Or MORE CONDITIONS
i need a formula to add amount based on two or more conditons. From the drop down list on each change i want to do the sum to be displayed in C18; see the attached file for reference. i can do the same thing using pivot tables but i want to the same to be done using the array formulas. Also tell me any other array formulas like this which are useful.

Formula To Add Amount Of Rows In A Column
I know a bit about excel nut not a hugh amount! i need to know a formula that adds the number of rows used in a column. Does that make sense?? ok

NAME
rob
dave
jim
darren
lee

i need a formula that will tell me there is 6 rows used in that column (not including the title).

Add Formula To Last Cell In Range Via Macro
I currently have the following code to copy and transpose data from one worksheet to another:

Range("E3:E6,E10:E53").Select
Selection.Copy
Sheet3.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Transpose:=True

What I would like to do is add to this code. I would like to add the following formula to the cell at the end of that pasted row. =VLOOKUP(Range,LeaveIndicator,6,FALSE). In this case the Range needs to be Row D and the last row of sheet3.

Add Subtraction Formula To Cell Via Code
If I wish to amend the below code such that it must use the cell one row above minus the cell on the left, how should I go about it?

Eg,

If in cell C10, the formula is to use C9 minus B10.
If in cell C11, the formula is to use C10 minus B11.

mylogoff.Offset(0, 1).FormulaR1C1 = "=RC[-1]-RC[-2]

Add Formula Via Macro On Changing Range
I’m wondering if it possible to have a variable in a Formula. I was looking around, but non of the other examples I found was working. The code I was trying is:

Sub All()
Dim lngDataRows As Long
Dim lngDataRowsSum As Long
lngDataRowsSum = Range("A5").End(xlDown).Row
Range("A" & lngDataRowsSum).Offset(1, 0).FormulaR1C1 = "Sum"
Range("M" & lngDataRowsSum).Offset(1, 0).FormulaR1C1 = "=sum(M6:M & lngdatarowssum)"
End Sub

Another code I tried was from this thread

Sub All()
Dim lngRowsBottom As Long
Dim lngRowsTop As Long
lngDataRowsSum = Range("A5").End(xlDown).Row
Range("C10").formula = "=sum(R["&lngrowstop&"]C:R["&lngrowsbottom&"]C)"
End Sub

The error message is: “Compile error: Expected: enf of statement” and the part “[C:R[“ is highlighted. Tried various ways to solve it but nothing was working.
Does anyone of you know how to get it up and running?

Add Cell Number To End Of Corresponding Formula
I have a monthly report on an excel spreadsheet that I must sum two columns from the previous month for every row in the sheet. I wish to take the value from column B and add to the end of the formula in column A. For instance my column A would contain the following: "=1200+6595+2599+275"

Column B would be a single number, i.e. "3200"

I want to be able to click a button and get "=1200+6595+2599+275+3200" in column A and place a "0" in column B for every row on the sheet. I have a pretty good understanding of VBA, but I am still learning the Excel object model.

Add Relative Row Formula To Cell
I'm importing comma delimited files into Excel. These files will contain formulas, but do not know on which row they will end up in once imported. My formula's need to reference the row they have been pasted to. For example, If I paste the formula on Row 2 the formula would be =A2+B2

Row 3 =A3+B3

However, since these formulas are coming in via a non-excel file, I need the cell reference to be dynamic. Something along the lines of =A#+B# where # would be interpreted by Excel as the current row the formula is on. (I know that '#' isn't correct.)

Add Nested Array Formula To Range
I have the following code that I am trying to distribute over an array. The best case scenario is that the code enters the text of the formula instead of the calculated value. When I tried to tweak a few things I couldn't even get the text to show up. This seems to only happen on the formulas which include If_Then statements. Other straight forward formulas such as "sum" etc... seem to go into place and calculate.

Sheets(1). Range("b3:s3").FormulaArrayR1C1 = _
"=IF(AND(MAX(2!R[2]C:R[6]C)>0,SUM(3!R[1]C:R[5]C)<0),MAX(2!R[2]C:R[6]C),"")"

IF The Value In R493 = 1 THEN Add 1095 To The Formula In Cell AA493
I have a date in cell F493 (DD/MM/YY)

I have the following formula in Cell AA493 =F493-21

What I need to do is look at Cell R493 IF the value in R493 = 1 THEN add 1095 to the formula in Cell AA493 . IF the value in R493 = 2 THEN add 730 to the formula in Cell AA493. IF the value in R493 = 3 THEN add 365 to the formula in Cell AA493. IF the value in R493 = 4 THEN PRINT "No Extended Guarantee"