1. E

    Multiple Date Columns in Data Model Pivot Table

    Good Afternoon, How can I create 2 columns with distinct date ranges? I have 1 table with my dates and units and I want to show the units sold for 1 week in 1 column and the next week in the second column and then calculate the change WOW. I don't know how to have two date columns from one...
  2. L

    making a chart of the count of matched pairs and triples

    I have a spreadsheet with 214 rows til now, with 10 units of data on each row, 5 numbers and 5 letters. The numbers go from 01 to 1237 and 80 letters from A to CB. I need to make a table with the number of occurences of matched pairs and triples. I would like to know the number of times that 2...
  3. I

    Value VLOOKUP is searching for is negative - returns #N/A

    Team, Trying to keep this example simple. I have a pivot table: <tbody> A B C 1 Aisle # Locations Units 2 001 229 1569 3 002 1 -12 4 003 12 214 </tbody> I am trying to combine information in a spreadsheet from three pivot tables using the VLOOKUP. However, when I try to display the...
  4. M

    Calculate units by multiple criteria

    Hi all, Happy Friday! I was asked to work out a formula based on multiple conditions and for the life of me I can't work it out. I think I'm over complicating it but I was thinking an INDEX and MATCH formula would work. I know I can just do a simple lookup to get the results but in all...
  5. J

    How to split units from numbers stored in a cell that is custom formatted

    Hi All! I am new to this forum. Need help. I need to separate units from quantities. i am not being able to do it because cells that are custom formatted and no text function works on these. When converted to text format, the units automatically disappears! Can you please help me out on this.
  6. S

    DAX measure produces more than TOP 15. Slicer issue?

    Hi, I'm having an issue with a DAX formula limited to TOP!%. But produces more that 15 lines. Could it be because I have a filter on the PIVOT TABLE for location. But need to add it to the measure? tblLocation.Loc# The measure below should provide me with the TOP 15 Brands sold for the day...
  7. S

    TOPn SOLD vs OH

    Hi, I’m trying to build a measure to list the TOP 10 models and what our inventory levels are for those items. I would rather use CUBIC formulas but since the list of the TOP items changes each day I am limited to a Pivot Table. I could have another pivot table and use a vlookup. But that...
  8. A

    Formula to calculate annual cost with unit prices with varying monthly volumes

    Hi all, I hope someone can help. I have a large XLS and I have a table of unit prices and then a separate sheet with costs by year. The problem is that each year is a single cell and the number of units increases by 10k each month. Is there a formula that will allow me to derive the yearly...
  9. A

    Forecasting Quantity in Sales

    I have a product for which units sold in May is 12420, Jun is 1200, and Jul is 12400. How can i forecast that how many units will be sold for the next 12 months?.
  10. S

    SUMIFS Units and skip such values

    <tbody> Units Amount Month 100 2300 Jan 100 0.10 Jan 200 1000 Feb -200 -1000 Feb 300 2000 Jan 300 -0.08 Jan </tbody> I want to apply formula in the column A Units and wanted to skip units if Values in column B is between -0.5 to 0.5, please help based on the...
  11. N

    Formula for Calculating Sales Projections

    Hey all - just a basic question that I'm blanking on for some reason... I have a pivot table that refreshes that shows me how many units I have sold through out the year with the total units sold in cell F75. I have another calculation in cell G76 that shows me what % of the way we are through...
  12. A

    Formula to distribute units based on weight, rounded to whole number, but sum to proper total

    Hi there, I'm having trouble figuring out a formula that will distribute my total number of units by weight. I need to round the result to the nearest whole number but I also need the total units once distributed to sum to the proper total. <tbody> Row 1 6.2% 6.4% 8.6% 9.4% 10.4% 9.6% 8.1%...
  13. L

    Help with a formula

    <tbody> Hi guys, I am trying to use a vlookup up to calculate whether a capital gain or loss has been made or lost when selling shares. A major problem I have is; if shares are purchased in two separate lots and at different prices, when I sell a portion of the shares I need...
  14. L

    Vlookup or something else?

    <colgroup><col span="9"></colgroup><tbody> Hi guys, I am trying to use a vlookup up to calculate whether a capital gain or loss has been made or lost when selling shares. A major problem I have is; if shares are purchased in two separate lots and at different prices, when I...
  15. D

    Calculate Units Picked 17:00 - 04:00am (Shift)

    Hi, I have created a Data Model from a block of data. The warehouse guys work from 17:00 to 04:00 daily, what's the best approach to obtain the units picked but displayed in one pivot table? I can achieve this over 2 pivot tables using filters but ideally I want this in one table. Each shift...
  16. D

    Writing Nested If formula which returns "0" once condition is met

    Hello, I have a nested if statement to return values once a certain number of units are deployed. What I'm having trouble with is how to structure the formula so that is returns zero once the condition is met. So for instance, the first time units deployed hits the 23-28 range, then return...
  17. K

    SUMIFS / SUMPRODUCT / Multiplication and addition in single cell formula

    Hi, I have exported accounts data in excel from accounting software. Now stocks of the items are in Cartons (boxes); please help me, how to convert them into KG because record of all other items is on KG only. Please see attached excel sheet. I can get the sum of boxes for desired products but...
  18. W

    Formula based on ranking and available capacity

    I have a problem where I am trying to produce a certain number of units, however the available capacity does not allow all of the units to be produced each month and they need to be produced in order of their ranking from 1 through 6. The ranking of the units to be produced changes from month...
  19. T

    Client name in four rows with different services and want to move the services to columns

    I have a client name in 3 or 4 rows. Each row has a different service with different units and unit price. I want to have each client have one row, and the service hours and price in different columns. Example Smith 3 units $5.00 per unit Smith 4 units $3.23 per unit I want Smith 3 $5.00...
  20. H

    vlookup alternative for array of data?

    I am trying to get a total of units sold for a particular UPC. The units are not summed up before they reach me, so vlookup does not work here--first I need to get the total of the units, and then I need to input that total. I usually use a pivot table for this, but recently I've run into issues...

Some videos you may like

This Week's Hot Topics