1. B

    Complex IF formula

    Hi everyone, I hope I can explain in detail, what I need. Cell J12 reflects the required date of drawing 101 for PO 123456. For this I used the formula: =IF(ISBLANK($B12),"",$B12+14) Cell J13 needs to reflect the required date of drawing 102, based on the date drawing 101 was sent back to the...
  2. 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...
  3. 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...
  4. 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...
  5. 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...
  6. 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...
  7. 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?
  8. 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...
  9. 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...
  10. 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...
  11. 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...
  12. 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...
  13. 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 =...
  14. 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...
  15. 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...
  16. 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...
  17. 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...
  18. 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...
  19. 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...
  20. 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...

Watch MrExcel Video

This Week's Hot Topics

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
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

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