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...
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...
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...
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
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.
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.
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...
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...
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...
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...
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...
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...
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...
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...
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 --...
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...
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...
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...
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...
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...
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 <1%. My boss has...
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!
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...
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...