# VBA Code To Calculate Two Column Values Automatically

Aug 10, 2009

I have a simple script that puts the save time in the last row of column C when the spreadsheet is saved. Another script puts a username in Column A and column B contains the time opened. I would like to add to the save script to make column D od the active row have a calculation similar to:

## Calculate Mean Of Column B Values Based On Date Values In Column A?

Dec 5, 2013

Below the two columns represent Column A (date and time) and Column B is temperature.

How can I take the mean of all temperature values for May 1, then take the mean of all temperature values for May 2, etc.?

5/1/2013 0:0012.4
5/1/2013 1:0012.4
5/1/2013 2:0012.5
5/1/2013 3:0012.5
5/1/2013 4:0012.5
5/1/2013 5:0012.4
5/1/2013 6:0012.4

## Formula To Calculate Average Values In Column B Where Value In Column A

Mar 11, 2013

I'm currently using this formula to calculate the average values in column B where the value in column A ='s E1 eg:

=AVERAGEIF(\$A\$2:\$A\$21,E1,B2:B21)

Instead of doing this however, I need the formula to calulate the average from column B where the "Date" in column A ='s the year and month I specify in other cells.

Year value specified in: F1
Month value specified in: G1

## Ranges, Column And Changing Values Automatically

Oct 14, 2008

I m trying to get around here within the company I work for.

I'm interested to know how to set a specific range value e.g. "5 to 10" once a value is between those 2 numbers, and then if the value in the next row below is 34.12 it will be "30 to 35".

Here's an example code I constructed, but the darn thing just keeps on running

## Code That Automatically Run After Selecting Item In Data Validation Dropdown List In Column C

Oct 12, 2011

I have a dropdown list in C24:C50 (=CategoryList) with data validation and a sub list in D24:D50 (=ItemList) with data validation. I am looking for a way to have code automatically run after selecting an item in the data validation dropdown list in column C.

Example; I click on C24 and make a selection. I what it to trigger code that would move me to D24 and open up the data validation list in D24. After the selection in D24 I would like it to move me back and down 1 row to C25. I have not found anything directly related to this but I have found that code can be run after a selection in a valadition list.

## Select Case Code For Column A Values To Populate Results In Column B In Same Row?

May 28, 2014

I'm trying to write a simple VBA code to loop through values in the range A14:A138 and based on the value (of a possible four values) in any row of that range, populate the adjacent column in the same row with a conditional result. For example if A14 = "Cat", then B14 = "I"; if instead A14 = "Dog", B14 = "II", etc. If there is no value in column A, the result should be blank (i.e. "").

I believe are in coding the destination range since I can get it to work for just one cell in B! Below is my code that is not working...

[Code].....

## Formula To Calculate Number Of Unique Values In A Column With Filters

Jul 9, 2014

I have a file with a column containing a series of records where a reference number can be repeated several times. I want to create a formula that will count the number of unique reference numbers in the column.

However - and this is the tricky part - I need it to allow for when the report is filtered, i.e. something like a SUBTOTAL function which ignores the hidden values.

So, the column in the full report has 691 unique values across 2,200+ records. If I apply a filter the column only has say 78 unique values. Is there a formula that can calculate this?

## Copy And Paste Unique Values From Column And Use Countif Function To Calculate Average

Jan 19, 2013

I'm stuck on the final piece of my macro project. I've attached a workbook with two sheets: sheet 1 is what I currently have, and sheet 2 is what I'd like my report to look like when complete.

On Sheet 1:
- column B is called Supervisor Name
- Columns D-O are months of the year, with either a Yes or No in each cell.

I'd like to write a macro that will:
- Take all unique values in Supervisor Name column, and paste these Above the current table.
- For each Supervisor, and each month, I'd like it to calculate, as a %, the number of Yes mentions in each month divided by the total cells (Yes/(Yes+No)).

Sheet 2 contains the output, in the format I'd like to see it.

Sheet 2:
Supervisor Name
October
November
December

Supervisor 1
8.3%

[Code] .......

## Automatically Calculate The Percentage

Jun 14, 2007

