• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search results

  1. Kimber

    Averageif formula over several columns to exclude numbers based on adjoining cell

    I have a worksheet with multiple percentages and coordinating description columns. On worksheets with a set of columns, I can use the formula =AVERAGEIFS(C:C,D:D,"<>Not needed"). However, I need to calculate multiple columns -- shown on the attached worksheet with the same status exclusion. I...
  2. Kimber

    Formula to include parentheses in text if applicable

    I have a cell showing net change -- both numerical and percentage. When positive, it has worked seamlessly, but I have negatives and I cannot find an answer. Basically, the net change is linked to two other calculated cells and an example is +30 (+6.3%) but when the answers are -55 (-10.6%) only...
  3. Kimber

    Track Remaining Amounts with Staggered Dates

    A friend has 10 "loans" that will need to be paid back (if he leaves the company) in full the first year, 80% the second year, 60% after three years, 40% after four years, 20% after five years, and 0 after six years. Easy for one date and amount. However, he has roughly ten dates with different...
  4. Kimber

    Excel formula not calculating correctly [SOLVED]

    I have a few cells where the sum calculation is not working correctly. When I highlight the cells, the total (sum) in the status bar is correct, however, the cell with the formula in it does not give an accurate answer (sum.) Please help! I have verified that cells involved are formatted as numbers
  5. Kimber

    12-month rolling turnover rate help

    Having trouble with a formula to calculate the 12-month rolling turnover rate for four categories. Missing some information so I am assuming I need to start formula in the 12th row of data? I've done this type of formula before but it only involved one column of data. Thank you for your help.
  6. Kimber

    Shade row based on text CONTAINED in first cell of row

    This seems like a simple formula but I did not find another post addressing it. I wish to shade a row based on "CAST" within other text in the first cell of the row. =$B2="CAST" works if it is the only word, but there is a string of text in the cells of the first column. Thank you.
  7. Kimber

    Dynamic Sum Range linked to value in another cell

    My current chart shows data by period over the course of six years. I need to change it to only show YTD total for the six years. I would like to be able to have the sum range increase for all columns as data is added to the 2019 column. For example, when data is added to Period 08 for 2019, I...
  8. Kimber

    Ignore #N/A in chart AND growing average formula

    I have a report that includes an accuracy column. A second column calculates the average accuracy for month 1 through the current month. In order for the chart to display (or not display, actually) the months with no data, I've entered "#N/A." The charts work properly for the monthly accuracy...
  9. Kimber

    Sum alternating cells linked to other workbook

    I have a worksheet that is populated from another workbook and has two total columns. There are two columns for each period -- quantity and dollars -- for each period, so the total columns correspond to the appropriate column periods. The sum formulas work if data is in the same workbook, but I...
  10. Kimber

    Issue with Excel formula and OneDrive name that includes a comma

    Yikes! Just had another issue come up. I am sorry if my description is confusing. We use OneDrive, so the other file that my cells are linked to has our company name in it. One of the total cells was returning an average where there was only one value. When I analyzed the new formula that uses...
  11. Kimber

    inconsistent result with identical formula

    I have three workbooks with identical template sheets and formulas. I don't want the quarter totals to show until the quarter is complete, so the formula I have had success with is =IF(D14>0,SUM(D12:D14),""). The three periods are in rows 12, 13, and 14. Two of the three matching worksheets...
  12. Kimber

    Excel worksheet freezes with insert/copy/paste

    The attached workbook is a mini version of the real one. The Exec Summary and dashboard sheets are created from data on 12 other worksheets. I deleted all but one to keep the file upload small. The report is updated monthly to show the most recent 12 months of data. All worksheets are part of...
  13. Kimber

    Display Months and Fiscal Years in axis

    I need to have a multiple level axis with fiscal years and months (July-June.) Is this possible?
  14. Kimber

    Comparing required and available capacity with usage - need formula help

    I need to create a sheet/table showing capacity for machines on first and second shifts and I need to compare usage to available capacity in both whole numbers and percentages. We will always fill first shift to capacity (100%) before producing on second shift. I need formulas for: Capacity...
  15. Kimber

    Values disappear when using filter in pivot table & chart

    My Pivot Table layout: REPORT FILTER: blank COLUMN LABELS: Values ROW LABELS: Location Description, then Reason VALUES: Sum of Cost, Sum of Qty I have sorted my table by location descriptions in descending order of sum of cost and all locations with reasons with cost and quantities are filled...
  16. Kimber

    Creating a YoY chart by period

    I have sales, production, and shipment data for several years that my boss would like to see in YoY to see if there are trends relating to seasons, months, etc. I'm wondering if the best way to display the charts is as 12 small charts (one for each month/period) on one sheet for each category --...
  17. Kimber

    Trouble aligning charts from tabs on transparencies

    I have several charts on separate tabs that require monthly updates and printing onto transparencies to be overlaid. I have ensured that all plot areas are the same size by entering the dimensions manually. However, they are not lined up on each worksheet so they do not line up when stacked. Can...
  18. Kimber

    Clustered Stacked Column Chart

    I have been charged with creating a stacked column chart with clusters for comparison. I have 13 Machines and I need two clustered columns for each -- one stacked for capacity (80% + 20%) and the second for items assigned to that machine. I've been able to create a stacked column chart for the...
  19. Kimber

    How to organize data for report

    I have financial data from a manually entered sheet provided by another department. I need to subtotal expenses by category for all products as well as by product for all categories. From the example you will see that a category was not provided for every product. This is an urgent need for my...
  20. Kimber

    master data workbook?

    I have many different reports and graphs that I create from many data sources. Most data is used for more than one report or chart, and historically, my predecessors entered the data into each report separately. Obviously, this increases the chance of human error and requires more steps for...
  21. Kimber

    Formulas for averages automatically updated when new column inserted

    I have a data for several products (rows)for each month (columns.) At the far right, I have the following columns (formula in parentheses): TOTAL (=SUM(M3:V3), 12 PERIOD AVERAGE (=X3/12), 6 PERIOD AVERAGE(=AVERAGE(R3:W3)), AND 3 PERIOD AVERAGE (=AVERAGE(U3:W3)). The worksheet is cumulative, but...
  22. Kimber

    Changing data point or line colors in chart

    I apologize if this has been addressed previously, but my searches (based on what I thought would work) yielded no results. I have a chart that graphs a daily rate in percentage form. The horizontal axis is the date and the vertical axis is the percentage. The GOAL is &#60;1%. My boss has...
  23. Kimber

    Display percentages +/- difference from 1

    I'm not sure how to phrase my question. I need to have variances show up only as a +/- from "1". In other words, 1.02 should show up as +.02 and .98 should show up as -.02. Your expert help will be greatly appreciated!
  24. Kimber

    conditional formatting with new daily cell references

    This is my first time to post a question, so please forgive me if my question is unclear. Currently, I print PDF reports sent to me each morning from the AS400. The report shows the planned daily average quantity (per day) and the actual daily average. Each morning I print this report and...
  25. Kimber

    conditional formatting for expanding worksheet

    Each day I get a report with several products listed with their daily plan and listed below each day in the period -- as it occurs. In other words, on the second day of the production period there is one entry for day one, on the third day there are two entries, etc. up to the end of the period...
Back
Top