Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Provident fund calculation for salary computation

Hi,

I am preapring a salary computation programme for my company. Now I am facing problem to calculate automatically CPF contribution payable and recoverable according to salary. I am looking for a VB programme or relevant formula to solve.

Please find attached file and advice me how to solve this calculation.

Thanks
jvlkerala


Post your answer or comment

comments powered by Disqus
I work in a large Public Service Payroll Department. I am trying to create a spreadsheet that will automatically calculate arrears payments for salaried staff. What I need is something that will make the calculation after I provide a start date an end date and old and new salary per annum. I'm self taught using excel but at a very very basic level.

My problem is that I don't know how to enter a formula which will give me the correct amount between 2 dates. For example

01/10/2005 to 16/09/2005 old salary PA 16000 new salary PA 17500. The manual formula would be:
((17500 - 16000)x1/12) + ((17500 - 16000) x 1/12 x 16/30)) = £191.67

Thanks

Brian

I'd appreciate help on a formula to calculate pension contributions based on annual salary where contributions are based on 4.5% of the first $41,100 of salary PLUS 6% on the balance of the salary. For example annual salary $50,000 (I wish!!!) Contributions of $1,849.50 (4.5% of $41,100) PLUS $534.00 (6% of $8,900) equal $2,383.50 NOTE While this is example is for a salary > $41,100 the formula who also need to calculate for salaries below $41,100.

Thanks for any help Peter

I want to make a fill-in calculator for a website using frontpage. It
requires andaverage of 5 numbers tht need to be inputted along with another
number that is used as a multiplier. Average of five numbers times multiplier
(number between 5 and 30 to 2 decimals) times 2% then divided by 12. I can
build the spreadsheet and formula my stumble is getting cells locked down and
transferred to Front page.Using Excel XP and Frontpage 2000

I want to make a fill-in calculator for a website using frontpage. It
requires andaverage of 5 numbers tht need to be inputted along with another
number that is used as a multiplier. Average of five numbers times multiplier
(number between 5 and 30 to 2 decimals) times 2% then divided by 12. I can
build the spreadsheet and formula my stumble is getting cells locked down and
transferred to Front page.Using Excel XP and Frontpage 2000

Hi to all
Required calculation for bank interest chargeable on working capital loan
based on outstanding on daily basis.
For example:
Day 1.Deposits 10,000:withdrawls 15000 ;closing balance -5000.
Day 2.Deposits 35000:Withdrawls 20000 :closing balance 10000
Day 3.Deposits 11000:withdrawls 55000 :closing balance -34000

Assuming sancioned loan is 100000,Rate of interest is 12% per annum.Interest
is payable on amount in debit.

Thnak you

Regards

tiya

Hello,

This is really bugging me.

I am trying to find a way to calculated percentiles based on values of trip distances and the number of observations of those trips occurring. I have attached an example of a small ample of data I would like to do this calculation for.

I am guessing there could be two solutions I do not know either though. The first one would be to do some sort of reverse summarize on the data, and the second one would be to perform the calculation directly. I would prefer to perform the calculations directly as it removes a step next time I need to do this but would appreciate any solution that doesn't involve manual entry.

Thanks in advance.

I wish all the members a happy and prosperous new year

I am 54 years. Started working on computers since 1990 or so starting
with Word Star and Lotus. Switched over to MS Office from 1997
converting or using Lotus files from Excel. Now I am extensively using
Excel for all my office work Salaries, Income Tax, Provident Fund,
Monthly and Annual Accounting. I am better in using all the Menus and
take their benefits including use of functions. I am able to record
simple macros for the repetitive tasks, assign them to self-created
tool bar, menus, sub-menus (controls) and key board letters with Ctrl+
keys but cannot write macros myself as I do not have the knowledge of
VB. I want to learn more abouot VB and programming in Excel. Can I
learn on-line easy-way without academically learning through big books.
Please suggest easy way to learn VB and preparing macros which would
easy my office work and I can put all my work in a programme/software
style.

