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

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

- Calculate a salary earned between any two dates
- How to calculate pension contribution on salary?
- Can I make a simple fill-in calculator for webpage using Excel?
- Can I make a simple fill-in calculator for webpage using Excel?
- Calculation for bank interest chargeable
- Percentile calculations for frequency of observations
- Learning VBA online for Excel
- Formula Won't Calculate On Specific Computer
- Vary # of rows for IRR calculation
- Annualized return calculation for the purpose of portfolio performance evaluation
- Provide Excel format for architectural units: ft-inches-fractions
- Calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets)
- CALCULATING WORKSHEETS (INCLUDING AGE CALCULATING SHEETS) FOR DOWNLOADING, GREAT FOR PENSIONS/LIFE INSURANCE CALCULATIONS!
- Provide Excel format for architectural units: ft-inches-fractions
- Salary Calculation get information from sheet1 to sheet2
- It this then that... (I think) calculate for missing info.
- Annualized return calculation for the purpose of portfolio performance evaluation
- Calculating starting balance
- Automatic simple calculation for dummy
- Macro too big for my computer?
- If Function for 2 calculations
- Formula to find average of field for all rows that contain another field
- Calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets)
- Formula for calculating averages of other cells based on multiple criteria

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

Thanks for any help Peter

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

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

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

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

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!

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

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

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

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

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

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

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

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

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.

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

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.

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

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

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.