years

  1. N

    SUMIF COUNTIF AVERAGE problem formula...

    Hi all, hoping that somebody can help with this formula please... I have a list of contractors, all with multiple lines for queries (for different pieces of work), with the number of days they take to respond to each query and the fiscal year that the query falls into. So I have the following...
  2. J

    Dynamic Date Column Headers

    I'm trying to build a financial report that automatically pulls in data based on a date range...but the date range can change. For simplicity, let's just use 'years'. Here's what I have: A1: 2019 (this is the starting year) A2: 5 (this is the number of incremental years I'd like to view)...
  3. C

    distinct count in pivot table

    How does distinct count work in pivot table? If I as summing distinct count of a customer and I showing three years of data for example If customer A bought stuff in all three years will he show three times or will he show in first occurence in data or will he show in first year of data...
  4. Smokeyham

    Show 5 Year Increments, based on Hire Date

    Hello, Our staff receive a recognition for every five years of service. I have been asked to add some columns to a spreadsheet that will show (1) the next year when the person will receive a 5-year recognition and (2) when that year does occur what interval that will be (i.e. a person hired...
  5. N

    Formulas all changed to relative cell references - why and how?

    I've been using a spreadsheet I created 5 years ago, which has grown and evolved the entire time. I use long and complicated formulas, many of which make heavy use of arrays, using INDEX and MATCH. I had to look at one of my formulas today, and noticed that NONE of my formulas are as they were...
  6. J

    Change Calender starting month from January to April

    Hi Guys, The sheet shown below tracks spend by calendar years and we need to change it to spend by tax/fiscal years April to March. Does anyone know how to do this ? Thanks <tbody> Supplier 1 MONTH Apr-17 £59.00 May-17 £60.00...
  7. S

    2% Salary Increase for the first 4 years then 1.5 for the last

    Good morning, I am trying to figure out a shorter formula for figuring out what a 2% increase a year would be for the first 4 years and then a 1.5% increase for the final year. I have a value in A1 i.e. 73688 in C1 I have a value from 1 to 5 telling me what the salary increase would be for that...
  8. F

    extract year value and rearrange

    Hallo! i have a column with dates (eg 1/1/2019) and want to extract the years, and put them in a row from oldest to newest. eg. i have in a column these dates: 1/1/2017 1/3/2017 1/1/2018 2/1/2018 1/1/2019 5/1/2019 and want to extract the years in 3 consecutive cells in a row from oldest to...
  9. K

    Solving For IRR Based On Irregular Cash Flows

    Hi guys, I'm trying to solve for <abbr title="internal rate of return" style="box-sizing: border-box; border-bottom: none transparent; cursor: pointer; text-decoration-line: none; text-decoration-style: initial;">IRR</abbr> (let's say 20%) based irregular cash flows - essentially trying to find...
  10. K

    Font Color

    Every year when I do my taxes, I take last years excel tax work sheet and copy it. Then I take all dollar values and change the font to red. This way I know they are last year values that havent yet been updated. When I type in the current values for this year, I want the font to be black...
  11. S

    Need to create a "heat-mapped" calendar based on previous years data for planned OOO.

    Hi everyone, I'm trying to create a color coordinated (heat mapped) calendar based on 3 years of previous data which will help my team members choose the "less intensive" days to take their OOO. I have my data scrubbed into days, months, and years for all 3 years but I don't know how to create...
  12. H

    Pivot Table - Filter by Year-Quarter

    Hi, I am currently setting up a dashboard for my company. I have a series of pivot charts that I want to be able to adjust using filters. I need to be able to adjust the date quarters that are being shown in each chart. The version of Excel I am using already groups the dates in my table into...
  13. T

    Multiple criteria formula

    I need to write a formula that will look at someone's hire date (C3) and if that is before 09/01/07 then it will calculate number of years they will have been here I have the year end date in C1. Based on number of years it takes salary (J3) and multiplies by % the years are broke down with the...
  14. S

    year of service

    i am using year of service formula as below but if the date is blank it is catching 119 instead of zero i want to do zero service =DATEDIF(A2,TODAY(),"y")&" Years "&DATEDIF(A2,TODAY(),"ym")&" Months "&DATEDIF(A2,TODAY(),"md")&" Days" <colgroup><col><col span="2"></colgroup><tbody> 21-Jun-97...
  15. L

    if function

    Hello- i have an excess inventory on hand which i need to figure out which part to keep and which part to destroy. my goal is to get a formula that will give me the desired output for the column in red font (destroy the rest). I have done a few scenarios to hopefully explain this better. As...
  16. L

    Excel-If And Function

    Hello, I would like a function that would output the years on hand by group max column based on the estimated years on hand max column. I have been able to complete the function by doing If, And statement, however it is a long formula. Is there a shorter way to complete with a range...
  17. J

    COUNTIF Dates expired. HELP!

    Hey guys, I've been trawling the internet, and it's many excel forums, and am yet to find exactly what I'm looking for! I've been working on a training matrix/schedule and my boss has asked me to come up with a % compliance figure. I really need some help understanding exactly what 'COUNTIF'...
  18. L

    Calculate percentage of client base

    <tbody> Client When became a client How long been a client % ABC 09/03/2017 1 year 10 months DEF 30/04/2013 5 years 9 months GHI 13/11/2007 11 years 2 months JKL 30/08/2018 0 years 5 months </tbody> Hi, I am trying to work out the following: I have a database of...
  19. S

    Spreading a given cost of X$'s over Z years Starting at year Y

    So I am trying to create a formula to equally spread a given cost X starting at year Y and spread over Z amount of years. Ex <tbody> Cost X Start Year Y Spread over Z years 2019 2020 2021 2022 2023 2024 2025 1000 2020 5 0 200 200 200 200 200 0 </tbody>...
  20. C

    Calculate Cumulative interest paid at Month # 15 for Mortgage amortized for 25 years

    Dear all, Is there a way to: Calculate Cumulative interest paid at Month # 15 ("n") for Mortgage amortized for 25 years using ONE line. I want a single formula whereby I can just change "n" and it gives me the cumulative interest up to that point. All other mortgage elements (interest rate...

Some videos you may like

This Week's Hot Topics

Top