Thanks

Gandhi

--
gandhi318Posted from - http://www.officehelp.in

First time poster, hopefully my problem is clear.

We use a spreadsheet to log all transactions at our front desk. I've made a column that automatically calculates the time when a transaction is input into a row. The formula is as follows:

=IF(D5="","",IF(A5="",NOW(),A5))

This morning that formula stopped working on the computer at the front desk, returning a message about a circular reference. I can open that same spreadsheet on my computer as well as others in the office and it calculates fine, but of course on the one computer on which I need it to work, it won't ;P

All computers are using Excel 97. Presumably a setting has changed on the front desk computer that's affecting this calculation. Any ideas how I can fix this?

Thanks in advance for any help!

I have an IRR calculation that is computed from several rows of cash flows. The # of rows corresponds to the number of years the analysis is for. The number of years can be edited by the user. Thus, the number of rows to calculate needs to change also. Right now that is static at 10 rows, since I do not know how to program this into the sheet.

Year one will always be the sum of two cells. The middle rows will allways be the same number pulled from one cell. The last year will be the sum of two cells. I can not just leave a zero in blank rows, or it will mess up the calculation.

How can I have Excel adjust the number of rows based on the input by the user? I have attached the sheet

Also, a little side note - If I have a cell that is a number and I want that number to appear in the text of another cell ie. user inputs 10 in the cell B9, and another cell says "Rate of return for {insert number from cell B9} years"

Thanks
Craig

I'm trying to evaluate the performance of a couple of stock portfolios
and I need to calculate Sharpe,Treynor Ratios, Information Ratio and
also Jensen Measure.
I have a monthly closing prices for all the stocks in a portfolio for
48 months.
My problem is that i'm not sure which rate of return to use in the
ratios..i figured that the annualized rate of return for the 48 months
would be better than just average annual rate of return. How should I
compute the annualized rate of return from the data i have?
I found an article with an example (of what i think I should do) of
calculation of annualized return with the mothly data at
http://www.russell.com/ca/Investor_S..._of_Return.asp
But I still don't have a formula and I'm not really sure whether it's a
right way.
Also when I calculate those compound indicators(Sharpe) do I have to
use annualized standard deviaton or is it ok to use just a standard
deviaton of the monthly returns?
Too many questions I know, but I'm kinda lost.
I guess I might as well stick with the average annual rate which would
make things less complicated
Thanx for your help

Excel does not appear to provide a way to use architectural measurements as
numerical values. This greatly limits the users ability to perform
calculations using dimensions from architectural drawings. While Excel will
allow a user to format a number as xx'-yy.yy", a true architectural
measurement would use fractions. For example, 10'-2 1/2" should be
manipulate-able as a number (add, multiply, etc.).

Engineers and architects who use the imperial measurement system spend much
time working around this issue. Refer to the discussion at
http://www.eng-tips.com/viewthread.c...=161789&page=1 for more information
and to see how much energy is wasted on this issue. Engineering and
architectural design professionals in countries that do not use the Metric
system eagerly await Microsoft to improve the functionality of Excel in this
regard.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

JUST TO LET YOU KNOW THAT I HAVE 17 SPREADSHEET FILES AVAILABLE FOR
YOU TO DOWNLOAD FREE TO YOUR COMPUTER! THESE INCLUDE A COMPOUND
INTEREST CALCULATING SHEET (NO NEED FOR TABLES OF THESE NOW!),
FRACTION TO LOWEST TERMS SHEET, INTERPOLATING AND EXTRAPOLATING SHEET
AS WELL AS MANY DAYS/DATES CALCULATING SHEETS (ALSO RETIREMENT DATE
CALCULATING SHEETS) PLUS DATE AND/OR TIME CALCULATION SHEETS AND "DAY
OF THE WEEK" RETURNING SHEETS. ALL THE DATE RELATED CALCULATION
SHEETS ARE OFFERED TO YOU AS EITHER A UK DATE FORMAT SHEET OR A US
ONE. THE 17 URLs FROM WHERE YOU CAN DOWNLOAD EACH RESPECTIVE
CALCULATION SHEET A

