excel function

  1. S

    Excel function for distinct count based on dates match

    Hello, I have data set where order ID's get created for every purchase transaction, whenever there is a change in transaction same order id gets updated and forms a new row. Now i wanted to distinct count how many orders i received per calendar day where the calendar dates sits out side of the...
  2. D

    Power Query - Hyperlink to file

    I have a power query setup that imports tables from all excel files in a folder. Is it possible to have a column that contains a link to the original excel file? [I have this working in a regular excel table using the hyperlink function (which uses the customer name to get to the correct...
  3. S

    Offset Match condition

    My output should have both the fruits which have value as true. For eg: For third row, Apple, Orange and Watermelon has the value as "True", but it is returning only first value. Formula i have used : =IFERROR(OFFSET([@Apple],[@[Offset Tower]],MATCH(TRUE,Table2[@[Apple]:[pineapple]],0)-1),"")...
  4. D

    Alternative to complex OFFSET Function

    Hi, I'm currently using an OFFSET function embedded in a SUMPRODUCT formula to calculate a multiple instances of a single production stream. The formula directly underneath "Total Volume" is the following...
  5. M

    Moving Data into one Row Based on ID and First Mention

    <colgroup><col><col><col><col><col><col><col><col><col><col span="6"></colgroup><tbody> Individual ID Last Name First Record Postal Code Attribute Group Attribute Start Date Step 1 Date Step 2 Date2 Step 3 Date3 Step 4 Date4 34376725 Aaron 10/21/2010 35205 Grants Mill Growth Track _4 Dream...
  6. T

    Function or macro to extract data from a unstructured cell containing text

    Hello I'm trying to find a way to extract a User ID from cells in a field called Metadata. The data is not in a similar format in each cell and I've been racking my brain trying to figure out how to easily extract the User ID to an adjacent cell so I can run it easily daily without having to...
  7. S

    Get the references from the cells that are not blank

    ****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;"> <tbody style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; font-size: inherit...
  8. T

    Excel Nested If, Or, Then function

    Hello, This is kind of a nested mess that I can't figure out. I'm looking for help because I don't have the skills to engineer this function. In cell Z2, (If cell X2 equals the value in W2, AND any of the cells P2/Q2/R2 have the value "Red" or the value "Orange", then put "Critical Attention"...
  9. J

    circular reference in excel

    Dear All Please have a look at the link of excel file attach . Till row no 980 everything is fine in column F But in row no 981 , the value in column F it does not add "Reheat Function" and so on please help...
  10. C

    Excel function to determine how many days in year YYYY

    Hello, I need to determine whether my daycount should be 365 or 366 depending on the year. For example in 2016 it was 366 and in 2017 it's 365. Is there a function which will do this? Thanks, Clare
  11. B

    Dynamic dependent List drop down in Excel

    Hello, I have a database of 3 columns. I want to create a 3 hierarchy drop down. My table is something like this. Each drop down is dependent on previously selected option. IMAGE : I want the drop down to look something like this. Database table would be updated time to time. Image...
  12. V

    Need help with excel simulation problem with the help of functions

    I am a novice in Excel functions.And have encountered this problem for my management science course.I would be grateful if someone can help me in it. the question is a part of the business simulation model.I need to find the function that can build - " after every 10 minutes, the server has to...
  13. V

    Countif and sumif in case of 2 ranges with VBA

    I can usethe countif and sumif functions in case of 1 range. But there are 2 ranges wherewithI have to work and I don’t have any idea. I would like to collect the uniqueconnect between elements of two ranges, count how many are them and how much isthe total of their values. See the example. Can...
  14. J

    Excel Help with related to Left Right Mid function

    The sample text data in my sheet are like the following, example Peter England, * Shirt / UK in a single cell. I need Peter England in a different cell Shirt in a different cell UK in a different cell. I am using the following function B1 =LEFT(A1,FIND(",",A1)-1) for getting Peter...
  15. G

    Vlookup with If statement Issue

    Hi Guys, I have a keyword data dump that has 2 of every keyword (one has data for desktops, one has data for smartphones) I would like to write a vlookup formula that will first check if the keyword is "desktop". If so, pull the corresponding rank (which would skip over the smartphone rows)...
  16. H

    Excel function that lists only non-zero/non-blank values in a column starting from a specific reference in a row/column

    I need to include a function in my C2 cell formula that will skip days with non-zero values and then list the next non-zero day (the day is also listed N times based on Col B value). <tbody> A B C 1 Mon 2 Mon 2 Tues 1 Mon 3 Wed 0 Tues 4 Thurs 0 Fri 5 Fri 1...
  17. F

    Sales Targets and Commission

    Hi Everyone, I am looking for advise on how to analysis sales targets and commission on daily bases. The table below is the one I had for a month but I would like to do break down on daily bases to track activity of my team on daily and weekly bases as well grant compensation...
  18. D

    COUNTIF Based on Two Criteria

    Hi Everyone, First, a bit of background. I have access to a database that provides me with engine status codes for a vehicle with a time stamp. This engine status code is in decimal, but needs to be converted to binary to be read because each engine status (No Engine Speed, Engine Derate, and...
  19. S

    Help with creating excel function

    Hey Guys, I am very new to creating functions using Microsoft VBA and hence need your help for the same. I want to create a function which should perform the operation mentioned below. Function IDW(StartValue, EndValue, CurrentPoint) IDW = (StartValue * (Row(EndValue) - Row(CurrentPoint)) +...
  20. A

    Any Function to do this?

    Hi all members, I need your help, Check pic below: Before: After: I want the data: First Min_ab001 then Max_ab001 Then Min_ab002 and Max_ab002 and so on... Please Help me if anyone knows a method.

Some videos you may like

This Week's Hot Topics

Top