1. M

    Challenging/Complex named range or Idea

    Hi folks! Can you please help me with the below? I have 2 columns AAA 12/1/2019 AVC 11/2/2019 AAF 11/3/2019 AA 11/4/2019 AAA 11/5/2019 AA 11/6/2019 AA 12/7/2019 AA 11/21/2019 AAF 11/15/2019 AVC 11/11/2019 I want to define named ranges depending on column A value. For...
  2. M

    Can this complex rate-of-return formula be simplified?

    =IF( ROW()-ROW(Table1[#Headers])<12, NA(), (1+INDEX([Monthly RR],ROW()-ROW(Table1[#Headers])))* (1+INDEX([Monthly RR],ROW()-ROW(Table1[#Headers])-1))* (1+INDEX([Monthly RR],ROW()-ROW(Table1[#Headers])-2))* (1+INDEX([Monthly RR],ROW()-ROW(Table1[#Headers])-3))* (1+INDEX([Monthly...
  3. F

    Complex IF formula

    I'm struggling with a complex IF formula, hoping someone can help. In D2 I have a category number 1-4. In F2 I have a number which could be positive or negative. I need a formula in G2 to work out the following conditions: IF F2 is 0, G2 = 0 IF D2 = 1 AND F2 is GREATER than zero MULTIPLY...
  4. H

    Complex lookup

    I am trying to figure out a rather complex lookup. Here is an example set of data. A B C D E F 1 2 3 4 X 5 2 3 What i need to do is the...
  5. K

    VBA code to format (un-pivot) table

    Hi, I am trying to un-pivot this table- <tbody> A1 A1 A1 A2 A2 A2 STORE RECEIPT WEEK Floorset Target Instore Newness Target Instore Newness FY20 100 89 25 200 193 55 </tbody> To look like this- <tbody> Business STORE RECEIPT WEEK Floorset Target Instore Newness A1 FY20 100 89...
  6. D

    what is the best course to learn about excel and vba?

    Is there a Microsoft course on excel or what do people think is the best course if you want to learn how to code in vba and make complex Spreadsheets?
  7. K

    Extracting multiple keywords from a cell

    I am trying to create a formula that will extract the campaign name that generated the lead in the sheet. The campaign name column looks like this- 2016_00_InboundWeb_Request_A_Demo 2016_04_PPC_Google_Generic 2016_00_InboundWeb_InboundCall&Chat 2016_12_Telemarketing_Infocore_Spa-Salon The...
  8. K

    Excel Multiple Keyword Extraction from cell

    I am trying to create a Formula(s) that will extract the campaign name that generated the lead in the sheet. Campaign Name 2016_00_InboundWeb_Request_A_Demo 2016_04_PPC_Google_Generic 2016_09_Hotleads_EngagementPrograms 2016_12_Telemarketing_Infocore_Medical...
  9. V

    Complex Macro

    Hello all, I am looking for some assistance with a complex macro. I need to pull in all data in tabs between 'Start' and 'End' in my workbook into one tab called 'journals'. The trick is, I need to pull in all the data from each tab in columns JK:MX. Then I need to stack the data so tab 1...
  10. J

    Nest NPV function within PMT formula as the argument for "PV" .

    I've been given a particularly diabolical challenge, and for the life of me, I cannot make it work. The challenge is to execute within a single cell a formula that will calculate "EAC: equivalent annual cost" within a single cell. There are two distinct steps in the calculation; first, one...
  11. N

    Calculation of Closing Balance in Excel

    So I want to apply a simple formula to calculate closing Balance i.e. 'Closing Balance= Opening balance + purchase - sale' But there are many complexities. the main issue is. I have 3 workbooks, Workbook 'O' and Workbook 'T' and Workbook 'CT' Workbook 'O'- containing Opening balance as on...
  12. M

    Complex Concatenation

    I am trying to concatenate a complex string of text and percentages and variances and struggling to get it to work. Can any geniuses out there assist me? A1 = "Revenue" A2 = % which could be +/- which is referencing Cell B2, ie -5.3%. Negative figure not required to be pulled through A3 =...
  13. N

    A bit complicated.. at least for me

    Hi all, I have this specific task at work which I failed to do with VBA. So let me explain and even put a link to my file. I have the below arrays: Alpha: Includes a lot of Index Code|Exchange Code (Separated by "|") Beta: Includes a lot of Date| Exchange code (Seprated by "|") What is...
  14. B

    VBA Select Case - Expression too complex?

    I get "Expression too complex" when attempting this: Suggestions/remedy greatly appreciated. Select Case Val(ws.Cells(iRow, 18).Value) Case Is <= Val(Sheets("Settings").[A90]): OptionButton1 = True Case Is > Val(Sheets("Settings").[A93]): OtionButton4 = True...
  15. A

    Complex IF Statement help please!

    Greetings, Im working with a template that has the following expression: =IF(AND($B10="Goal",H$5>=$E10,H$5<=$E10+$F10-1),2,IF(AND($B10="Milestone",H$5>=$E10,H$5<=$E10+$F10-1),1,"")) The value in Column F is currently # of days - I'd like to change that to an End Date and would really like some...
  16. K

    Excel Formula

    HI Everyone, I am trying to calculate readings based on readings entered previously and then readings entered after the date. I know this might be a complex formula or simple for some, but I can't seem to figure it out. <tbody> Date Readings Calculated Reading 11-1-2018 60 60 11-2-2018...
  17. D

    Nested if or function

    I have a nested IF OR formula, but both of the criteria to evaluate has the same result. My formula is: <colgroup><col width="64" style="width: 48pt;"> <tbody> <colgroup><col width="64" style="width: 48pt;"> <col width="76" style="width: 57pt; mso-width-source: userset; mso-width-alt...
  18. I

    SUMIFS with multiple criteria, cell referencing

    Hi, Following the brilliantly fast response I got yesterday, I thought I'd try a trickier question. I'll be honest, I think of myself as good with excel but am very capable of getting completely lost when it comes to complex formula! I've got a SUMIFS formula which picks up spend of a...
  19. J

    value limit for a complex formula

    Hi, I have a complex formula that I am using for calculation of potential for employees receiving a bonus. It is based on limits of percentages. The formula looks like this: =IF(C33="THRESHOLD",C29*0.5,IF(C33="target",C29,IF(C33="max",C29*1.5))) In essence there is a drop down with the words in...
  20. A

    date and periods

    I would like the following values to populate based on dates: if date is 08-31-18 then 1 if date is 09/30/2018 then 2 ................................................................................. if date is 07/31/2019 then 12 What is the best way to do it? the if formula becomes too...

Some videos you may like

This Week's Hot Topics