User Profile
umasirhc
Copper Contributor
Joined 5 years ago
User Widgets
Recent Discussions
Returning the range for a specific value
I'm not sure if this question has come up, but I'm trying to figure out how to look up a value in a source table and then return the nearest range value that is less than and greater than the value into 2 different columns in the original table. My catch is that I also need to lookup a separate value in the original table to determine WHICH range to use. I've attached an example sheet. Basically I want to look up the value of Table1's MED_TRUE in Table2, look at Table1's DOSE_CALC value and return the first value that is less than DOSE_CALC into the NRST_VLS_DOWN column and the first value that is greater than DOSE_CALC into the NRST_VLS_UP column. Hope that made sense. Thanks for your help!1.1KViews0likes3CommentsAveraging Distinct Counts in a Pivot Table
OK, I've created a Pivot Table that pulls the distinct count of patient identifiers per date in one column by the date in the rows from a query. I'm trying to get a calculation of the average daily number of distinct patient identifiers but I'm struggling. I've added the data to the Data Model and tried to play around with measures, but just can't get it to work. Any ideas? How do you get Excel to calculate a distinct count for each day and then average those distinct counts by the number of dates in the Pivot Table? Sorry I can't provide a test sheet with this question.Solved16KViews0likes9CommentsUsing OFFSET formula to create dynamic print area when cells contain formulas?
Good afternoon, Looking for help. I'm trying to set up a dynamic print area using OFFSET but, for the sheet I'm trying to print, all cells contain formulas so I've used IF and IFERROR to make them show as blank when necessary. What I'm finding is that trying to use COUNTA to define the range of rows doesn't work because Excel considers a cell containing a formula not blank, even if visually it is. I kind of got COUNT to work by adding a number to the column header for a column that will contain number values, but for some reason it cuts off the last line that I would have expected to print. The data in the sheet should grow and shrink based on the values in the PRODUCT_DESC field that are pulling in from a pivot table in another file. The formula I'm using for the dynamic print area is =OFFSET($A$1,0,0,COUNT($F:$F),12) What am I doing wrong and is there a better way to format the formula? I've attached the sheet, but I had to break the links since they link to protected data, so consider that all cells in columns A through L would have a background formula (except for E and H which are manual entry fields). Thanks for any help you can provide! Chris A.8KViews0likes3CommentsFirst time help request
Hello! First time here, so I hope I'm posting on the right board. I have turn-around time data that is entered by my staff in columns for each individual date. But I would like to summarize the data in various categories with the date filtering down a single column and the categories moving horizontally. I've gotten the date fieldto work with an INDEX function, but since I'm trying to use SUM and COUNT/COUNTIF functions as well, I can get one row to work but I can't figure out how to copy the formulas down the table to maintain the reference to the row range while allowing the column to move as the formula is copied down each row. Could use some guidance on the best way to handle this as I want to be able to present the data using pivot table/chart.808Views0likes2Comments
Groups
Recent Blog Articles
No content to show