http://uploads.savefile.com/redir/80049.xls
http://uploads.savefile.com/redir/80059.xls
http://uploads.savefile.com/redir/80060.xls
http://uploads.savefile.com/redir/80061.xls
http://uploads.savefile.com/redir/80062.xls
http://uploads.savefile.com/redir/80063.xls
http://uploads.savefile.com/redir/80064.xls
http://uploads.savefile.com/redir/80065.xls
http://uploads.savefile.com/redir/80067.xls
http://uploads.savefile.com/redir/80068.xls
http://uploads.savefile.com/redir/80069.xls
http://uploads.savefile.com/redir/80070.xls
http://uploads.savefile.com/redir/80071.xls
http://uploads.savefile.com/redir/80072.xls
http://uploads.savefile.com/redir/80073.xls
http://uploads.savefile.com/redir/80074.xls
http://uploads.savefile.com/redir/80076.xls

P.S. THE 80061.XLS AND 80062.XLS URLs CORRESPOND TO MY AGE CALCULATING
SPREADSHEETS. REMEMBER TO CLICK "ENABLE MACROS" ON THE BOX WHICH
APPEARS WHEN YOU OPEN EITHER OF THESE SHEETS ON YOUR COMPUTER. THESE
WILL CALCULATE ANY TYPE (YES ANY TYPE!) OF AGE (NO: DAYS BETWEEN "AGE
TYPES" ARE CALCULATED, SO THE USER CAN "TAILOR" THE CALCULATIONS GIVEN
BY THE SHEET TO ANY AGE TYPE THAT THEY LIKE - I HAVE SEEN SOME PAST
DEBATE THREADS ON USER GROUPS ABOUT THIS - LEAP YEARS AND THE LIKE!).

THE 17 SHEETS COVER ALL THE FUNDAMENTAL AGE, DATE AND DAY CALCULATIONS
FOR PENSIONS AND LIFE INSURANCE/ASSURANCE FUNDAMENTAL CALCULATIONS
PLUS MORE (E.G. THE GENERAL DATE AND TIME CALCULATING SHEETS). THE
SHEETS ARE ALL THE "RICHARD MARYTREE" RANGE - A FREE "TOOLSET" OF
CALCULATING SHEETS FOR YOU TO DOWNLOAD FREE. NJOY! RICHARD

JUST TO LET YOU KNOW THAT I HAVE 17 SPREADSHEET FILES AVAILABLE FOR
YOU TO DOWNLOAD FREE TO YOUR COMPUTER! THESE INCLUDE A COMPOUND
INTEREST CALCULATING SHEET (NO NEED FOR TABLES OF THESE NOW!),
FRACTION TO LOWEST TERMS SHEET, INTERPOLATING AND EXTRAPOLATING SHEET
AS WELL AS MANY DAYS/DATES CALCULATING SHEETS (ALSO RETIREMENT DATE
CALCULATING SHEETS) PLUS DATE AND/OR TIME CALCULATION SHEETS AND "DAY
OF THE WEEK" RETURNING SHEETS. ALL THE DATE RELATED CALCULATION
SHEETS ARE OFFERED TO YOU AS EITHER A UK DATE FORMAT SHEET OR A US
ONE. THE 17 URLs FROM WHERE YOU CAN DOWNLOAD EACH RESPECTIVE
CALCULATION SHEET ARE:

