1. L

    How do I make one cell always equal to a specific number while still taking into account of other cells.

    I need a formula which allows the 'Money Leftover' cell K7 to always equal 500. I've probably worded the above really vaguely, however I'll add an image to try and get explain myself. What I need: As I start to add in values under columns C-I, the values in K will either go up or down...
  2. A

    Formula to identify changes of less than 20% and more than 20% over time?

    . I have the following data from the S&P 500 index ranging from 1990-present. I need to do the following; identify all periods of market downturns and market upturns market upturn is defined as a period during which there is no downside move in the index greater than 20% and a downturn is...
  3. A

    XNPV function

    Hello All, If somebody help with my my question I will be very thankful. Thank you very much in advance. My question is that when I use XNPV function in payment schedule as attached which I know only the monthly irregular payments and I want to discount all the payments to 1 January with taking...
  4. R

    Create a tree structure in Excel - Possible VBA input

    Hello all, *Second time posting as the last thread did not display correctly* This may be a long shot but I am throwing this out there to see if anyone can help. I would like to recreate a form of a three-structure in Excel. Below is an sample set of folder structure data. Commercial...
  5. B

    Search a Function for X if found do Y

    Hello All - When I copy a Balance Sheet from a website the negative figures map over like this "(100,000" as the other parenthesis pastes to the next cell. I then have my own formatted Balance Sheet elsewhere on the worksheet and am able to use Index(Match) to pull over the initial figures I...
  6. T

    Vlookup from last months report for this months report

    Hi, each month I run a report and complete multiple Vlookups against last months report through VBA. The reports are called the same thing, example - April folder - Finance extract, May folder - Finance extract. In order for my macros to work, I have to each month rename the previous month...
  7. V

    Count needed based on date

    Hi team, like below format, i do have lot number of records. I want formula to calculate unique count, on every date from which department.. can anyone suggest on this.. ---- User count
  8. D

    VBA for Finance structure to Database structure (Copy & Paste)

    Hi everybody, I use this forum as much as I can to look for better ways to use Excel, but I'mn ot that good with VBA and I need ome help for restructuring some finance data into a Database tab. I have 4 tabs, identical formatting except the amounts per Month are different. The 4 tabs represent...
  9. B

    User Defined Function for stock variance

    Hello, I need help in creating a custom function that will calculate variance of a stock using its raw prices. This means that the returns calculation will take place within the function. I have written the code, but its not giving the right answer, could anyone please guide me? Thanks in...
  10. U

    FV Function (Future Value Function)

    Hi, I am having an issue in using FV (Future Value) function in Excel. I am suppose to be comparing four different plans. FV function works fine for three of them. I am hoping someone to have a look at my Excel sheet and let me know where I am making a mistake? Regards
  11. J

    Amortization macro help

    I've found some information online to help our finance department with expense amortization, however rather than having the amortization happen over days within the month (calculating the dailyrate * days in month), I'd simply like for this same functionality to work at a month scale, where the...
  12. O

    Formatting data to use in pivot table

    Hello All, I have this issue have been trying to solve for a while now and cant seem to find any solution. I'm hoping someone here can help me. Here is the issues. I have data that comes to me in this format and it have a few more columns and rows. Dept Users Finance I...
  13. O

    Crosstab, Transform, or Pivot? I'm lost...

    Trying to figure out the best way to turn my rows into columns, while maintaining the values and headers. Looks like a reverse crosstab query or something. I'm an amateur btw... I have about 35 columns that I want to stay the same (keep as columns), then about 50 column that have months...
  14. V

    formula to count data based on Account

    Hi All, Kindly find the attached file in which I need formula to count data based on Account. The attached sheet will self-explain the exact requirement. Your help in this regards will be highly appreciated. <tbody> Desk Location Emp. ID Employee Name Account P4001 #N/A...
  15. M

    formula help - look up on two criteria

    Morning all I was wondering if anyone could help me with a formula. I need to create a lookup using 2 criteria (department in column A and department in row 4. All the departments are listed A5 to A50 and Months B4:L4. So the first one would be Finance is in cell A5 and January is B4 and so...
  16. K

    Macros to build Income statements

    Hello, I have to build monthly Income statements (IS) for 47 projects and one combined (IS) based on Trial Balance (TB) exported from accounting system into Excel. Under each account of the TB there are 48 rows related projects (47 projects and one not assigned to projects, in total 48...
  17. K

    Prevent circular reference in cashflow model

    Hi, I have taken over a cashflow model which is do with land acquistion. The model calculates in a land value before cost of finance. Then you take the cost of finance off however that is worked out on the site value which is being discounted to take account of the cost of finance. The cost...
  18. D

    Run MS Dos Command CMD any copy text from Excel please?

    Hi, I am almost there but struggling with the last part - I have a command line in an Excel workbook (SHEET1 cells A1 to A10) - I would like to have a macro to open cmd (command in MS DOS) and then copy the text in cells A1 to A10 to the command (CMD) line. So far I have Shell "cmd.exe /k...
  19. F

    How to stop/pause macro so "#Requesting Data from the server." can load.

    Hello all, I would like some help with an issue I am having when I am running my macro. I am using Moody's Excel Add In, and as I pull in DataPoints using their API function it requests the data that I need. Although when I run a marco that has these formulas built with their API functions...
  20. A

    Sumif with dynamic range

    Hello, I am struggling with a formula to create a sumif with a dynamic range. The data I have is as follows: <tbody> ROW/COLUMN A B C D E F G H I J K L M N 1 Department Expense Jan-18 Feb-18 Mar-18 Apr-18 May-18 Jun-18 Jul-18 Aug-18 Sep-18 Oct-18 Nov-18 Dec-18 2 Finance Phone 199 90 10 7...

Some videos you may like

This Week's Hot Topics