# 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!

## Checkbox IF Functions: Calculate Checkboxes

Apr 13, 2009

I am trying to calculate checkboxes in my excel worksheet and am really having a hard time in doing so. - I am usually pretty good at formulas but this one is causing me to drink as it is probably something pretty simple that I have forgotten to do. Here is my dilemma:

Equipment Rentals.xls

I am trying to calculate that if cell C4 is checked then it will calculate cell a4*d4 (cost of equipment x number of days), or if cell E4 is checked it will calculate cell F4 *A4 and if cell G4 is check it will calculate cell H4*A4. - the totals will be calculated in cell I4 depending on what check box is checked.

When I write the formula as =IF(C4=TRUE,A4*D4,IF(E4=TRUE,A4*F4, IF(G4=TRUE,A4*H4,0)))

## Calculate With Subtotal Functions And Write Value In Cell

Aug 22, 2014

i have wrote the following code snipped:

Sheets(2).Activate
Sheets(2).Range("D8").Value = Application.WorksheetFunction.Subtotal(109, Sheet(4).Range("BJ3:BJ" & b)) + Application.WorksheetFunction.Subtotal(109, Sheet(3).Range("BJ4:BJ" & a))

The debugger said: That "Sheet" is not allowed in the subtotal-function. because i would like to calculate two subtotals in two sheets an write the sum in another sheet

## User Defined Functions And Using Application.Volatile To Update Sheet Automatically

Jan 13, 2009

I created a user defined function and using Application.Volatile to update sheet automatically.

But this slowing down my sheet execution alot.

When i remove that it doesnt take much time but no automatic update.

## 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.

## 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!

## 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:

## 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"

## Functions Compared With VBA Functions

Mar 14, 2008

I am aware of the following topic in the VBA Help file:

"Using Microsoft Excel Worksheet Functions in Visual Basic
You can use most Microsoft Excel worksheet functions in your Visual Basic statements. To see a list of the worksheet functions you can use, see List of Worksheet Functions Available to Visual Basic.

Note Some worksheet functions aren�t useful in Visual Basic. For example, the Concatenate function isn�t needed because in Visual Basic you can use the & operator to join multiple text values."

And I'm aware of how to call Excel funcitons from within VBA; e.g., answer = Application.WorksheetFunction.Min(myRange)

However, not only are some Excel functions not useful; the fact is they cannot be used because VBA has a native function that does exactly the same thing and you have to use that native VBA function to achieve your goal. It is these overlapping functions that I am especially interested in. I want to know what I should use directly in VBA and what I need to go to Excel for.

## IF THEN Functions..

Dec 4, 2009

I want to calculate the following...

If Cell A>40, Then the result should be ((Cell A-40)*23.25)
If it is not >40, Return 0.00

This is for a timesheet / overtime.

## Possible Functions In VB

Jan 12, 2010

Is it possible to use excel functions with in VB? (such as CONCATENATE, LOGEST etc.) For example, if I have a site no. and data for this site in an array could I append the site number to the data by:

## Use Of Vb Instead Of All My Functions

Apr 19, 2007

i have a spreadsheet acting as a type of database (our work wont pay for more licences for access!), and it has multiple functions running. well, it has 3000 lines x2 for 2 sheets with about 5-10 full colums of functions. this means the template file i have starts off at about 10mg. with data, this goes upto around 15-20mb. howver, we get a lot of the massive size increases. however, the sizes we get are often upto 150mb! so, i dont know vb at all, and have only learned functions recently. was hoping someone might be able to help out sorting this out? i dont know if i can upload a coppy online, but i can email.

## Sum Functions - Add All And Return A Value

Jun 9, 2014

So I have say 1-4 columns that may have an A to describe a student as absent. In the 5th column I'd like it to add all the A's and return a value of 1-4 or if possible... a result that says 4A etc. The column might have another letter in it so i only need to add it up if it has an A in it.

To visualize, i just want to add up the students that have not attended a class.

## Functions Use For Various Conditioning?

Jun 19, 2014

I wish to set 2 different cases (good and normal sales) for a list of data.

During good sales
- fruits can be sold within 4 hours
- Fruits sold off during peak season (Definition of peak season: 2 calendar days before or on public holiday or 10 days before 31st May)
- < 2 fruits per type

Date of public holiday for example: 1 Jan 2014, 18 Apr 2014, 1 May 2014

For normal sales
- fruits only able to sell of the next day
- Sell of on non-peak season
- > 2 fruits per type

Sample file attached.

## NESTED IF And AND Functions?

Jun 30, 2014

I am trying to write a formula that will satisfy the following:

C1 and G1 are number values.

IF (cell E1 does not contain any text) AND (G1-C1-12.5>=0), then output (G1-C1-12.5), otherwise output nothing (""). BUT, IF (cell E1 DOES contain text) AND (G1-C1-13>=0), then output (G1-C1-13), otherwise output nothing.

## Sumif Functions

Jan 8, 2009

I have an entire excel column which was filled-in with values ranging from 1000 to 40000. What I was trying to do is to just sum up the all the values which are between 9000 and 20000. I tried using the sumif function =sumif(and(A5:A40,">9000"),(A5:A40,"<20000")) but it does not work, it says that the formula that I typed contains an error.

## Marco Vs Functions

Nov 23, 2009

Currently, all of our data in Excel are linked by using Excel Functions between workbooks and some of the files are so large that it may take 10 minutes to open and copy and paste a cell may take three minutes (which is ridiculous slow).

Pardon my ignorance, my understand of Excel VBA/Marco is about automation. Even i use marco to run the excel, i still need formula in the cell in order to perform the task, right?

## Worksheet Functions In VBA

Jan 15, 2007

Is possible to pass names into a worksheet function and whether values from other sheets are able to be passed in, eg i am trying to paste this function into the work sheet to find the left two characters of a certain string. However the string position varies:

= left(worksheet name! row(1) column.range("startno"),2

## Check Box Functions

May 18, 2009

I have a cell (D19) that I want to be automated to return a value based on a formula (it will equal Cell D15). But I also want to have the ability to allow the user to override this and enter a manual value if needed. My idea on how to do this is having a check box with a macro where if it is checked it will unlock the cell and clear out the formula and when checked it will reenter the formula and lock the cell.

Also same form - I need a check box that will check all applicable check boxes (all boxes under Deductions to be taken). Can anyone assist me in setting up these check boxes so that my functions will work, or if there is a "better" way of providing these functions please let me know.

## Interest Functions

Dec 3, 2005

The file I am working with is available here

(1) I need to work out the value of 2 years worth of interest on a loan - details would be

Loan = 30,180.00
interest = 14.90% pa (calculated daily)
term of loan would be 60 months

I am currently using an ammortisation speadsheet which calculates the interest per month etc ... at this time I simply highlight and calculate the interest which would be two years worth ...

the problem is this calculation varies from the bank interest calculations (although the repayments are the same and the total interest payable amounts are the same)

The banks calculations are said to be done on the basis that the minimum monthly repayment is met and there are no additional fees thrown in ... so I cant understand the difference ...

(2) In addition to the above question, but working on the same loan, traditionally amortisation tables calculate the amount of the repayments, the interest component and the principal component ... some include a column where you can factor in additional repayments on a row by row basis (like the one I am referring to) the table assumes that the payment is made on the due date ...

what I would like to be able to do is
(a) insert the actual payment dates
(b) if no payment is recorded for that period, automatically insert an overdue fee