http://uploads.savefile.com/redir/80049.xls
http://uploads.savefile.com/redir/80059.xls
http://uploads.savefile.com/redir/80060.xls
http://uploads.savefile.com/redir/80061.xls
http://uploads.savefile.com/redir/80062.xls
http://uploads.savefile.com/redir/80063.xls
http://uploads.savefile.com/redir/80064.xls
http://uploads.savefile.com/redir/80065.xls
http://uploads.savefile.com/redir/80067.xls
http://uploads.savefile.com/redir/80068.xls
http://uploads.savefile.com/redir/80069.xls
http://uploads.savefile.com/redir/80070.xls
http://uploads.savefile.com/redir/80071.xls
http://uploads.savefile.com/redir/80072.xls
http://uploads.savefile.com/redir/80073.xls
http://uploads.savefile.com/redir/80074.xls
http://uploads.savefile.com/redir/80076.xls

P.S. THE 80061.XLS AND 80062.XLS URLs CORRESPOND TO MY AGE CALCULATING
SPREADSHEETS. REMEMBER TO CLICK "ENABLE MACROS" ON THE BOX WHICH
APPEARS WHEN YOU OPEN EITHER OF THESE SHEETS ON YOUR COMPUTER. THESE
WILL CALCULATE ANY TYPE (YES ANY TYPE!) OF AGE (NO: DAYS BETWEEN "AGE
TYPES" ARE CALCULATED, SO THE USER CAN "TAILOR" THE CALCULATIONS GIVEN
BY THE SHEET TO ANY AGE TYPE THAT THEY LIKE - I HAVE SEEN SOME PAST
DEBATE THREADS ON USER GROUPS ABOUT THIS - LEAP YEARS AND THE LIKE!).

THE 17 SHEETS COVER ALL THE FUNDAMENTAL AGE, DATE AND DAY CALCULATIONS
FOR PENSIONS AND LIFE INSURANCE/ASSURANCE FUNDAMENTAL CALCULATIONS
PLUS MORE (E.G. THE GENERAL DATE AND TIME CALCULATING SHEETS). THE
SHEETS ARE ALL THE "RICHARD MARYTREE" RANGE - A "TOOLSET" OF
CALCULATION SHEETS FOR YOU TO DOWNLOAD! NJOY! RICHARD

Excel does not appear to provide a way to use architectural measurements as
numerical values. This greatly limits the users ability to perform
calculations using dimensions from architectural drawings. While Excel will
allow a user to format a number as xx'-yy.yy", a true architectural
measurement would use fractions. For example, 10'-2 1/2" should be
manipulate-able as a number (add, multiply, etc.).

Engineers and architects who use the imperial measurement system spend much
time working around this issue. Refer to the discussion at
http://www.eng-tips.com/viewthread.c...=161789&page=1 for more information
and to see how much energy is wasted on this issue. Engineering and
architectural design professionals in countries that do not use the Metric
system eagerly await Microsoft to improve the functionality of Excel in this
regard.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Dear Admin/Moderators/Seniors

I need help for salary calculation also how to get inforamtion from sheet1 to sheet2 if i enter in sheet1 (Attendance Register) it should automatically comes in sheet2 (salary Calcuation).

Also i want to select month in attendance register if i change the month the days and dates should comes automatically and if sunday it should hilighted in red colour.

Please find attached file and go through the formulas and calculate same like that.

VBA password is : Carrot

Pls help me.

Thanks in advance.

Regards,
Logu

I'm niot even certain how to describe this problem. (novice)
I want to create a spreadsheet that allows me to compare rates of office leases from advertisements over time. Sometimes the total monthly rent is provided like $1500, and sometimes the sq. ft. rate is provided. ($15.00/ sq ft.) which is an annual rate for the space. The size of the office is always provided so I want.

My Question is, Is there a way to create a cell that calculates the sq. ft. rate or the monthly rate, if one is not provided, but will allow you to insert the information if it is provided?

Example Ad would be:

For lease Office: 1100 sq. ft. $1300 a month (need the sq. ft. rate) which is ($1300 x 12 / 1100 = $14.18)

or

1100 sq. ft. Office for Least: $14.18 sq. ft. (need monthly rate) which is 1100 x $14.18 / 12 = $1300mo.)