code needed to be able to automatically calculate the percentage needed from one year to the next. I simply need it to automatically create a third column each time the copy old data button is pressed, and I need the column to have the percent increase ((current year - past year)/current year). Hopefully you can understand what I am aiming for. I have the current code below, and I will attach a compressed and stripped version of my workbook.

## How To Calculate AGE Automatically Using FUNCTIONS?

Nov 1, 2008

Hi to all. I am trying to calculate a list of person's age automatically using functions. Is that possible? I am using Microsoft Excel 2007. I try searcing the web and they say DATEDIF.... But i just can't find the function in Excel 2007!

## Automatically Calculate Date

Mar 14, 2008

I made to automatically calculating by date which is time formating want to count the stem hours, but it prompted error?

Sub UpdateColG()

Dim IRow As Long

For IRow = 3 To Range("F").End(xlUp).Row
Range("G" & IRow).Value = Range("F" & IRow).Value - Range("F" & IRow - 1).Value
Next
End Sub

===================================
the logical is Range (F2-F1) returns to (G2)
sample : (F2)10:30-(F1)09:45 =(G2)00:45.

## Conditional Formula And Calculate Automatically

Mar 1, 2009

I am creating a sales per day/week worksheet for my reps to keep track of their sales. I have created the sheet that they only have to enter in what they have sold and it automatically calculates what they have sold per hour in the day, and what that makes their weekly total.

The problem is sometimes they will work 5 days, sometimes 4, sometimes 6 or 7.

im trying to fix the weekly average formula to divide by 7.5 for 1 day worked, 15 for 2, 22.5 for 3, etc all through 7...

this is the formula I have now, obviously it doesnt work

=IF(COUNT(B7:H7)=1,SUM(E42,E75,E110,E143,E175,E207,E238)/7.5,IF(COUNT(B7:H7)=2,SUM(E42,E75,E110,E143,E175,E207,E238)/15,IF(COUNT(B7:H7)=3,SUM(E42,E75,E110,E143,E175,E207,E238)/22.5,IF(COUNT(B7:H7)=4,SUM(E42,E75,E110,E143,E175,E207,E238)/30,IF(COUNT(B7:H7)=5,SUM(E42,E75,E110,E143,E175,E207,E238)/37.5,IF(COUNT(B7:H7)=6,SUM(E42,E75,E110,E143,E175,E207,E238)/45,IF(COUNT(B7:H7)=7,SUM(E42,E75,E110,E143,E175,E207,E238)/52.5)))))))

## Stopping Formula To Automatically Calculate

Feb 20, 2009

Is there a way in which I can get excel not to run any formulas until I run some sort of command or click a button in order to tell it to?

For example, I have two sheets, one sheet has raw data entered manually into each cell, the other sheet has a large amount of formulas to calculate totals from this raw data. But every time a number is entered into a cell in the raw data sheet Excel says 'Calculating Cells', I want to be able to enter all my raw data, then go to my totals sheet and execute all my formulas.

## Automatically Calculate Date In The Future

Jul 25, 2007

I am trying to create a spreadsheet that will automatically calculate a date in the future.

The future date calculated needs to take the time NOW (as the user completes a field) and determines if this is before MIDNIGHT on the following SUNDAY. If so it calculates the date as a Tuesday 2 weeks on. For example... I request a date now on 1st Jan which is a Thursday. This is before midnight Sunday and so the date calculated would be Tuesday 20th January.

If the time NOW is after MIDNIGHT Sunday and before the next TUESDAY.. ie 0001hrs on the Monday and 2359hrs on the Monday then the date returned should be Tuesday 3 weeks on.. e.g 27th.

It would also need a function that would allow the total number of referrals on a Tuesday to 20 at which point it would overspill and provide a date on the Friday of the same week.. so in the first example it would be Friday 23rd

Is this something that can be achieved with formulas and a set of dates for it to calculate from or do I need to find someone who can do it in VBA for me.

## Automatically Calculate Cell When Changed

Oct 24, 2006

