# annual

1. ### INDEX and MATCH Function

I am trying to use the INDEX and MATCH function to look up a date within a range to bring back a annual salary amount. Here is the LOOKUP table which is on a sperate tab in the workbook call Pay_Scales: Annual Salary Start Date End Date 46395 2018-12-17 2019-05-02 47789...
2. ### Search for date in Range for each Employee VBA

I have 850 employees and I need to calculate an estimated costs for them. I need to be able to loop through the employees, for each employee, look up their annual salary based on date ranges and Group and level. Here is an example of the pay table (simplified)...
3. ### Annual Leave Database - A-Z Sorting, Repeated Lists and User Access

Hi all I have been asked to make a chart for staff in an office. Currently there are approximately 111 members of staff, however this is likely to increase in the coming months. I have prepared a workbook which has sheets for 2019 and 2020. Each of these sheets has the days of the year across...
4. ### IF AND OR Formula

Hello I am trying to complete a formula for the following I want to check that if the value in NMI MIRN State says VIC OR NSW OR QLD then the value in Annual Peak KWh needs to be greater than 100000 or IF NMI MIRN State says NSW or SA then the value in Annual Peak KWh needs to be greater...
5. ### Bonus Calculation - Pro Rata

Dear Mr. Excel, Hope this mail finds you in good health. I have a question, and would appreciate it if you could answer it ASAP. Ex: I have a bonus pool of 150,000 to be distributed among 5 employees on criteria of grade, department, annual evaluation and their date of joining (pro rata)...
6. ### Holiday calculator need help - time sensitive

I'm creating a holiday tracker that has certain requirements and I'm stuck. I feel like the solution is something simple but I'm giving up and asking for help. I'll do my best to explain... Holidays are accrued at the rates you see in right most side of the table based on length of service...
7. ### How can I find out a annual increasing PMT for a value

I have a future value of 2307936 (after 5 years) and need to make an investment every month, with an annual increase of 10%. The rate of return on investment is 9% per annum. If there is no annual increase of 10% I am getting 30882 as PMT. How do I calculate with 10% annual increase.
8. ### Formula for Annual Leave

Hello, I am working somewhere that i am entitled to 2 days off per week whenever i am taking 1 day off per week 1 day needs to be added to my annual leave whenever I am taking 3 Days off per week 1 Day needs to be deducted so i am looking for a formula that will count in an array of 7 cells...
9. ### Multiple Nested IF functions

Attempting to calculate the available annual man hours per production worker based on their years of service. Their annual leave both vacation and sick are based on their years on board. Knowing this I can then calculate what their true production man-hours per year are. Having trouble...
10. ### VBA to copy columns to new sheet with new order and two or more criteria

Hello, everyone This is my first post here and I have a question I apologize in advance for my bad English My girlfriend has a home for elderly people and I want to simplify her work I have an Excel workbook in which I have made several documents and two lists of people who are housed in...
11. ### Reformatting or consolidating multiple worksheets for summary

Hi All I have a workbook where each tab represents a country. Within that, it shows the same functions and items per client. I need to summarise the data across all sheets to extract key pieces of information without having to reformat the sheets. I inherited the workbook so don't want to...
12. ### Calculating tax based on slabs

Dear all, Need some guidance calculating taxes based on fixed slabs. Ive attempted creating a table with the three columns below and tried to work out a logic based on VLOOKUP but am unable to get the formula setup right. I have salaries at varying levels and I am trying to create a workbook...
13. ### Calculating 0.5 leave days

Hi, I have created an annual leave calendar with drop down lists for each day making it possible to select the type of leave (annual leave (1day) and annual half day leave (0.5day), in a table below I have a summary where the annual leave for each month is added up, providing an annual total at...
14. ### Indirect Formula: reference to two cells plus space?

Hello, First of all, my apologies if this has been already answered in the past... My problem: I have some sheets' names obtained by combining strings from two columns in a different sheet, separated by a space. So, based on the below example, my tabs' names could be: A Monthly D Monthly B...
15. ### Date of annual increment

Is there any excel formula to find out annual increment date? Example: If A1= 01/05/2017 as appointment date, B1= 31/04/2018 as annual increment date, If A1=25/07/2017, B2=30/06/2018....etc.
16. ### VLOOKUP in a Range of Dates?

I don't even know if a macro can help me with this. I have a listing of employees showing their annual pay rates by a range of dates. I need to be able to look up a date within those ranges. For example: Employee # Start Date End Date Annual Salary Employee 123...
17. ### Formula to calculate market rent rates

Hello I have a work book with two tabs. 1. Contractual Rent Steps - This shows the annual cost per square foot. 2. Market Rent Steps - This will use the annual market cost per square foot with a 3% increase when the tenants Contractual annual rent step increases. What I am trying to do is...
18. ### Working backwards to year 1 rent with a fixed total

Hello brainy people, In negotiations to rent a new building, I know our budget is say \$3,000,000 over 5 years and also that the Landlord will be looking for some annual increments. I'm really struggling to put in a formula to calculate year 1 rent which allows me to experiment with various...
19. ### VBA to transpose dataset and add a piece of data

Need to write vba code to sort of transpose from a horizontal data set to a vertical data set with an extra piece of data created. If the table 1 has no data for mon -> sun just skip Is there an easy way.. I just seem to be way our of my depth here!! This data set has like 10,000 centres, so...
20. ### Copy 2 columns of data from a text file using criteria for listbox

Hi all I have had a try with this and it's beaten me too. i'm trying to display 2 columns from a text file. Row 1 are headings and the environ\$"username" will be the column name I wish to use and contains annual leave type data going down. And i would like to display column 1 which will have...

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back