I'm trying to evaluate the performance of a couple of stock portfolios
and I need to calculate Sharpe,Treynor Ratios, Information Ratio and
also Jensen Measure.
I have a monthly closing prices for all the stocks in a portfolio for
48 months.
My problem is that i'm not sure which rate of return to use in the
ratios..i figured that the annualized rate of return for the 48 months
would be better than just average annual rate of return. How should I
compute the annualized rate of return from the data i have?
I found an article with an example (of what i think I should do) of
calculation of annualized return with the mothly data at
http://www.russell.com/ca/Investor_S..._of_Return.asp
But I still don't have a formula and I'm not really sure whether it's a
right way.
Also when I calculate those compound indicators(Sharpe) do I have to
use annualized standard deviaton or is it ok to use just a standard
deviaton of the monthly returns?
Too many questions I know, but I'm kinda lost.
I guess I might as well stick with the average annual rate which would
make things less complicated
Thanx for your help

We are putting loaned funds in escrow to pay the first five years of a loan, and this $ will earn interest (bank will only provide entire loan payout insted of providing funds ad hoc). We want to end with close to or $0 in escrow. What I need is to automatically calculate how much money we need in escrow to cover 60 payments with the escrow balance earning interest. Let's say our payment is fixed at $1,000, and the interest is fixed at 6.25% compouned monthly after the payment. I can manually calculate that I need to start with $51,683.63 to pay off the $60K in payments, but I need to be able to constantly manipulate these numbers. Should I be using FV or PV for this, something else, or is there no special formula for this type of calculation?

Me=Dummy and don't have any computer languages on my computer.

I have a CSV file (call it before.csv) with the first row as a header for column A through M inclusive. I have a large number of rows that vary from say 500 to 2000.

Column L (second to last) is an integer.

Often, I have to open the CSV file, create a formula off in another column (N O P - depending where the cursor lands) and take the value is Column N and divide by a number X where X is 1<=X<0. Let's say X varies by my mood - meaning it is not always set. Today it may be 0.95, tomorrow maybe 0.92, the next maybe 0.83.

I then create the simple function, copy it, and paste the function down the column to the last row with a number (all 500 to 2000 or so).

I then copy that whole column that is now column L / X (a real number) and paste value into column L replacing the previous integer with the new value. I then delete the temporary caluclation column then save as another file name (call it after.csv) - leaving before.csv as it was. after.csv will already be a file so it will need to overwrite the file.

The new number than is in column L does not need to be an integer, but a roundup to the next highest would be acceptable if needed.

How do I make this a lot easier? How can I automate it? How can I get it to run on a regular schedule without my intervention.

Ideally I have my before.csv and after.csv sitting there - every XX minutes, a process runs, opens before.csv, does the calculation, saves the new file and overwrites as after.csv - if it is fully automated like that, I need a place to enter and store (and be able to change) my X factor 1<=X<0 - if it is more of a manual process, I need to be prompted for X each time or again have a place to store it and change it.

Ultimately it would be nice to have a fully automatic deal and the capability to make it operate manually as well.

I have a program that generate before.csv every 30 minutes and that file gets uploaded automatically to a few places around the internet every 10-60 minutes - one place the file gets uploaded to needs to have that value in column L altered to fit certain specification that I set. So I have before.csv automatically uploaded to say 5 places and after.csv uploaded to 1 place.

I also need to be cautious of causing problems of opening and changing these files as they are being uploaded.

Currently the file is about 150kb.

Thanks for any advice on what to do whether it be within Excel or pointed into a direction of someone who can write some code for me for a fee.

Hello All,
A few weeks ago The Dude helped me out with a macro that worked perfectly. However, now that I have increased the number of values it reads it is incredibly slow and frequently my computer locks up before it makes much progress (emachines laptop). The macro takes values from several spreadsheets in a file, then makes one summary spreadsheet of four columns and ~70 rows. The first and second column contain the date and the store number, the second and third column contain the item ordered and the number of items ordered respecitively.