There is a column in my spreadsheet which needs to reflect a " percent done".
I used to enter just a numerical value in (eg. 20) to reflect 20% done. My boss wanted it to show as an actual percentage (eg. 20%). I changed the format to Percent, Zero decimal places. Then you had to enter .20 to get the 20% to show.
Now my boss doesn't like that - and wants to be able to enter "20" to get the 20% to show. I put the following code in my SelectionChange event...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'other logic
If Target.Column = Asc(PercentDoneColumn) - 64 Then
If Target.Value <> "" Then
Target.Value = Target.Value / 100
End If
End If
'end other logic
End Sub

My problem is that this works well when putting a new percentage in, but not for when you click on a cell that already has a percentage in it. For example, if you have .2 (displaying "20%" in the cell), and click on that cell, it divides that number by 100, and gives you 0.2%, displaying as "0%". I want to enter a number between 0 and 100, have it displayed and stored as a percent. At this point, I'd settle for the displayed, but would prefer to have it stored as a percent, too.
I also want it to not reset my value unless I'm putting a new value in!

## Automatically Calculate The Bonus Points Into A Cell

Mar 29, 2006

I am updating a sports competition ladder. There are two aspects to compiling the table I would like to automate but have been unable to find out how via the excel documentation.

The aspects are.

1. When a team loses by a margin of seven points or less below the total points scored by the winning team then the team is awarded a single "1" bonus point that contributes to their overall competition points tally.
2. When a team scores four tries or more they are likewise awarded a bonus point.

I require formulas that will automatically calculate the bonus points into a cell.

Situation One
cell 1 = Points For (eg. 17)
Cell 2 = Points Against (eg. 24).
In this instance the team did not win. They scored 17 points and the winning team scored 24 points. Therefore the team lost but lost by seven points and is therefore entitled to one (1) bonus point.
SOLUTION NEEDED - I need a formula that will calculate whether cell 2 is seven or less than cell 1, and if so automatically place a total of "1" in cell 3.

Situation 2
Cell 1 = Tries Scored (eg. 5)
In this instance the team has scored five tries. They have scored more than four tries or more and is therefore entitled to one (1) bonus point.
SOLUTION NEEDED - I need a formula that will scan the number in cell 1. If this number is 4 or more, then a total of "1" should be automatically placed in Cell 2.

I have looked into the excel help but to no avail and the search terms are so broad it was a nightmare trying to search the forums.

## Monthly Depreciation Of An Asset To Automatically Calculate

Jan 25, 2010

I am working on a depreciation schedule in which I want the monthly depreciation of an asset to automatically calculate and, if the asset is fully depreciation, caclulate a zero or the balance to be depreciation (if less than the monthly depreciation). Please see below example. As you can see my asset is fully depreciated at the end of February but because there remains a \$0.01, the formula is calculating another month in March and then reversing it in April (less the \$0.01). Here's the formula I'm using. What am I doing wrong?

Column H is March, Column C is my monthly depreciation, and column E is my beginning book value:

=-IF(ABS(SUM(\$F2:H2))>=\$E2,(SUM(\$F2:H2)+\$E2),IF(\$E2=\$C2,\$C2,\$E2)))

Purchase
PriceMonthly DepreciationAccumulated Depreciation 12/31/20091/1/2010 Beginning Book ValueJan-10Feb-10Mar-10Apr-10May-10
LCD PROJECTOR 797.12 13.29 (770.54) 26.58 (13.29) (13.29) (13.29) 13.28 -

## Automatically Calculate Based On CheckBox State

Jan 23, 2008

I have a Worksheet_Change event like the one below. When Checkbox1 is ticked the it subtracts and when it's not, it adds:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim intValue As Integer
If Not Intersect(Target, Range("W12:X24")) Is Nothing Then
intValue = CInt(Target.Value)
If CheckBox1.Value Then intValue = intValue * -1
Select Case Target
' Apples
Case Is = Range("W12")
Logbook.Show
Range("I10").Value = CInt(Range("I10").Value) + intValue
Case Is = Range("X12")........................

## Excel 2013 :: Automatically Calculate Area Between Two Charts?

Dec 22, 2013

I have Excel 2013.

Find attached a sample fileDummy for area between.