Is it possible that the macro is too big? Do I have a crazy loop in there somewhere? Any help would be greatly appreciated. Anyone care to take a look or maybe even run it on their computer?
Here it is!
Alan

Sub addHyperLinkToFiles()
' Declare variables
Dim a(999999), i
Dim Msg, style, Title
Dim myDrive, myLocation, LoadDir As String
Dim counter, loopLimit
' Initialize variables...
' *** IMPORTANT *** Replace the drive name "C" below w/ your reference
myDrive = "C"
' *** IMPORTANT *** Replace the file path below w/ your reference
myLocation = "Documents and SettingsallanDesktopCKORDERS"

' Turn off screen updating (program runs faster)...
Application.ScreenUpdating = False

' Notify user of progress...
oldStatusBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.StatusBar = "Searching for files; please wait..."
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

'Set the filepath to the correct directory...
ChDrive myDrive
ChDir myDrive & ":" & myLocation

' Initialize the array counter...
i = 0
' Load array with names of Excel files in correct directory...
a(i) = Dir("*.xls")
' If no Excel files are present, alert user & exit program...
If a(i) = "" Then
GoTo FilesMissing ' the error handler...
Exit Sub
Else
' Loop through the Excel files to count files as loop limit...
Do
i = i + 1
a(i) = Dir()
Loop Until a(i) = ""
' Count the number of names in the array...
fileCount = CStr(i)
' Notify user of number of files to be protected...
Application.StatusBar = "Number of files to process: " & fileCount & " - please wait..."
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
' Start looping through the Excel file(s) to open & process each one...
For MyFilCount = 0 To (fileCount - 1)
LoadDir = CurDir & ""
Workbooks.Open LoadDir & (a(MyFilCount)), UpdateLinks:=0, _
ReadOnly:=False, IgnoreReadOnlyRecommended:=True
' Provide file processing status to user ...
Application.StatusBar = _
"Processing file " & MyFilCount + 1 & " of " & fileCount & ": " & a(MyFilCount) & "; please wait..."
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

' Add filename hyperlink & related data...
Filename = ActiveWorkbook.Name
Worksheets("DAILY SANDWICH ORDER FORM").Activate
val1 = ActiveSheet.Range("e3").Value
val2 = ActiveSheet.Range("y35").Value
val3 = ActiveSheet.Range("y36").Value
val4 = ActiveSheet.Range("y37").Value
...and this pattern repeats until...

val129 = ActiveSheet.Range("z92").Value
val130 = ActiveSheet.Range("z93").Value
val131 = ActiveSheet.Range("z94").Value
val132 = ActiveSheet.Range("z95").Value
val133 = ActiveSheet.Range("z96").Value
val134 = ActiveSheet.Range("z97").Value
val135 = ActiveSheet.Range("z98").Value
val136 = ActiveSheet.Range("z99").Value
val137 = ActiveSheet.Range("z100").Value
val138 = ActiveSheet.Range("z101").Value
val139 = ActiveSheet.Range("z102").Value
val140 = ActiveSheet.Range("z103").Value
val141 = ActiveSheet.Range("ag9").Value
ActiveWorkbook.Close SaveChanges:=False
ActiveSheet.Range("A3").Select
Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Value = Filename
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=LoadDir & (a(MyFilCount)) _
, TextToDisplay:=Filename
ActiveCell.Offset(1, 0).Value = val141
ActiveCell.Offset(2, 0).Value = val141
ActiveCell.Offset(3, 0).Value = val141

...and this pattern repeats until...

ActiveCell.Offset(67, 0).Value = val141
ActiveCell.Offset(68, 0).Value = val141
ActiveCell.Offset(69, 0).Value = val141
ActiveCell.Offset(1, 1).Value = val1
ActiveCell.Offset(2, 1).Value = val1
ActiveCell.Offset(3, 1).Value = val1

...and this pattern repeats until...