charts.xlsx

## Automatically Calculate 2 Cells Based On Cell Changed

Apr 25, 2009

I have some rather complex formulas that when a value is placed in one of any three cells it solves for the other two, taking values from other places on a worksheet (including the cell itself).

Scenario: Cell F10, G10, and H10 all have formulas in them. If a value is placed in F10 it solves for G10 and H10. If a value is place in G10 it solves for F10 and H10. If a value is placed in H10, it solves for F10 and G10.

I really do not think the actual formulas matters at this time...

Has anyone done this before, using VBA code to place the formulas in cells F10 G10 and H10? Look for a working example of this if possible.

## Excel 2011 :: Automatically Calculate Running Subtotal By Category

Mar 27, 2013

I am using Excel 2011 for Mac.

I am creating a workbook with multiple worksheets, one overview sheet and then one sheet for each month of the year.

In the monthly sheets I have a Category column for which I have created a drop-down menu of expense categories. Then I have an Expense Amount column where I enter the amount spent.

The Category column will not necessarily remain sorted by category because I will be entering the expenses as they come up throughout the month so the categories will be all mixed up, unless I manually sort them.

What I want to do is somehow automatically calculate a running subtotal by Category (that will update with each new entry) and simultaneously automatically transfer the running subtotal for each Category to a specific cell on the overview sheet.

## Worksheet Calculate Event To Automatically Change The Color Of A Cell

Jan 30, 2009

I am trying to use the worksheet calculate event to automatically change the color of a cell only when that particular cell changes. In E2 of the worksheet is a formula use to determine rating based on the result of 2 other cells. The rating is classified as follows

Low
Moderate
High
Maximum

I would like to generate a different set of color to the cell and fonts for each of the rating. For example,

"Cyan" to the cell E1 and E2 with Black font if the result is "Low"
"Plum" to the cell E1 and E2 with "Black font if the result is "Moderate"
"Blue" to the cell E1 and E2 with "White" font if the result is "High" and
"Red" to the cell E1 and E2 with "White" font if the result is "Maximum"

## VLookup "Inputing Data Values Automatically Based On Data Value In Another Column"

Sep 13, 2009

I have a thread in here called "Inputing data values automatically based on data value in another column". I have determined that I need to use the VLookup function.

## Calculate Values Cumulatively Over And Above Previous Values

Apr 17, 2007

REFER TO MY ATTACHMENT.

Can I get a simple ONE LINE formula to calculate the below combination

Basic Cost = 100 (A) (my Material cost)

ADD : 10% for Freight (B)

A+B = C

ADD : 10 % for Overheads on 110 (On C) = D

C+D = E

ADD : 15% towards Margin on 121 (on E) = F

E+F = G

WHERE G = 139.15

I require this because my %s varies according to products.

## VBA Code To Calculate When Criteria Met

Nov 25, 2013

In the first tab of my workbook I have a data table. In K2 down of that tab is a list of names, in D2 is their form, in I2 is the Type (there are 3 Types which start with (R), (P) or (S), in J2 is the points score associated with the Type and in H2 is the date they received the points.

For each name in the table I need it to sum the running points totals for (R), (P) & (S) Types and when they reach the minimum points thresholds below I want it to record the earliest DATE this threshold was achieved.

School Award: (R) = 20, (P) = 40, (S) = 20
Bronze: (R) = 40, (P)=80, (S)= 40
Silver: (R)= 60, (P)=120, (S)= 60
Gold: (R)= 80, (P)=160, (S) = 80

The output should look like:

Name
Form
School Award
Bronze
Silver
Gold

Anders Peter
7BN
20/9/13
15/11/13

Ball Jim
7BN
20/11/13

Carter Mark
7BN
19/9/13
8/11/13
24/11/13

## Calculate IRR In VBA Macro Code

Jul 7, 2009

I used the following statements to (try to) calculate an IRR in my VBA program and received a runtime application error 1004. Tried several variants of the statement but cannot seem to escape the error.

Dim IRRValues(12)

For iIRR = 65 To 74
For iTIGRcubYear = 1 To 11
IRRValues(iTIGRcubYear) = Worksheets("TIGRCub"). Cells(iIRR, 3 + iTIGRcubYear)
Next iTIGRcubYear
aIRR = Application.WorksheetFunction.IRR(IRRValues, 0.2)
Worksheets("TIGRCub").Cells(iIRR, 14 + Scenario) = aIRR
Next iIRR

## Calculate The Average Of A Group Cells In One Column Based On The Condition Of Another Column

Oct 2, 2008

I'm trying to figure out if there is a formula I could use that will calculate the average of a group cells in one column based on the condition of another column. It's hard to explain, so I will show an example. All the data is on a one worksheet and I'm trying to show totals and averages on another worksheet. Location, Days

17, 4
17, 3
17, 5
26, 4
26, 8
26, 10
26, 7

On a different worksheet I would want to know what the average days are for each location. So is there a formula that I could use that will look at column A for a specified location number and then average all the days in column B for that location? I'm using Excel 2003 and have tried using the Average(if) but with no success.

## Calculate Average For Column Based On Moving Start Column?

May 3, 2013

I have a pivot that includes customer data and I need to create an average for each row based on the first populated column for each customer. After one month of units appears for the customer, I need to calculate the average going forward, and if there are blanks after that they should be treated as 0 in the average. Below is an example of my description of the pivot.

Jan Feb Mar Apr
Customer #1 1 1 3
Customer #2 1 3
Customer #3 2 2

So, the average for customer #1 would be calculated from Column B to Column E, and factor column D as a 0 in that calculation. Customer 2's average would start in Column C and go to Column E, and factor column E as a 0 in that calculation. Customer 3's average would start in column D to column E.

## Find Duplicates In Column A And Calculate Difference Between Times In Column B

Apr 4, 2009

I love this forum, and am usually able to find the help I need without bothering anyone However this one has me stumped and I wonder if anyone can help. It feels like it should be a fairly simple solution, but they can often be the ones that are most eluding LOL!

I have two columns; in column A are incoming telephone numbers and in column B are the date and time the calls were made. (I've put a few hashes in column A just to maintain confidentiality of the numbers, but in reality the cell is formatted as text in order to maintain the leading zero, and entries will follow the format 01234567890)

A sample would look like this:

0##6270####01-Mar-2009 00:01:440##6271####01-Mar-2009 00:03:020##6271####01-Mar-2009 00:03:040##6272####01-Mar-2009 00:16:330##6273####01-Mar-2009 00:30:490##6274####01-Mar-2009 00:55:470##6274####01-Mar-2009 01:06:170##6274####01-Mar-2009 01:07:420##6275####01-Mar-2009 01:08:360##6275####01-Mar-2009 01:11:410##6276####01-Mar-2009 01:13:45

Some numbers only call in once, I need to identify them as only called once.

Some numbers call twice, if they do I need to be able to show time it took between call 1 and call 2.

Some numbers call more than twice. For each successive call I need to be able to show the time since the previous call.

In my mind, the results table would need to look something like this:

NumberTime of callTime between
1st and 2nd call
Time between
2nd and 3rd call
Time between
3rd and 4th call
0##6270####01-Mar-2009 00:01:44Only called once0##6271####01-Mar-2009 00:03:0200:00:020##6272####01-Mar-2009 00:16:33Only called once0##6273####01-Mar-2009 00:30:49Only called once0##6274####01-Mar-2009 00:55:4700:10:3000:01:250##6275####01-Mar-2009 01:08:3600:03:050##6276####01-Mar-2009 01:13:45Only called once

## Passing Variables And Values Between Sheet Code And Module Code

May 13, 2009

I have some buttons in different sheets in an excel file, each button has its own code, that is the reason I can not move the code related to each object to another location (sheet or module).

And I have one piece of code in Module1 (Auto_load) in order to execute automatically this routine every time file is opened. Inside "auto_load" routine I initialize some values of some check buttons,options buttons and positions of some objects in diferent sheets, but I can not pass the value of variables between Module and Sheet's code even when I declare as public variables and/or function.

I have the following structure: ...