ActiveCell.Offset(65, 1).Value = val1
ActiveCell.Offset(66, 1).Value = val1
ActiveCell.Offset(67, 1).Value = val1
ActiveCell.Offset(68, 1).Value = val1
ActiveCell.Offset(69, 1).Value = val1

ActiveCell.Offset(1, 2).Value = val2
ActiveCell.Offset(2, 2).Value = val3
ActiveCell.Offset(3, 2).Value = val4
ActiveCell.Offset(4, 2).Value = val5

...and this pattern repeats until...

ActiveCell.Offset(65, 2).Value = val66
ActiveCell.Offset(66, 2).Value = val67
ActiveCell.Offset(67, 2).Value = val68
ActiveCell.Offset(68, 2).Value = val69
ActiveCell.Offset(69, 2).Value = val70

...and this pattern repeats until...

ActiveCell.Offset(67, 3).Value = val138
ActiveCell.Offset(68, 3).Value = val139
ActiveCell.Offset(69, 3).Value = val140
Next MyFilCount
' Reset screen updating and status bar...
Application.ScreenUpdating = True
Application.StatusBar = False
Application.DisplayStatusBar = oldStatusBar
' Define user dialog parameters
Msg = "File processing is now complete."
style = vbOKOnly + vbInformation + vbDefaultButton1
Title = "File Processing Status"
' Display user dialog
Response = MsgBox(Msg, style, Title)
End If
Exit Sub
'Error handler if no file(s) exist in directory...
FilesMissing:
' Define user dialog parameters
Msg = "There are no files located in the " & myDrive & ":" & myLocation & " directory." & Chr(13) & _
"The program stopped and no updates were made."
style = vbOKOnly + vbCritical + vbDefaultButton1
Title = "Missing File(s)"
' Display user dialog
Response = MsgBox(Msg, style, Title)
' Reset screen updating and status bar...
Application.ScreenUpdating = True
Application.StatusBar = False
Application.DisplayStatusBar = oldStatusBar
End Sub

I want the cell to do a calculation based on the contents of the preceeding
cell. If the preceeding cell contains R, I want it to complete one
calculation, but if the cell contains something different, I want it to
complete a different calculation.

Can anyone provide a suggestion for this?

Thank you.

Say I have a worksheet with the following information:

Name Position Salary
John Producer $10,000
Jeremy Producer $98,000
Jaime Producer $50,000
Darren Artist $67,000
Chris Artist $75,000
Clint Artist $30,000
Adam Artist $57,000

In Cell C2 (salary for John) I want to create a formula that looks for
all rows that contain the same position as in B2 (John's position which
is producer) and then calculates the average of all the salaries in
column C of those rows that have the position producer. Then I want to
compare that calculated average salary for all rows that have the
position producer to the value in cell C2. I am using cell C2 as an
example but I would like to do the same calculation for all salary
rows. Basically I want to use conditional formatting to show if the
average salary for that persons position is below, around, or above
that persons salary. So once I create the conditional formatting formula
I can copy it to all the cells that have salary.

Can the above be done without using VBA or is VBA the only way to do
it? If VBA is the only way to do it can someone point me in the rough
direction of how I would go about it?

Thank in advance for any help provided.

- John

---
avast! Antivirus: Outbound message clean.
Virus Database (VPS): 0612-4, 03/25/2006
Tested on: 3/25/2006 12:13:46 PM
avast! - copyright (c) 1988-2005 ALWIL Software.
http://www.avast.com

JUST TO LET YOU KNOW THAT I HAVE 17 SPREADSHEET FILES AVAILABLE FOR
YOU TO DOWNLOAD FREE TO YOUR COMPUTER! THESE INCLUDE A COMPOUND
INTEREST CALCULATING SHEET (NO NEED FOR TABLES OF THESE NOW!),
FRACTION TO LOWEST TERMS SHEET, INTERPOLATING AND EXTRAPOLATING SHEET
AS WELL AS MANY DAYS/DATES CALCULATING SHEETS (ALSO RETIREMENT DATE
CALCULATING SHEETS) PLUS DATE AND/OR TIME CALCULATION SHEETS AND "DAY
OF THE WEEK" RETURNING SHEETS. ALL THE DATE RELATED CALCULATION
SHEETS ARE OFFERED TO YOU AS EITHER A UK DATE FORMAT SHEET OR A US
ONE. THE 17 URLs FROM WHERE YOU CAN DOWNLOAD EACH RESPECTIVE
CALCULATION SHEET ARE:

http://uploads.savefile.com/redir/80049.xls
http://uploads.savefile.com/redir/80059.xls
http://uploads.savefile.com/redir/80060.xls
http://uploads.savefile.com/redir/80061.xls
http://uploads.savefile.com/redir/80062.xls
http://uploads.savefile.com/redir/80063.xls
http://uploads.savefile.com/redir/80064.xls
http://uploads.savefile.com/redir/80065.xls
http://uploads.savefile.com/redir/80067.xls
http://uploads.savefile.com/redir/80068.xls
http://uploads.savefile.com/redir/80069.xls
http://uploads.savefile.com/redir/80070.xls
http://uploads.savefile.com/redir/80071.xls
http://uploads.savefile.com/redir/80072.xls
http://uploads.savefile.com/redir/80073.xls
http://uploads.savefile.com/redir/80074.xls
http://uploads.savefile.com/redir/80076.xls

P.S. THE 80061.XLS AND 80062.XLS URLs CORRESPOND TO MY AGE CALCULATING
SPREADSHEETS. REMEMBER TO CLICK "ENABLE MACROS" ON THE BOX WHICH
APPEARS WHEN YOU OPEN EITHER OF THESE SHEETS ON YOUR COMPUTER. THESE
WILL CALCULATE ANY TYPE (YES ANY TYPE!) OF AGE (NO: DAYS BETWEEN "AGE
TYPES" ARE CALCULATED, SO THE USER CAN "TAILOR" THE CALCULATIONS GIVEN
BY THE SHEET TO ANY AGE TYPE THAT THEY LIKE - I HAVE SEEN SOME PAST
DEBATE THREADS ON USER GROUPS ABOUT THIS - LEAP YEARS AND THE LIKE!).

THE 17 SHEETS COVER ALL THE FUNDAMENTAL AGE, DATE AND DAY CALCULATIONS
FOR PENSIONS AND LIFE INSURANCE/ASSURANCE FUNDAMENTAL CALCULATIONS
PLUS MORE (E.G. THE GENERAL DATE AND TIME CALCULATING SHEETS). THE
SHEETS ARE ALL THE "RICHARD MARYTREE" RANGE - A FREE "TOOLSET" OF
CALCULATING SHEETS FOR YOU TO DOWNLOAD FREE. NJOY! RICHARD

Hi,

I'm a graduate student in Ecology, and I have a large data set with a year column, a day-of-year (as in Jan 1 = 1, Dec 31 = 365), a trap site column (e.g. trap 2a, 4g, etc. up to 7h), and a value ("Pr") for the number of spiders caught in each trap divided by the number of days that the traps were out (a measure of the total caught standardized by trapping effort). What I need to figure out is how to write a logical function to create a new column that will, for each trap, provide the average and standard deviation of the OTHER traps collected that day. For example: if there are three traps, 2a, 2b, and 2c, collected 1999, on day 211, in the 2a row would be the average value and SD of 2b and 2c, in the 2b row, of 2a and 2c, and in the 2c row, of 2a and 2b. So the function has to pick out same year and day, but different trap site, then average them. Any ideas? I'm attaching a very abbreviated and simplified version of my sheet as well (with the new values in two separate columns that I would want to be able to calculate for a 5000+ row sheet), in case that helps. Thanks! Also, I have excel 2008 for Mac 12.3.1

Best,
Mike


No luck finding an answer? You could always try Google.